By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,166 Members | 1,130 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,166 IT Pros & Developers. It's quick & easy.

How to update multiple rows (php/mysql) for little league database

P: 9
I am a rookie coder and got some great help from Marcus last year so I thought I would make another request.

I am trying to retrieve all boys in one household into a form and edit the individual rows and then update with one submit button.

I can retrieve the rows and populate the form but the update does not work.

Thanks in advance

Here is the testing code obtained from http://www.phpeasystep.com/mysql/10.html

Expand|Select|Wrap|Line Numbers
  1. <body>
  2. <strong>Update multiple rows in mysql</strong><br>
  3.  
  4. <?php
  5. mysql_connect("","","") or die("Error: ".mysqlerror());
  6. mysql_select_db(""); 
  7.  
  8. $x='7147010725';
  9. $sql = "select * from theboard where hphone = $x";
  10. $result = mysql_query($sql);
  11.  
  12. // Count table rows
  13. $count=mysql_num_rows($result);
  14. ?>
  15.  
  16. <table width="500" border="0" cellspacing="1" cellpadding="0">
  17. <form name="form1" method="post" action="">
  18. <tr>
  19. <td>
  20. <table width="500" border="0" cellspacing="1" cellpadding="0">
  21.  
  22.  
  23. <tr>
  24. <td align="center"><strong>Id</strong></td>
  25. <td align="center"><strong>Name</strong></td>
  26. <td align="center"><strong>Lastname</strong></td>
  27. <td align="center"><strong>Email</strong></td>
  28. </tr>
  29. <?php
  30. while($rows=mysql_fetch_array($result)){
  31. ?>
  32. <tr>
  33. <td align="center"><? $id[]=$rows['id']; ?><? echo $rows['id']; ?></td>
  34. <td align="center"><input name="first[]" type="text" id="first" value="<? echo $rows['first']; ?>"></td>
  35. <td align="center"><input name="last[]" type="text" id="last" value="<? echo $rows['last']; ?>"></td>
  36. <td align="center"><input name="email[]" type="text" id="email" value="<? echo $rows['email']; ?>"></td>
  37. </tr>
  38.  
  39. <?php
  40. }
  41. ?>
  42. <tr>
  43. <td colspan="4" align="center"><input type="submit" name="Submit" value="Submit"></td>
  44. </tr>
  45. </table>
  46. </td>
  47. </tr>
  48. </form>
  49. </table>
  50. <?php
  51. // Check if button name "Submit" is active, do this
  52. if($Submit){
  53. for($i=0;$i<$count;$i++){
  54. $sql1="UPDATE theboard SET first='$first[$i]', last='$last[$i]', email='$email[$i]' WHERE id='$id[$i]'";
  55. $result1=mysql_query($sql1);
  56. }
  57. }
  58. //what the heck is this???
  59. if($result1){
  60. header("location:update_multiple.php");
  61. }
  62. mysql_close();
  63.  
  64. ?>
  65. </body>
  66.  
Aug 1 '10 #1
Share this Question
Share on Google+
7 Replies


TheServant
Expert 100+
P: 1,168
Re: "What the heck is this???" - It's only progressing to the next page or refreshing the current page (update_multiple.php) if the MySQL query was successful. It is not really for security, but more to save errors coming up for when users ask for funny things.

Have you tried to go through the code yourself to adapt it to your situation? I always prefer rewriting things so I understand each line, and find faster ways to do things.

Is your database structure the same? ie. ID, Name, Lastname, Email? Have you constructed your database?
Aug 1 '10 #2

P: 9
Yes the code has been adapted to fit my database.
Aug 1 '10 #3

TheServant
Expert 100+
P: 1,168
Sorry, didn't read the question correctly. I believe your problem is in your update statement:
Expand|Select|Wrap|Line Numbers
  1. for($i=0;$i<$count;$i++){ 
  2. $sql1="UPDATE theboard SET first='$first[$i]', last='$last[$i]', email='$email[$i]' WHERE id='$id[$i]'"; 
  3. $result1=mysql_query($sql1);
Try:
Expand|Select|Wrap|Line Numbers
  1. for($i=0;$i<$count;$i++){ 
  2. $sql1="UPDATE theboard SET first='".$first[$i]."', last='".$last[$i]."', email='".$email[$i]."' WHERE id='".$id[$i]."'"; 
  3. $result1=mysql_query($sql1);
The problem is calling array elements in strings.
Aug 2 '10 #4

P: 9
TheServant,
Is it Sunday or Monday in Australia?

Thanks for the help, but your solution did not work either. When does $submit become true? When does its value get checked?

It appears the for loop is not firing. I added an echo statement inside the loop and it does not show. Also $sql1 does not evaluate to true since it does not go to the page in the header statement.

Any ideas?
Aug 2 '10 #5

TheServant
Expert 100+
P: 1,168
It's Monday.

Ahh yes, didn't see that little if statement. You're right, one problem is $submit. It looks like it's missing a few lines of generating the $submit.

You will need to test for whether the submit button has been pressed. However it can also be seen that you have no definitions for $first, $last, $id, $email arrays which you also use.

You will need to have some definitions like:
Expand|Select|Wrap|Line Numbers
  1. $Submit = $_POST['Submit'];
  2. $first = $_POST['first'];
  3. $last = $_POST['last'];
  4. $email = $_POST['email'];
You should also be aware that an array's first key is 0, not 1. For ID 1, you will need array(0) - if you do not have someone with an ID of 0...

Also, all that PHP you have at the bottom of your page, should be at the top. The PHP function header('location:...') will not work if there has been ANY output. As in, if you have any HTML sent to the browser, it will not work. That's why it should be at the top.

Honestly, it's not very good code, and again, I recommend a do over.
Aug 2 '10 #6

P: 9
It was the only code I could find that attempted to update multiple rows on one submit.

I understand how set the variables from the $_POST as you show above. I have done that many times. What I don't know how to do is set $_POST variables from the array.

In other words there will be more than one $first, $last etc. Would a loop work on the page receiving the form POST?

Sorry for my lack of knowledge but I am learning as I go.
Aug 2 '10 #7

TheServant
Expert 100+
P: 1,168
No problem.

When you have something like name="email[]" then an array is sent in the POST variable: $_POST['email']

That means when you assign another variable to that:
Expand|Select|Wrap|Line Numbers
  1. $new_var = $_POST['email'];
It will assign the variable (which is an array) to the $new_var. So your POST variable can be an array of arrays.

As far as I can see that looping should work, however, I am a bit too busy to try it myself at the moment. ** Where is $count set?

I didn't mean to insult you, but there is not variable assignment in the code you posted. So I am guessing that you have not posted it all?

I will share some logic of how I would set up your page:
Expand|Select|Wrap|Line Numbers
  1. if (page request is from submitting a form) {
  2. Go through and validate $_POST and update MySQL table with values
  3. Set $message = "Table successfully updated";
  4. }
  5.  
  6. Call data from MySQL table
  7. Print HTML and make the form submit to the current page
  8. if (isset($message)) {
  9. echo $message;
  10. }
  11.  
That way, there is no header changes, your update code only runs if a form was submitted, but the rest of the page loads the same all the time. Also all of your PHP is at the top, and all your HTML is at the bottom - which is a good practice to get into.
Aug 2 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.