updating multiple form values to MySQL

[vzwhaley]

i am building a content management system for my employer's Web site, which is a daily newspaper. haven't had too much trouble learning PHP up to this point, but I have a problem now that I am finding difficult to figure out. any help would be much appreciated.

I have two MySQL tables for a Poll on our site, one named PollQuestions and one named PollAnswers. I am trying to build an administration form that will allow the user to pull up both the question and answers for any given poll and edit them if they need to. I haven't had a problem with the Poll Question, but with the Poll Answers, I am unsure how I can pull each Poll Answer into a text field and update all of them in one query based on an ID field for the answers.

In other words, I have each answer pulling into the form from the database and for each answer, i have a hidden field that stores that particular answer's ID number.

So how do I update all of the answers in one query based on that particular answer's ID number? I have written the following code, but it does not work. Any help would be much appreciated.

For the form itself, here is the code for the Answer text fields:
<code>
<?php do {?>
<tr bgcolor="#FFFFFF">
<td align="right" valign="middle" bgcolor="#CCCCCC" class="LeftNav"><div>Answer :
</div>
</td>
<td colspan="2" valign="middle" bgcolor="#CCCCCC" class="LeftNav">
<input name="Answer[]" type="text" value="<?php echo $RS2['Answer'];?>" size="45"><input type="hidden" name="AnswerID[]" value="<?php echo $RS2['ID'];?>"> </td>
</tr>
<?php } while ($RS2 = mysql_fetch_assoc($Recordset2));?>
</code>

For the MySQL update, here is the code:
<code>
foreach($_POST['Answer'] as $Answer) {
$Answer2 = str_replace("'", "\'", "$Answer");

foreach($_POST['AnswerID'] as $AnswerID) {
$AnswerID2 = $AnswerID;

mysql_query("UPDATE PollAnswers SET Answer = '$Answer2' WHERE ID = '$AnswerID2' ");

</code>

[jatar_k]

Welcome to WebmasterWorld vzwhaley,

I would loop through them and update one in each iteration of the loop. This allows you to handle any possible errors as well.

though are you setting them answer to many ids?

if so you can use IN

UPDATE PollAnswers SET Answer = '$Answer2' WHERE ID IN ('$AnswerID2','$AnswerID3','$AnswerID4')

not sure if either of those helps

[vzwhaley]

Yes, each Answer[] has its own ID number in the database and I need the answers to be updated based on that ID number. Make sense? I have included a hidden form field that lists the ID number for that particular answer.

So how would I loop through each answer and insert them individually? thanks for your help.

[jatar_k]

if you have multiple answers within the same form then you will need some type of naming convention to make each answer individually accessible. You then just create each insert statement from the values posted to the script and issue the insert query.

first thing

build the form(s) then submit it and dump the $POST array. That should give you an idea of what you are working with.

I usually dump arrays like so

echo '<pre>';
print_r($_POST);
echo '</pre>';

[vzwhaley]

thank you for your help! i'll try that ASAP! have a great day!