Hi,
I just purchased SC9.5.
I am trying to insert multiple records into a table when I press a button. I used sc_lookup and array() but only one record was inserted. Would appreciate if someone can enlighten me the method on how to insert multiple records into a table.
Thanks for your help.
Perhaps a better explanation would explain what you are trying to do!
Are these records that you capture or are you reading the data from another file/table? If the later then you should do a count on the lookup with a for next loop.
Can you show the code you have on the button
This is the one I use:
$pid=960;
$school=1;
////Loop through records
$sql=" SELECT id
FROM therapists WHERE eptherapist_master_epid = ‘$pid’
AND eptherapist_active = ‘Y’ ";
sc_lookup(rs,$sql);
foreach ({rs} as $key => $value)
{
$thid = {rs[$key][0]};
//INSERT SQL HERE
}
///Two stage LOOP
$sql=" SELECT studentid as sid FROM classlist LIMIT 5";
sc_lookup(aa,$sql) ;
foreach ({aa} as $key1 => $value)
{
$sid = {aa[$key1][0]};
$sql=" SELECT studentlastname as name FROM classlist WHERE studentid = ‘$sid’ “;
sc_lookup(bb,$sql) ;
foreach ({bb} as $key2 => $value)
{
$name = {bb[$key2][0]};
$sql=” INSERT INTO tablename (field1,field2) VALUES (’$var1’,’$var2’) ";
sc_exec_sql($sql);
}
}
Remember {rs} is an array and I will suggest changing the loop so that it references the array.
sc_lookup(rs,$sql);
for ($x = 0; $x < count({rs}); $x++) {
$thid = {rs[0][$x]};
//INSERT SQL HERE
}
if you add the code below just after the read, you will able to see the array. eg
sc_lookup(rs,$sql);
echo '<pre>';
print_r({rs});
Thanks for that adjustment.
Perhaps you would be kind enough to answer a question for me.
I see that this method grabs table data according to the number of the field in the Mysql Query.
Is there a way to do it so that you can grab the data with the Name of the field?
Like this?
$sql=" SELECT student_last name as lastn
FROM students WHERE schoolid=1 ";
sc_lookup(rs,$sql);
foreach ({rs} as $key => $value)
{
$lastName = {rs[$key][‘lastn’]};
}
Sorry, but not as far as I know. From my understanding the lookup reads the data into the variable.
ok thanks. also… I am getting an index error when I run your code…
$sql=" SELECT studentid
FROM classlist WHERE status =1 LIMIT 5";
sc_lookup(rs,$sql);
for (
$x = 0;
$x < count({rs});
$x++
)
{
$sid = {rs[0][$x]};
echo"SID:$sid
";
}
It only grabs the first $sid
if there is more than one record then
$sid = {rs[$x][$y]
in this case $y is the field position. In your case
$sid = {rs[$x][0]
Thanks that worked great!!
Thanks for your explanations is very helpful. It works.
@nwdbs
AFAIK, to use field names you have to use sc_select
The SC help shows only the field position syntax:
while (!{my_data}->EOF)
{
{clientName} = {my_data}->fields[1];
{my_data}->MoveNext();
}
But you can use field names as well
while (!{my_data}->EOF)
{
{clientName} = {my_data}->fields(‘clientName’);
{my_data}->MoveNext();
}
Wow thanks so much Robydago.
This works great:
$sql=“SELECT * FROM classlist WHERE schoolid = 66 AND status = 1 LIMIT 5”;
sc_select(my_data, $sql);
while (!{my_data}->EOF)
{
$name= {my_data}->fields(‘studentlastname’);
echo"Name=$name
";
{my_data}->MoveNext();
}
Are there any drawbacks to using sc_select as opposed to the other method?