Updating Multiple Rows in MySQL using PHP

Submitted by: 
admin
Monday, October 3, 2011 - 11:54
Language: 
PHP
Visitors have accessed this post 36749 times.

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. ?>

style="display:block; text-align:center;"
data-ad-format="fluid"
data-ad-layout="in-article"
data-ad-client="ca-pub-3664195971165750"
data-ad-slot="8323940646">

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. ?>

Import the SQL Statement into your database called "students" using PHPMyAdmin included in the zip file.

Tags: 
PHP/MySQL
php tutorial
update multiple rows
admin's picture

Article by admin

admin has submitted 555 source code / articles.

If you like this post, you can follow SourceCodester on Twitter.

Subscribe to SourceCodester feed via RSS or EMAIL to receive instant updates.

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Comments

Anonymous (not verified)

Tue, 10/04/2011 - 09:33

thanks alot sir for this solution. It has been a problem on my mind for quite some time now. Another big problem i have is how to create a line graph using data from a database such that when the database is altered, the graph automatically updates itself. if you can offer any help at all, please do so by sending me a mail via ; [email protected]. Thank you.

Anonymous (not verified)

Tue, 10/04/2011 - 13:39

can you create a directory system using php? pls.. i need this. my prof. give us a code does not working thats why dnt know how to do that system. so pls. can you create a directory system. any directory like telephone directory, employee directory etc. anything that you can do. pls. help me. :((

Anonymous (not verified)

Wed, 10/05/2011 - 04:08

Hi sir,,hahaha,,,,thank you gd,,,na kwa ko na kag na try ko na man butang sakun system,,lamat gd nga madamu,,hahaha,,uhm,,,,ang prob nlg is that,on how to add regular load to a specific students,,,rehus sg enrollment system,ke sa sbg ang system ko ga add course individualy,,,t,,it realy takes tym for the admin to input such data,,hmmpt,,ask ko tne sir panu mag add regular load sg students para mpahapus ang ubra sg admin or the registrar..pa check ko cmu sir if mag balik ko sa skul...kag thank you gd liwat sa codes...hahah....,,,,by:Smool Eztremos

Anonymous (not verified)

Wed, 11/23/2011 - 05:11

Thanks a lot, this was very helpful!

beni (not verified)

Fri, 02/03/2012 - 03:55

As you wrote there is no many tutorials on this subject,
helped me very much.

Anonymous (not verified)

Tue, 01/22/2013 - 14:25

Sir,
U have helped me a lot..I was really looking for the this for the last one week..Ur code is simple and very very dramatic ...Lot of thanks,

Anonymous (not verified)

Tue, 03/12/2013 - 05:38

Thanks a lot !!! Sir you have helped me a lot.

Anonymous (not verified)

Tue, 05/07/2013 - 23:15

It has been very useful and with a clear code, thank you !!!!!

Anonymous (not verified)

Sun, 06/09/2013 - 05:49

Thank you very much, your code helped me a lot, thanks.

prem_123 (not verified)

Wed, 08/07/2013 - 12:03

sir,
can i change the text box which is going to be updated to combo box with my own option values
means i want combo box in place of text box,. with the pre filled option and some some my provided options

Evans Mwambije (not verified)

Fri, 09/20/2013 - 23:39

It very nice code dear

Amir Weinberger (not verified)

Wed, 06/25/2014 - 17:46

Hi,

Thanks for the great code.

How does it work with SELECT fields?
When I fill the SELECT field with table data, I don't know how to pass the selected value to the processing page. I get there a blank value

Thanks again!

sanjay sharan (not verified)

Tue, 07/19/2016 - 09:48

I found this site really great. Giving solutions without any pomp and shows which other websites actually doing.
Great! cannot getting appropriate word to praise

Add new comment

More information about text formats

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3>
  • You may insert videos with [video:URL]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.