inserting values from a loop

Question

$sql1 = "SELECT SIDno FROM class WHERE SubjID='$SubjName' and SecID='$SecName'";
$result1 = mysql_query($sql1);
while ($row1 = mysql_fetch_assoc($result1)){
    $IDno = $row1["SIDno"];
    $sql2="INSERT INTO registered ( ServiceID, IDno, Stype)VALUES('$RecCode','$IDno','$Stype')";
}

this is my code. its working but it only insert one data into the database. How can make it away to insert all the possible data from the loop. Can anyone help me?

Answer 1

You’re probably executing the query after the loop so only the last record is being inserted.

Try to execute the insertion query at the end of the loop:

while ($row1 = mysql_fetch_assoc($result1)) {
    $IDno = $row1["SIDno"];
    $sql2 = "INSERT INTO registered (ServiceID, IDno, Stype) VALUES ('".mysql_real_escape_string($RecCode)."', '".mysql_real_escape_string($IDno)."', '".mysql_real_escape_string($Stype)."')";
    mysql_query($sql2);
}

Or you first collect all data and then do one query to insert all records:

$values = array();
while ($row1 = mysql_fetch_assoc($result1)) {
    $IDno = $row1["SIDno"];
    $values[] = "('".mysql_real_escape_string($RecCode)."', '".mysql_real_escape_string($IDno)."', '".mysql_real_escape_string($Stype)."')";
}
if (!empty($values)) {
    $sql2 = "INSERT INTO registered (ServiceID, IDno, Stype) VALUES ".implode(',', $values);
    mysql_query($sql2);
}

But don’t forget to prepare the values for the query (see mysql_real_escape_string function).

Answer 2

Change this line:

$sql2="INSERT INTO registered..."

to this:

$sql2 .= "INSERT INTO registered..."

inside the loop. You are accidentally overwriting the insert statement each time. If you use .= you will append the next statement to the previous one, creating a batch of insert scripts, one for each record.

Answer 3

Note: make sure you're escaping your variables with mysql_real_escape_string.

$sql1 = "SELECT SIDno FROM class WHERE SubjID='$SubjName' and SecID='$SecName'";
$result1 = mysql_query($sql1);

$sql2 = "INSERT INTO registered (ServiceID, IDno, Stype) VALUES ";

$addComma = false;
while ($row1 = mysql_fetch_assoc($result1)){
    $IDno = $row1["SIDno"];
    $sql2 .= ($addComma ? ", " : "") . "('$RecCode','$IDno','$Stype')";
    $addComma = true;
}

Answer 4

If you are not planing to do anything with the fetched data, you could use INSERT .. SELECT .. statement.

Example:

INSERT INTO registered (ServiceID, IDno, Stype)
SELECT field1, field2, field3
FROM class
WHERE SubjID='$SubjName' and SecID='$SecName'"

And like written before me, escape your variables...