Hi
I have an insert query which is executed within a select query. For every ID the SELECT query retrieves, I want to INSERT 4 records into another table wthin my database but use the ID from the select query as part of the VALUES for the INSERT query.
Got it?
Anyway, I am having problems with my code below, because the INSERT query is not being reset after each time it is run, so if i retrieved 100 ID's from the the SELECT query, the 1st INSERT query is run 100 times. Hope that makes sense!
How can I get it so that the INSERT query is only run once once the ID is retrieved from the database?
This is my code:
// get the ID's from the database...
$sqlquery = "SELECT qid, refid FROM statustable WHERE refid = $refid";
$result = mysql_query($sqlquery);
while ($datarow = mysql_fetch_array($result)) {
$questionid = $datarow['refid'];
$qrefid = $datarow['qid'];
// set up the 4 sets of insert values...
$insertavalues .= "($questionid, $qrefid, \"A\", $b)";
$insertavalues .= "($questionid, $qrefid, \"B\", $b)";
$insertavalues .= "($questionid, $qrefid, \"C\", $b)";
$insertavalues .= "($questionid, $qrefid, \"D\", $b)";
// now insert them...
$insertavalues = trim($insertavalues);
$insertasqlquery = "INSERT INTO othertable (qid, refid, letter, qindex) VALUES $insertavalues";
$insertadataresult = mysql_query($insertasqlquery);
}
mysql_free_result($result);
I hope that makes sense and thanks in advance for any help offered.
