CODEWITHSUNDEEP
php
seniorprojectdiva
seniorprojectdiva

Reputation:

inserting values from a loop

$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?

Upvotes: 0

Views: 271

Answers (4)

Glavić
Glavić

Reputation: 43552

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...

Upvotes: 3

Gumbo
Gumbo

Reputation: 655169

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).

Upvotes: 3

Andrew Hare
Andrew Hare

Reputation: 351466

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.

Upvotes: -1

strager
strager

Reputation: 90012

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;
}

Upvotes: 1

Related Questions