Hi SitePoint members
I have been perusing through the solutions for “updating multiple rows with one query”, but I have a pressing question: How would one “SET” multiple column values with one query?
Here is my example…the normal update command would be:
UPDATE table_FooBar
SET answerOne='$ans1Val',
answerTwo='$ans2Val',
answerThree='$ans3Val'
WHERE member_id='$memberid' AND question_id='$questionid';
Now I have to do this for up to 20 rows for that individual reponse.
Normally, it would be nice to do this - but when I put it into a loop, MySQL database only executes the first line of code for some odd reason.
// question ID ranges from 1-20
// $questionid == 1, $member_id==1
UPDATE table_FooBar
SET answerOne='$ans1Val',
answerTwo='$ans2Val',
answerThree='$ans3Val'
WHERE member_id='$memberid' AND question_id='$questionid';
// $questionid == 2, $member_id==1
UPDATE table_FooBar
SET answerOne='$ans1Val',
answerTwo='$ans2Val',
answerThree='$ans3Val'
WHERE member_id='$memberid' AND question_id='$questionid';
...etc.
Here is the PHP loop
$numQuestions = 20;
$questionid = 1;
for($x=1;$x<=$numQuestions;$x++)
{
$sql = " UPDATE table_foobar ";
$sql .= " SET answerOne='" . $ans1Val . "' ";
$sql .= ", answerTwo = '". $ans2Val ."' ";
$sql .= ", answerThree = '". $ans3Val ."' ";
$sql .= " WHERE ";
$sql .= " member_id=" . $memberid . " AND question_id= " . $questionid . "; ";
$result = mysql_query($sql, $db);
$questionid++;
}
And here is what I see parsed when I debug it (I am only showing the first 3 lines of parsed code)
UPDATE table_FooBar SET answerOne='yes', answerTwo='no', answerThree='yes' WHERE member_id = 1 AND question_id = 1;
UPDATE table_FooBar SET answerOne='no', answerTwo='no', answerThree='yes' WHERE member_id = 1 AND question_id = 2;
UPDATE table_FooBar SET answerOne='yes', answerTwo='yes', answerThree='no' WHERE member_id = 1 AND question_id = 3;
BUT>> It only executes the first line of code! Any Tips?