[SOLVED] Inserting into database within a loop

[A.White.89]

I am trying to insert 2 or more things into a table with a foreach loop but for some reason only the first query is made within the loop.  The script doesn't die but when I browse the table, only 1 of the rows is there. 

 

$query = mysql_query("SELECT `convID` FROM `askdoctors` ORDER BY `convID` DESC LIMIT 1") or die("Could not run query");
$lastID = mysql_fetch_assoc($query);
$convID = $lastID['convID'] + 1; 

$doctors = mysql_query("SELECT `ID` FROM `doctors` WHERE `type` = '$category'") or die("Could not run query");
$insert = mysql_fetch_assoc($doctors);
foreach($insert as $doc) 
{
    $doctorID = $doc['ID'];
    mysql_query("INSERT INTO `askdoctors`(`convID`,`userID`,`doctorID`,`subject`,`content`,`category`)
VALUES('$convID','$userID','$doctorID','$subject','$content','$category')") or die("Could not run query");
}

 

Any suggestions?

[thewooleymammoth]

try

 

<?php
$query = mysql_query("SELECT `convID` FROM `askdoctors` ORDER BY `convID` DESC LIMIT 1") or die("Could not run query");
$lastID = mysql_fetch_assoc($query);
$convID = $lastID['convID'] + 1;
            
$doctors = mysql_query("SELECT `ID` FROM `doctors` WHERE `type` = '$category'") or die("Could not run query");
$insert = mysql_fetch_assoc($doctors);
foreach($insert as $doc)
{
    $doctorID = $doc['ID'];
    if(mysql_query("INSERT INTO `askdoctors`(`convID`,`userID`,`doctorID`,`subject`,`content`,`category`)
   VALUES('$convID','$userID','$doctorID','$subject','$content','$category')")){

       echo "query completed <br />";
     }else{
          echo "database error: ".mysql_error()."<br />;
     }


}
?>

 

atleast that way you can see each line iether being completed or erroring. Im not quite sure what your trying to do? are you trying to insert data for each row?

 

here is how you insert data into each row that is returned from your query

 

<?php
$query=mysql_query("SELECT * FROM `table` WHERE `desired_row` = 'desired_data'");

while($info=mysql_fetch_array($query)){
     $id=$info['id'];
     $data1=$info['data1'];
     $data2=$info['data2'];
     $data3=$info['data3'];

     if(mysql_query("INSERT INTO `table`(`data1`,`data2`,`data3`)
   VALUES('$data1','$data2','$data3') WHERE `id` = '$id' LIMIT 1;")){
     
          echo "Query successful <br />";

     }else {

          echo "Query error: ".mysql_error()."<br />";

     }
}
?>

 

Note: any value can be the id as long as its unique from other rows. if you want to sort which order it adds them too use

 

mysql_query("SELECT * FROM `table` WHERE `id` = 'id' ORDER BY `sortfield` asc");

 

[A.White.89]

Thanks for the response.

 

Doing that just gives me "query completed" but still doesn't add multiple rows to the table.

 

The problem wasn't that the query was failing, just that it wasn't inserting properly.  (It didn't die).

 

 

[thewooleymammoth]

i think your problem might be that mysql_fetch_assoc(); doesnt fetch an array of rows, it fetches all the data in a given row. so a foreach loop based on a mysql_fetch_assoc(); is going to output each column for the first row it fetches, then running the fetch again will give you the next rows data

 

hopefully that makes sense, im getting pretty tired

[A.White.89]

That wasn't the issue, but I figured it out.

 

while($doc = mysql_fetch_assoc($doctors))
{
$doctorID = $doc['ID'];
mysql_query("INSERT INTO `askdoctors`(`convID`,`userID`,`doctorID`,`subject`,`content`,`category`)
   VALUES('$convID','$userID','$doctorID','$subject','$content','$category')"))
}

 

This worked fine.  Thanks for the help.