PHP RSS PHP RSS

Updating multiple rows in mysql...how to add a checkbox!

Thread Solved

•
•
Join Date: Jul 2008
Posts: 2
Reputation: Benbo1980 is an unknown quantity at this point 
Solved Threads: 0
Benbo1980 Benbo1980 is offline Offline
Newbie Poster

Updating multiple rows in mysql...how to add a checkbox!

 
0
  #1
Jul 16th, 2008
Hi there,

I have successfully setup a page using a tutorial (shown http://www.phpeasystep.com/mysql/10.html)

It's all working fine as it stands.

I have now added another column to the database that can either be 1 or 0 and I would like to be able to change it on the update page using a checkbox for each record.

The code I tried was as follows:
PHP Syntax (Toggle Plain Text) 
  1. <strong>Update multiple rows in mysql</strong><br> 
  2.  
  3. <?php 
  4. $host="localhost"; // Host name 
  5. $username=""; // Mysql username 
  6. $password=""; // Mysql password 
  7. $db_name="test"; // Database name 
  8. $tbl_name="test_mysql"; // Table name 
  9.  
  10. // Connect to server and select databse. 
  11. mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
  12. mysql_select_db("$db_name")or die("cannot select DB"); 
  13.  
  14. $sql="SELECT * FROM $tbl_name"; 
  15. $result=mysql_query($sql); 
  16.  
  17. // Count table rows 
  18. $count=mysql_num_rows($result); 
  19. ?> 
  20. <table width="500" border="0" cellspacing="1" cellpadding="0"> 
  21. <form name="form1" method="post" action=""> 
  22. <tr> 
  23. <td> 
  24. <table width="500" border="0" cellspacing="1" cellpadding="0"> 
  25.  
  26.  
  27. <tr> 
  28. <td align="center"><strong>Id</strong></td> 
  29. <td align="center"><strong>Name</strong></td> 
  30. <td align="center"><strong>Lastname</strong></td> 
  31. <td align="center"><strong>Email</strong></td> 
  32. <td align="center"><strong>On / Off</strong></td> 
  33. </tr> 
  34. <?php 
  35. while($rows=mysql_fetch_array($result)){ 
  36. ?> 
  37. <tr> 
  38. <td align="center"><? $id[]=$rows['id']; ?><? echo $rows['id']; ?></td> 
  39. <td align="center"><input name="name[]" type="text" id="name" value="<? echo $rows['name']; ?>"></td> 
  40. <td align="center"><input name="lastname[]" type="text" id="lastname" value="<? echo $rows['lastname']; ?>"></td> 
  41. <td align="center"><input name="email[]" type="text" id="email" value="<? echo $rows['email']; ?>"></td> 
  42. <td align="center"><input name="ONOFF[]" type="checkbox" id="ONOFF" value="1" 
  43. <?php if ($rows['ONOFF'] ==1) { echo "checked";} else {} ?> 
  44. ></td> 
  45. </tr> 
  46. <?php 
  47. } 
  48. ?> 
  49. <tr> 
  50. <td colspan="4" align="center"><input type="submit" name="Submit" value="Submit"></td> 
  51. </tr> 
  52. </table> 
  53. </td> 
  54. </tr> 
  55. </form> 
  56. </table> 
  57. <?php 
  58. // Check if button name "Submit" is active, do this 
  59. if($Submit){ 
  60. for($i=0;$i<$count;$i++){ 
  61. $sql1="UPDATE $tbl_name SET name='$name[$i]', lastname='$lastname[$i]', email='$email[$i]', ONOFF='$ONOFF[$i]' WHERE id='$id[$i]'"; 
  62. $result1=mysql_query($sql1); 
  63. } 
  64. } 
  65.  
  66. if($result1){ 
  67. header("location:update_multiple.php"); 
  68. } 
  69. mysql_close(); 
  70. ?>

What happens is weird - no matter which checkboxes are ticked, the database appears to update the wrong records. For example, if I had checked every other record (4 in total) and clicked submit, the database would have simply changed the top four records, not necessarily the ones I had originally selected.

Any ideas?!

Thanks,

Ben
Last edited by Tekmaven; Jul 16th, 2008 at 3:10 pm. Reason: Code tags
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2008
Posts: 849
Reputation: R0bb0b is on a distinguished road 
Solved Threads: 67
R0bb0b's Avatar
R0bb0b R0bb0b is offline Offline
Practically a Posting Shark

Re: Updating multiple rows in mysql...how to add a checkbox!

 
0
  #2
Jul 16th, 2008
Try this
php Syntax (Toggle Plain Text) 
  1. <strong>Update multiple rows in mysql</strong><br> 
  2.  
  3. <?php 
  4. $host="localhost"; // Host name 
  5. $username=""; // Mysql username 
  6. $password=""; // Mysql password 
  7. $db_name="test"; // Database name 
  8. $tbl_name="test_mysql"; // Table name 
  9.  
  10. // Connect to server and select databse. 
  11. mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
  12. mysql_select_db("$db_name")or die("cannot select DB"); 
  13.  
  14. $sql="SELECT * FROM $tbl_name"; 
  15. $result=mysql_query($sql); 
  16.  
  17. // Count table rows 
  18. $count=mysql_num_rows($result); 
  19. ?> 
  20. <table width="500" border="0" cellspacing="1" cellpadding="0"> 
  21. <form name="form1" method="post" action="<? echo $_SERVER['REQUEST_URI']; ?>"> 
  22. <tr> 
  23. <td> 
  24. <table width="500" border="0" cellspacing="1" cellpadding="0"> 
  25.  
  26.  
  27. <tr> 
  28. <td align="center"><strong>Id</strong></td> 
  29. <td align="center"><strong>Name</strong></td> 
  30. <td align="center"><strong>Lastname</strong></td> 
  31. <td align="center"><strong>Email</strong></td> 
  32. <td align="center"><strong>On / Off</strong></td> 
  33. </tr> 
  34. <?php 
  35. while($rows=mysql_fetch_array($result))
  36. { 
  37. ?> 
  38. <tr> 
  39. <td align="center"><input type="hidden" name="id[]" value="<? echo $rows['id']; ?>" /><? echo $rows['id']; ?></td> 
  40. <td align="center"><input name="name<? echo $rows['id']; ?>" type="text" id="name" value="<? echo $rows['name']; ?>"></td> 
  41. <td align="center"><input name="lastname<? echo $rows['id']; ?>" type="text" id="lastname" value="<? echo $rows['lastname']; ?>"></td> 
  42. <td align="center"><input name="email<? echo $rows['id']; ?>" type="text" id="email" value="<? echo $rows['email']; ?>"></td> 
  43. <td align="center"><input name="ONOFF<? echo $rows['id']; ?>" type="checkbox" id="ONOFF" value="1" 
  44. <?php if ($rows['ONOFF'] ==1) { echo "checked";} else {} ?> 
  45. ></td> 
  46. </tr> 
  47. <?php 
  48. } 
  49. ?> 
  50. <tr> 
  51. <td colspan="4" align="center"><input type="submit" name="Submit" value="Submit"></td> 
  52. </tr> 
  53. </table> 
  54. </td> 
  55. </tr> 
  56. </form> 
  57. </table> 
  58. <?php 
  59. // Check if button name "Submit" is active, do this 
  60. if($Submit)
  61. { 
  62. 	foreach($_POST['id'] as $id)
  63. 	{ 
  64. 		$sql1="UPDATE ".$tbl_name." SET name='".$_POST["name".$id]."', lastname='".$_POST["lastname".$id]."', email='".$_POST["email".$id]."', ONOFF='".$_POST["ONOFF".$id]."' WHERE id='".$id."'"; 
  65. 		$result1=mysql_query($sql1); 
  66. 	} 
  67. } 
  68.  
  69. if($result1){ 
  70. header("location:update_multiple.php"); 
  71. } 
  72. mysql_close(); 
  73. ?>

You were looping through with a counter and using the loopnumber as your id, this will only work if you are absolutely positive that your ids are sequential, looks like you are using register globals I think. I've never used them so I actually find your code confusing.
Last edited by R0bb0b; Jul 16th, 2008 at 3:42 pm.
“Be who you are and say what you feel because those who mind don't matter and those who matter don't mind.” - Dr. Seuss

-- The documentation is inevitable, you may get away with it for a little while but eventually you too will have to do the deed.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2008
Posts: 2
Reputation: Benbo1980 is an unknown quantity at this point 
Solved Threads: 0
Benbo1980 Benbo1980 is offline Offline
Newbie Poster

Re: Updating multiple rows in mysql...how to add a checkbox!

 
0
  #3
Jul 16th, 2008
Thankyou very much - that worked a treat!
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2007
Posts: 1
Reputation: jinzing is an unknown quantity at this point 
Solved Threads: 0
jinzing jinzing is offline Offline
Newbie Poster

Re: Updating multiple rows in mysql...how to add a checkbox!

 
0
  #4
Aug 14th, 2008
i was having the same problem and this solution worked great! thanks
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2009
Posts: 2
Reputation: withremote is an unknown quantity at this point 
Solved Threads: 0
withremote withremote is offline Offline
Newbie Poster

Re: Updating multiple rows in mysql...how to add a checkbox!

 
0
  #5
Feb 17th, 2009
I'm getting a Warning: Cannot modify header information - headers already sent by from
PHP Syntax (Toggle Plain Text) 
  1. <form name="form1" method="post" action="<? echo $_SERVER['REQUEST_URI']; ?>">

when the script hits
PHP Syntax (Toggle Plain Text) 
  1. if($result1){ 
  2. header("location:update_multiple.php"); 
  3. }

Otherwise it works like a charm. Any idea as to how to avoid this error?
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2009
Posts: 2
Reputation: withremote is an unknown quantity at this point 
Solved Threads: 0
withremote withremote is offline Offline
Newbie Poster

Re: Updating multiple rows in mysql...how to add a checkbox!

 
0
  #6
Feb 18th, 2009
I figured it out.

I needed to use ob_start(); at the top of my page, before any of the script runs, then ob_end_flush(); directly after the redirect.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2009
Posts: 1
Reputation: lemonizer is an unknown quantity at this point 
Solved Threads: 0
lemonizer lemonizer is offline Offline
Newbie Poster

Re: Updating multiple rows in mysql...how to add a checkbox!

 
0
  #7
Jun 24th, 2009
Using the code listed above, this is a subset of code I used for Radio buttons instead of checkboxes.

In addition, I was having trouble detecting the Submit button being used, so that section of the code was changed.

php Syntax (Toggle Plain Text) 
  1.   if ($row['YesNo']=="Yes")
  2.   {
  3.   echo "<td><INPUT type=\"radio\" name=\"YesNo".$row['ID']."\" value=\"Yes\" CHECKED> Yes <INPUT type=\"radio\" name=\"YesNo".$row['ID']."\" value=\"No\" > No</td>";
  4.   }
  5. 	else {
  6. 	echo "<td><INPUT type=\"radio\" name=\"YesNo".$row['ID']."\" value=\"Yes\" > Yes <INPUT type=\"radio\" name=\"YesNo".$row['ID']."\" value=\"No\" CHECKED> No</td>";
  7. 	}
  8.   echo "</tr>";
  9.   }
  10. echo "</table>";
  11. }
  12. ?>
  13. <input type="Submit" name="Submit" value="Submit">
  14. </form>
  15. <?php
  16. Interviewed='".$_POST['YesNo'.$id]."' WHERE ID=".$_POST['id'];
  17. //echo "<p>SQL:".$sql1;
  18.  
  19. //check if Submit button is active
  20. if (isset($_POST['Submit']))
  21. {
  22. 	echo "<p>submit pressed";
  23. 	foreach($_POST['id'] as $id)
  24. 	{
  25. 		$sql1="UPDATE table SET YesNo='".$_POST['YesNo'.$id]."' WHERE ID=".$id;
  26. 		$result1=mysql_query($sql1);
  27. 		echo "<p>".$sql1;
  28. 	}
  29. }
  30.  
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2009
Posts: 1
Reputation: Silvor is an unknown quantity at this point 
Solved Threads: 0
Silvor Silvor is offline Offline
Newbie Poster

Re: Updating multiple rows in mysql...how to add a checkbox!

 
0
  #8
Sep 7th, 2009
This is excellent, helped me greatly!

One question - how can I get it to INSERT a row if there is currently no row with that ID?

I want to be able to update all rows that it can update and insert rows which aren't currently there?

Thank you.
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Other Threads in the PHP Forum
Thread Tools Search this Thread



apache api array basic beginner binary body broken cakephp checkbox class cms code computing cron curl database date date/time delete display dynamic echo email error file files filter folder form forms function functions gc_maxlifetime global google host href htaccess html image include insert ip javascript joomla limit link list login mail memmory memory menu mlm msqli_multi_query multiple mycodeisbad mysql navigation oop parameter parsing paypal pdf php problem query radio random recourse recursion regex remote script search seo server sessions sms snippet source space sql static syntax system table thesishelp tutorial update upload url validator variable video web webdesign xml youtube
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC