Page 1 of 1

multiple form text field updates to MySQL

Posted: Tue Oct 18, 2005 10:06 am
by 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: Select all

<?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)); ?>

For the MySQL update, here is the code:

Code: Select all

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' ");

Posted: Tue Oct 18, 2005 7:02 pm
by yakasha
First, unless you're retrieving the first row from Recordset2 before the code you've pasted, you'll get a blank answer. Change to while() {} loop,.

Second, I don't think you can ever know for sure what order the clients will return the post data, so its probably not a good idea to assume the two arrays will line up every time. Try this to ensure the right ids are with the right answer:

Code: Select all

<?php
while( $RS2 = mysql_fetch_assoc($Recordset2) )
{
?>
  <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[<?php echo $RS2['ID']; ?>]" type="text" value="<?php echo $RS2['Answer']; ?>" size="45">
    </td>
  </tr>
<?php
}
?>
Then, for what you're trying to do, you can't do it in one query, you'll have to do each seperately.

try:

Code: Select all

foreach( $_POST['Answer'] as $id => $newAnswer )
{
  mysql_query( "UPDATE PollAnswers SET Answer = '" . mysql_escape_string($newAnswer) . "' WHERE ID='" . mysql_escape_string($id) . "'" );
}
Should use the mysql_escape_string because you never know what is coming back.