How to Updating Multiple Rows in MySQL using PHP

There are so many tutorials on how to update one (1) record at a time. But only few tutorial on how to update multiple rows at once.

I dedicate this tutorial to my 3rd year students.

Save the following code as select.php:

1. <?php
2. mysql_connect("localhost","root","");
3. mysql_select_db("students") or die("Unable to select database");
4.  
5. $sql = "SELECT * FROM students ORDER BY id";
6.  
7. $result = mysql_query($sql) or die($sql."<br/><br/>".mysql_error());
8.  
9. $i = 0;
10.  
11. echo '<table width="50%">';
12. echo '<tr>';
13. echo '<td>ID</td>';
14. echo '<td>Name</td>';
15. echo '<td>Address</td>';
16. echo '</tr>';
17.  
18. echo "<form name='form_update' method='post' action='update.php'>\n";
19. while ($students = mysql_fetch_array($result)) {
20. echo '<tr>';
21. echo "<td>{$students['id']}<input type='hidden' name='id[$i]' value='{$students['id']}' /></td>";
22. echo "<td>{$students['name']}</td>";
23. echo "<td><input type='text' size='40' name='address[$i]' value='{$students['address']}' /></td>";
24. echo '</tr>';
25. ++$i;
26. }
27. echo '<tr>';
28. echo "<td><input type='submit' value='submit' /></td>";
29. echo '</tr>';
30. echo "</form>";
31. echo '</table>';
32. ?>

Save the following code as update.php:

1. <?php
2. mysql_connect("localhost","root","");
3. mysql_select_db("students") or die("Unable to select database");
4.  
5. $size = count($_POST['address']);
6.  
7. $i = 0;
8. while ($i < $size) {
9. $address= $_POST['address'][$i];
10. $id = $_POST['id'][$i];
11.  
12. $query = "UPDATE students SET address = '$address' WHERE id = '$id' LIMIT 1";
13. mysql_query($query) or die ("Error in query: $query");
14. echo "$address<br /><br /><em>Updated!</em><br /><br />";
15. ++$i;
16. }
17. ?>