How to update multiple mysql rows with PHP

Tags: ,

Did you ever want to be able to update a dynamic number of rows in your mysql table and didn’t know how? For example: like on myspace when you edit your photo album, you have a gallery and you list one of your albums with x pictures in it. You want to be able to edit all the pics at once and then save all the changes with just one click, without the need to save every picture, one by one. How to do it?

This is how I did it, I don’t think this is the best way, so if you know a better one you are very welcome to put it into the comments.

We need the form, so let’s start with it:

<form method="post">
	<input type="text" name="name[]" value="picture 1" /><br/>
	<input type="hidden" name="id[]" value="1" />
	<input type="text" name="name[]" value="picture 2" /><br/>
	<input type="hidden" name="id[]" value="2" />
	<input type="text" name="name[]" value="picture 3" /><br/>
	<input type="hidden" name="id[]" value="3" />
	<input type="submit" value="Save changes" name="edit" />

What we did here is make a form with six (6) input fields in it, three text inputs and name them as name[] and three hidden inputs with the id of the picture, so when we submit our form, we get an array as the result. We could make any number of fields we want this way (if we read an album we would make a loop that displays an input for every picture in it). The array will look like this:

    [name] => Array
            [0] => picture 1
            [1] => picture 2
            [2] => picture 3

    [id] => Array
            [0] => 1
            [1] => 2
            [2] => 3

    [edit] => Save changes

Now we have our beautiful array with new picture names and their ids. What we do next is use php to save these values to our database. Let’s say we have our pictures information in the table named pictures.

for ($i = 0; $i < count($_POST['name']); $i++) {
	mysql_query('update pictures set
	name = "' . mysql_real_escape_string($_POST['name'][$i]) . '"
	where id = "' . $_POST['id'][$i] . '"');

What I did here is count the array “name”:


and do a loop that will repeat itself that many times:

for ($i = 0; $i < count($_POST['name']); $i++) {

In the loop, mysql updates the name of the picture with id $i with the name of the field $i. So it goes like:

update pictures set name = "picture1" where id = "1"

This is it, the loop in this case repeats 3 times but it would work the same way for 10 or 15 fields.

I don’t know if what I wrote is understandable, I hope it is and it will help you. If you don’t understand something, you are very welcome to ask it in the comments!

If you liked this post think about subscribing to my RSS feed and prevent missing anything interesting. It's free, fast and doesn't hurt. Promise. Click here.
Related posts: