How to update multiple rows in mysql with php

First published on June 4, 2006

Here comes my first geeky PHP post. For people who have basic knowledge in updating MySQL information through a PHP form, you’ll notice that almost all of the tutorials only tell you how to update ONE record at a time. Well, I figured it must be a simple tweak to update multiple records. Unfortunately, I had no knowledge of arrays (which are the keys to the solution) and ended up Google-searching for many hours.

Well, here’s the solution! It isn’t elegant, as I am quite the amateur coder, but it works and contains all the basics that you’ll need.

Note that there is no data validation in this script. For example, if you know that a field should contain integers, you should use something like intval($fieldvalue) when processing the form. Most text fields can be displayed with something like htmlentities($fieldvalue,ENT_QUOTES). If your server does not have the “magic quotes” featured turned on, text fields should be processed with something like mysql_real_escape_string($fieldvalue).

Let’s assume you’re updating book information about different courses (as I was trying to do).

Here is the file to display and edit the book information:

<?php
// connect to the database
mysql_connect(hostname,username,password);

// select the database
mysql_select_db(database) or die("Unable to select database");

// run the query and put the results in an array variable called $result
$result = mysql_query("SELECT * FROM table ORDER BY course");

// start a counter in order to number the input fields for each record
$i = 0;


// open a form
print "<form name='namestoupdate' method='post' action='update.php'>\n";

// start a loop to print all of the courses with their book information
// the mysql_fetch_array function puts each record into an array. each time it is called, it moves the array counter up until there are no more records left
while ($books = mysql_fetch_array($result)) {

// assuming you have three important columns (the index (id), the course name (course), and the book info (bookinfo))
  // start displaying the info; the most important part is to make the name an array (notice bookinfo[$i])
  print "<input type='hidden' name='id[$i]' value='{$books['id']}' />";
  print "<p>{$books['course']}: <input type='text' size='40' name='bookinfo[$i]' value='{$books['bookinfo']}' /></p>\n";


// add 1 to the count, close the loop, close the form, and the mysql connection
++$i;
}
print "<input type='submit' value='submit' />";
print "</form>";
mysql_close();
?>

Here is the file to update the information (called update.php as referred to in the previous file:

<?php
// connect to the database and select the correct database
mysql_connect(hostname,username,password);
mysql_select_db(database) or die("Unable to select database");

// find out how many records there are to update
$size = count($_POST['bookinfo']);

// start a loop in order to update each record
$i = 0;
while ($i < $size) {
// define each variable
$bookinfo= $_POST['bookinfo'][$i];
$id = $_POST['id'][$i];

// do the update and print out some info just to provide some visual feedback
// you might need to remove the single quotes around the field names, for example bookinfo = '$bookinfo' instead of `bookinfo` = '$bookinfo'
$query = "UPDATE table SET `bookinfo` = '$bookinfo' WHERE `id` = '$id' LIMIT 1";
mysql_query($query) or die ("Error in query: $query");
print "$bookinfo<br /><br /><em>Updated!</em><br /><br />";
++$i;
}
mysql_close();
?>

This entry is filed under Web development tutorials and written by Peter Keung. You can follow any responses to this entry through the RSS 2.0 feed.

123 Responses to “How to update multiple rows in mysql with php”

October 25th, 2006 at 11:25 pm
Thomas (Belgium) says:

As yourself I was Google-searching for many hours for a sollution to update multiple records in one go. I found your post and gave it a try myself on my own tables and data and it works like a charm!! Thanks for that great post, it made my life so much easier!!

Best regards from Belgium!

Thomas
November 10th, 2006 at 8:39 am
Sam says:

Like Thomas, I’ve been search for most of the last day for this bit of code. You’re a star for posting it, Peter!

Now, you wouldn’t happen to also know how to have an addition number of empty rows below the ones that were returned, so that users can add more records into the database using the same from, would you?

Thanks a ton,
~Sam
November 30th, 2006 at 10:34 pm
Ghani Siraj says:

Yes, Peter. Your script works great and save hours for search on the net.It’s sample and clear to understand.
If you want to be redirected after updating the data just replace the code ( print “$bookinfoUpdated!“;
)
by
header(“Location: index.php”);

or change index.php to wathever you want to go to.
December 12th, 2006 at 4:25 pm
Kiki says:

This is a great tutorial, but I’m having trouble figuring out how to make the update script work because I have more than one field that could potentially be edited. In your example, the only field you can edit is bookinfo. I have 3 that could be edited. How would I find out how many records there are to update when each separate record might have changes in cmpletely different fields.

Please e-mail me with a solution, thanks a bunch!
December 16th, 2006 at 11:29 am
Peter says:

You would duplicate everything you see for bookinfo.

1) Add another input field
print "<input type=’text’ size=’40’ name=’anotherfield[$i]‘ value='{$books['anotherfield']}’ />”;

2) Define the posted variable
$anotherfield = $_POST['anotherfield'][$i];

3) Replace the query with this:
$query = "UPDATE table SET `bookinfo` = ‘$bookinfo’, `anotherfield` = ‘$anotherfield’ WHERE `id` = ‘$id’ LIMIT 1";

4) Replace the confirmation stuff with this:
print "$bookinfo $anotherfield<br /><br /><em>Updated!</em><br /><br />";
March 25th, 2007 at 4:07 am
Matt says:

Peter

This is great! Thanks so much

Matt.
March 30th, 2007 at 10:39 am
Erik says:

Your script worked when I couldn’t get any others to work. Bravo! The only error I had in the end was a SQL error because I needed to remove the single quotes from field names:
title=’$title’,th_img=’$th_img’,th_width=’$th_width’,th_height=’$th_height’,img=’$img’,width=’$width’,height=’$height’ WHERE id = $id

I got it to update 6 fields for multiple rows, though.
April 5th, 2007 at 9:56 am
Chris says:

Can’t get it to work? How does the UPDATE query know to look for the right record after you’ve created an array out of $id?

You have:
$id = $_POST['id'][$i];

Then in the query you update where id = $ID, but there won’t be and records where id = ['id'][$i].

So for me the script runs but doesn’t update anything because it can’t find any records with those ID #s
April 5th, 2007 at 10:04 am
Peter says:

How is your database table set up? In this case, “id” is the primary key, or index for the table. Thus, it is a unique identifier for each row. You need either this constant field or a combination of fields to uniquely identify the row to update.
April 5th, 2007 at 10:09 am
Chris says:

In my table it is called CalID (its a calendar of events).

Lets say I have a record with a CalID of 287. After I’ve run this line:

$id = $_POST['id'][$i];

and then echo $id, I get 22. So then in the UPDATE it is looking for a record with a CalID of 22, not 287.

What am I missing that would allow the server to parse the actual, original CalID from the array that is created that contains the CalID and the current $i ??
April 17th, 2007 at 2:19 am
matt says:

OMG this is unsafe!!! please filter the user variables… (at least use addslashes)
April 27th, 2007 at 7:48 pm
Wendy says:

Very helpful script, clean, simple, easy to understand.

Question — I have apostrophes in some of the text I wish to update, is there a way to encode the input so that I don’t get an error message? Thanks –
April 28th, 2007 at 1:43 am
Peter says:

When reading from the database, I suggest that you use the function htmlentities like so (can be combined into one line if desired):

$bookinfo=mysql_result($result,$i,”bookinfo”);
$bookinfo=htmlentities($bookinfo,ENT_QUOTES);

When writing to the database, I suggest that you prepare the data like so (also can be combined into one line if desired):

$bookinfo=$_POST['bookinfo'][$i];
$bookinfo=mysql_real_escape_string($bookinfo);
April 28th, 2007 at 11:43 am
Wendy says:

Beautiful, thank you!
May 9th, 2007 at 10:56 pm
edward says:

Actually there is a way you can do multiple inserts with only one mysql query which is probably way faster (although negligible).

INSERT INTO [TABLE] (fields1, field2, field3, etc…)
VALUES (value_a1, value_a2, value_a3, etc…), (value_b1, value_b2, value_b3, etc…), (value_c1, value_c2, value_c3, etc…), etc.

I usually do it by setting up an array using a while loop (or something similar) and implode…
$insert[] = “(value_a1, value_a2, value_a3, etc..)”;
$insert[] = “(value_b1, value_b2, value_b3, etc..)”;
$insert[] = “(value_c1, value_c2, value_c3, etc..)”;

INSERT INTO [TABLE] (fields1, field2, field3, etc…)
VALUES “. implode(“, “, $insert) .”

Just a note: all the fields and values have to match in count or you will get an error.
June 29th, 2007 at 7:04 am
John says:

Good script but… when trying to isolate records for updating using a foreach() on a recieved variable and a where clause in the query the records are returned fine except in the form the [$i] returns [0] for every returned record prior to update. Otherwise it works fine. Is there a way round this?? It can be done because it is used in phpMyAdmin but I’m b*gg***d if I can see how. – And my head hurts now.
July 11th, 2007 at 9:00 pm
azeo says:

Great site! Need some help. I have a large email list and would like to update my group id and this is what i came up with:

UPDATE `jos_lmusers` SET `group_id`=2 WHERE `users_id`=1 FROM `users_id`=1000 ;

But I dont want to update row my row, i would like to do row 1 to 1000, then 1000 to 2000. Can someone please help me to tell me what i need to add to this sql to update 1000 rows at a time or even 500.

Thanks in advance
July 11th, 2007 at 10:56 pm
Peter says:

Try:

UPDATE `jos_lmusers` SET `group_id`=2 WHERE `users_id` BETWEEN 1 AND 1000
July 12th, 2007 at 6:17 am
azeo says:

Thank you so much ! it worked like a charm.
July 12th, 2007 at 9:05 am
Mark says:

Good Script. I’ll like to know how can i use this script to insert new data instead of update?
July 12th, 2007 at 12:11 pm
Peter says:

First, generate the form with multiple fields for multiple database rows to insert. Instead of:

while ($i < $num) {

Use something like:

while ($i < 10) {

And instead of putting info into the fields, leave them blank:

print “<p><input type=’text’ size=’40’ name=’course[$i]‘ />: <input type=’text’ size=’40’ name=’bookinfo[$i]‘ /></p>\n”;

Then, use a similar form processor (with the same sort of loop) but use a query similar to this:

INSERT INTO table (bookinfo, course) VALUES(‘$bookinfo’, ‘$course’)
July 24th, 2007 at 3:38 pm
Virginia says:

Muy bueno!!! Gracias! Algunos cambios que tuve que hacerle, pero buenÃsimo que lo compartas, me salvaste! je
August 31st, 2007 at 12:32 pm
Simon Hodge says:

What a fantastic script, easy to understand, i’ve been trying for days to achieve this. Thanks you
September 11th, 2007 at 5:43 pm
stu says:

WOW What a STAR!!! Been trying to do this for days – Very easy to understand – THANK YOU!
September 26th, 2007 at 9:42 pm
missy says:

hye there peter!
this is an awesome code cos i’ve been searching for it for like.. months!
however,
mine have some problem.

i have to make a little calculation before updating table.

how am i going to do this?

i’ll give u some explaination here:

i have a table called table with field : id/number/total.

there will be a ‘list’ page whereas it will show u all of the list that need to be updated.

fields : id, number.

when a user enter ‘submit’ button, it will go to the updated process.

in the ‘updatedprocess’ page, there will be a small calculation.

example:
&total=number*365;

now the UPDATE table SET total WHERE id blablabla will take the place.

mine is not working.
please help!!!
October 17th, 2007 at 5:29 am
Alison says:

This is the business. My last multiple upload script required that i over write my whole table.

Much appreciated thanks
October 26th, 2007 at 4:54 am
Drew says:

Nice script thanks. I had a weird issue when I was trying to create a similar script. I used ‘;’ (semicolons) to separate multiple update queries into one query variable and then run the mysql_query().

which works fine in PHPmyAdmin SQL window, however I kept get an error when running on the server, must be some kind of maximum query limit built into the php.ini somewhere … just guessing.

Anywhoz, nice script
November 1st, 2007 at 4:40 am
Sasha says:

Thanks for the script! Worked fine. Tossing variables around in multiple arrays always does my head in.
November 2nd, 2007 at 8:55 pm
michael says:

thanks for this, it’s the only one i could understand after quite a few hours of googling.

works great
November 13th, 2007 at 3:20 am
Ã¶zgÃ¼r says:

sorry, but this is only an ugly solution.
looping through an array and updating in each iteration would cause a disaster if you try to update 20.000 records.

somebody must invent a way to update multiple records at one shot.
December 4th, 2007 at 2:39 am
PaulM says:

Well done, Peter. That was tremendously useful.

I had spent ages trying to get Dreamweaver to do a script for me, without success. With yours, the job was done in a couple of hours, and most of that time was spent being super-cautious with backups and test-copies because I was updating crucial information in a database of 3000 records and I was desperate for nothing to go wrong.
December 10th, 2007 at 7:35 am
peta says:

very helpful, thanks for posting. It certainly got me out of the code spaghetti I was stuck in (I am only a beginner), the other questions and your answers Peter, have been very useful indeed, thanks for taking the time… most decent of you.
December 14th, 2007 at 2:15 am
Sinej says:

Hi!

Instead of UPDATE I am trying to get to INSERT code. I have a page that has a shopping cart (items come from table “items”) which I would like to insert into table “itemsOrdered” upon presing submit button.
I simply can not figure the code out. How in the world can I put all these selected items into another table by pressing one button alone?

Hope someone can give me a hint…
December 14th, 2007 at 8:58 am
Peter says:

For multiple inserts, check out this comment and this comment.
December 23rd, 2007 at 11:08 am
fearnothing says:

Great script! Extremely valuable information – thank you!

I have a very large database to update, so I would prefer to select a subset in the MYSQL query (using a POST variable from an input form) for the first name of the surnames held in the table i.e all the names beginning with ‘A’ etc.

Your script only seems to work for those beginning with ‘A’ select – if I select for example those beginning with ‘G’ the update doesn’t work. Any suggestions on how I should be tweaking the code?

Best regards, FN.
December 26th, 2007 at 12:00 pm
Peter says:

Hi, this might not be the most efficient way, but this is what I do (suppose I wanted to select all rows where fieldtomatch starting with an “a” or “b”). Replace the very first MySQL query statement to this:

SELECT fieldnames FROM table WHERE fieldtomatch REGEXP '^[A-Ba-b]'

See here for more info about regular expressions.
January 8th, 2008 at 10:52 pm
Erin says:

EXCELLENT – Thank You!!!
January 15th, 2008 at 8:41 pm
Dan says:

Is there anything I would have to do special to this to make it work with checkboxes instead of text fields? It doesn’t seem to want to insert a “Y” into the field to indicate it is checked.
January 15th, 2008 at 10:47 pm
Peter says:

Yeah, this would be done on the PHP side. Suppose you had a checkbox named "checkme[1]". On the PHP side when the form is submitted, the value of $_POST['checkme'][1] would be "on". So then you could go:

<?php if($_POST['checkme'][1] == 'on') perform whatever database action you want; ?>

(don’t forget to validate)

In the form itself, you could have something like

<input type="checkbox" name="checkme[1]" <?php if($whatever_value[1] == 'yes') print 'checked '; ?>/>

If you’re referring to how to store this checkbox value in the database, use a field of the "boolean" or "tinyint(1)" type, so it’ll store only true (or 1) and false (or 0) values.
January 16th, 2008 at 12:19 am
Dan says:

Hi Peter-
Thanks for the help. Unfortunately, I’m not much of a programmer and I’m still having trouble. I’m trying to integrate your pieces into the original sample you provided but the update page doesn’t seem to want to either pick up which ones I have checked or else it doesn’t update the database correctly.

Do you think you could provide a more detailed sample and how it would fit exactly with your original code? I’m looking ideally for a final form like:

User1 [checkbox]
User2 [checkbox]
User3 [checkbox]

where it just records (either with a 1, or a Y) which boxes are checked and stores those. Eventually I’m going to add more checkboxes for each user but I’m hoping I can get those working once I get one here started.

Thanks again!
January 16th, 2008 at 12:48 pm
Peter says:

Let’s assume your checkbox is recorded in a field called "active" in the database and that it records "1" for checked and "0" for not checked. Your database fields are simply "id" "course" and "active". Let’s assume also that you are updating only "active".

Assuming also that you use the same sample code, replace this particular section for the form display where appropriate:

// start displaying the info; the most important part is to make the names arrays print "<input type='hidden' name='id[$i]' value='{$books['id']}' />"; print "<p>{$books['course']}: <input type='checkbox' name='userchecked[$i]'"; if ($books['active']) print ' checked'; print " /></p>\n";

In the form processing code:

// start a loop in order to update each record $i = 0; while ($i < $size) { // define each variable $bookinfo= $_POST['userchecked'][$i]; $id = $_POST['id'][$i]; $active = 0; if ($bookinfo == 'on') $active = 1; // do the update and print out some info just to provide some visual feedback $query = "UPDATE table SET `active` = '$active' WHERE `id` = '$id' LIMIT 1"; mysql_query($query) or die ("Error in query: $query"); print "<em>Updated!</em><br /><br />"; ++$i; }
January 23rd, 2008 at 12:44 pm
Jim says:

I have been trying this to work with the checkbox as Peter has mentioned and am having problems. When I hit submit, the correct records are not updated. If I update 5 records, it associates those updates with the first five records listed – not the five I updated. It does not appear to be associating the ID in the array. This is what I have done…

$i = 0;//counts entries

while ($row = mysql_fetch_assoc($result))
{
// start displaying the info; the most important part is to make the names arrays
echo “”;
echo “”;
echo “{$row['lname']}, {$row['fname']} {$row['mname']} ($row[ID][$i])”;
$i++;
}

and I process it using this…

$size = count($_POST['userchecked']);

// start a loop in order to update each record
$i = 0;
while ($i < $size)
{
// define each variable
$nalp_info= $_POST['userchecked'][$i];
$ID = $_POST['ID'][$i];
$nalp = 0;
if ($nalp_info == ‘on’) {
$nalp = 1;
}
// do the update and print out some info just to provide some visual feedback
$query = “UPDATE students SET ‘nalp’ = ‘$nalp’ WHERE ID = ‘$ID’ LIMIT 1″;
mysql_query($query) or die (“Error in query: $query”);
print “Updated!“;
++$i;
}

Any guidance someone can provide would be greatly appreciated.
January 23rd, 2008 at 1:05 pm
Peter says:

The key part there is the $ID variable. Is what you named as “ID” in your form actually the ID from the database or just an incremental count?
January 23rd, 2008 at 1:09 pm
Jim says:

The “ID” is from the database
January 23rd, 2008 at 1:32 pm
Peter says:

Try using a different variable for what is counted in the $size variable (ID is your safest bet). If you only check 5 boxes, the $size value will only be 5. But the ID count will be correct no matter how many boxes are checked.
January 25th, 2008 at 8:40 am
WillurdC says:

I have built a similar script for an application that I am working on and I am stuck. I have no problem creating the update form – That works great. I can submit the variable with POST to another page and display all of the correct values from the form on that page, so I know the form is working. However, when I create the loop to update the entries in the mysql table, only the first row is getting update and all the others are just ignored. I have read through every entry on there looking for a solution and I have been working through trial and error for 3 days and haven’t come up with anything that works.

From what I have read on other forums, I don’t think that anything is wrong with my code, I think that something on the server must be blocking the from happening – How can I get around this?
January 26th, 2008 at 1:51 pm
Peter says:

Hi Willurd,

Paste your code into a text file (stripping out all of the password or other sensitive information) and upload it to your server. Post a link to that file, then me or other visitors to this site can take a look and hopefully offer you some suggestions. I am no coding guru, and there are forums dedicated to helping people with code, but I’ll see if I can spot any problems in your code.
January 29th, 2008 at 9:38 pm
tt says:

ever tried:

“UPDATE table_name SET column_name1=’ value[$array]‘, column_name2=’ value[$array]‘ WHERE column_name=’ value[$array]‘ “;

?
March 4th, 2008 at 2:21 pm
Nathan says:

Great little tutorial! Much easier to understand than a lot of code trying to do this that i've seen. Like you said most people only show how to use links to update one record at a time and avoid checkboxes which do seem ideal for multiple row updates at the same time.
March 12th, 2008 at 3:40 am
uwem says:

thanks for the tip. God bless you
April 8th, 2008 at 2:59 pm
DataTrust Group says:

doesnt look like anyone mentioned the "IN" clause for updating multiple records

update opportunities set flag = ‘1’, queue = ‘this’ WHERE opportunity_id IN (1,2,3,4,5,6,7,8,9)
May 2nd, 2008 at 5:24 am
John MacKellar says:

Hi, i cant get this to work. when i post the variables over to the other page, it does not seem to recognise the "counting variable". e.g.
$stuff = $_POST['frm_rqst'][$i];

$i is the counting variable. thus going up by one every round it makes. This works if i enter a number into the posting variable. e.g. $_POST['frm_rqst1'] etc. So it is working on the page i am sending it from, but its just not accepting the $i as part of the name of the item being posted. any ideas?
May 3rd, 2008 at 12:00 pm
Peter says:

Make sure that your form fields are named frm_rqst[1], frm_rqst[2] and so on. Then put print_r($frm_rqst); into your form processor to make sure they’re being submitted properly as items in one array.
May 9th, 2008 at 7:15 am
Walter says:

hi! great script!

i have a question, i have a table with requests for other company to accept them or not, for that they shall tick a checkbox and click the button "accept". this must update the db field "state" of that request to 1, if the company clicks the button "reject" it must update the db field to 2. (0 is for not viewed and 4 is for finished).

Now the db will print a lot of lines with a checkbox in each of them, how can i do the buttons to update the db like that??

i’m a bit of a newbie with this so sorry is this sounds stupid..

thanks a lot!
May 9th, 2008 at 7:29 am
Peter says:

Hi, I’m not 100% sure as I’ve never needed to do such a thing, but you could try using multiple submit buttons (same type but different names). Then your form processor can check which button was hit and process things different based on that.

if ($_POST['accept']) do this;
if ($_POST['reject']) do this;
June 7th, 2008 at 12:37 am
spidernik84 says:

Thanks, your tutorial made my day, i had this need i could not satisfy for days!
June 19th, 2008 at 1:37 pm
Chuck says:

Awesome page so far! I have a bit of a different scenerio.
I am letting my visitor choose multiple items from a dropdown list. Once they do, I need to insert each item into a new row into the database. I only want to allow 9 items max to be inserted, even if they choose more.
The form field pulls the options from a different table in the database and looks like this:
<select name="counties[]" size="4" multiple class="formcontent" id="counties[]">
<option value=""<? if (!(strcmp("", $_POST['counties']))) {echo "SELECTED";} ?>>Choose Counties
</option>
<?
$query4 = mysql_query("SELECT * FROM counties WHERE state = ‘".$_POST['primary_state']."’ ORDER BY county", $conn);
$row4 = mysql_fetch_assoc($query4);
do { ?>
<option value="<? echo $row4['county']; ?>">
<? echo $row4['county']; ?></option>
<? } while ($row4 = mysql_fetch_assoc($query4)); ?>
</select>
</div></td>
</tr>
(I know I have mixed php and html here but I will fix that later)

Once they submit I can implode it and get a comma seperated list:
$counties = implode(", ", $_POST['counties']);

Next, I need to insert this info, along with other info, into seperate rows:

$insertSQL = "INSERT INTO listings (id, mem_id, state, county)
VALUES (”, ‘2’, ‘South Carolina’, ‘1st County’
),(”, ‘2’, ‘South Carolina’, ‘2nd County’
),(”, ‘2’, ‘South Carolina’, ‘3rd County’);
etc up to 9 max.

The id is auto generated for each row, the mem_id will be the same for each row, state will be the same for each row, county will be different for each row.

I’m looking forward to hearing from you.
Thanks in advance!

So, how do I take the array and do an insert?
June 19th, 2008 at 1:41 pm
Chuck says:

Walter,
if($_POST['checkbox'] == ‘accept’) { do this; }
else if($_POST['checkbox'] == reject) { do this; }
June 19th, 2008 at 4:26 pm
Chuck says:

Never mind. I wrote the code. It was easier than i thought. In case anyone needs it:

FIRST – here is the form select field:
<select name="items[]" size="4" multiple id="items[]">
<option value="item1">item 1</option>
<option value="item2">item 2</option>
<option value="item3">item 3</option>
<option value="item4">item 4</option>
<option value="item5">item 5</option>
<option value="item6">item 6</option>
</select>

HERE IS THE REST:
$num_rows = count($_POST['items']); // counts how many items are in the array
if($num_rows > 3) {$size = $3;} else {$size = $num_rows;} // only allow up to 3 items to be listed

$i=0;
while ($i < $size) {
$item = $_POST['items'][$i];
INSERT INTO listings (id, item) VALUES (‘ ‘, ‘".$item."’); // insert into db
print ”.$item.'<br>'; // show the list of items
++$i;
} // end while

I HOPE THIS IS USEFUL TO SOMEONE. I FIGURED MOST OF THIS OUT BY USING WHAT OTHERS WROTE ON THIS SAME BLOG. THANKS GUYS/GALS
July 8th, 2008 at 12:19 am
no says:

looping an update query requires more system resources than using one query…
Instead use a case statement…
August 30th, 2008 at 10:01 am
Zamm says:

Good script…this is the script that i’ve looking for..easy to understand..thanks a lot

Regard,

Zamm,
Malaysia
September 5th, 2008 at 9:35 am
Randy says:

I really appreciate your posting this script and information. I am trying to use it to update a table which has a listing of board members and member id’s associated with the organization members. I have modified the script to dynamically query the database and return the list of positions on the board. Now, I want to create dropdowns for each position where I can select a member for each position and then update the database with the new member id’s. For example, there are 3 potitions – President, Vice President, and Treasurer. And, there are 3 members in the organization – Bill, Steve, and Bob. I want to list each position with a corresponding dropdown where I can select Bill for President, Bob for Vice President, and Steve for Treasurer. How do you formulate the update query when it’s based off of 2 different query arrays?

Thanks,

Randy
September 5th, 2008 at 12:59 pm
Peter says:

Hi Randy, without going through your structure in detail, there really shouldn’t be anything different. The update script simply takes what’s posted to it. For example, assuming that the member table holds member information as well as their respective position IDs, then you’re only updating that one member table. For each entry in the form, you might have two pieces of data called, say, "member_id[]" (maybe in a hidden input field) and "position_id[]" (the name of each <select> tag). Or, if you’re adventurous, name the <select> tags like so: <select name="position_id[<?php print member_id; ?>]">

Then the update script pulls the member IDs and position IDs to formulate the query: UPDATE members SET position_id… WHERE member_id… (Or in the latter case, the keys of the position_id array as the member IDs and the values as the position IDs).
November 13th, 2008 at 6:19 am
qiqi says:

How to update if the ID (in WHERE clause) in an array like …WHERE ID = $id[]?

Answer: it’s not different than the model as outlined in the post. You don’t actually insert the $id array directly into the query. You loop through each item to make multiple UPDATE or INSERT statements.
November 16th, 2008 at 7:02 pm
AL-Muslim says:

I will not say thank like every one
I will say more than they in few word

"I hope to see you in Paradise my Friend"
December 9th, 2008 at 4:42 am
Son says:

Array variables in mysql queries?
I wrote: UPDATE publisher SET bank=bank+’$account[$i]‘ WHERE id=’$i’"
but it doesn’t work. Could tell me why?
Thanks.
December 9th, 2008 at 9:05 am
Peter says:

Hi Son, assuming that your $i variable has been incremented, the code you showed should work within a loop. It’s probably just a syntax error. Try enclosing your array variable with curly brackets like so {$account[$i]}.
December 10th, 2008 at 4:33 am
Son says:

Thank you for the answer. I changed to {account[$i]}, but it doen’t work too. Could me advise how to use field name like a array variable in a query? For example:
UPDATE trade_publisher SET bank=bank+’$account[username]‘ WHERE id=10’";
Problem is: username in this case is a field_name and I want to use it like an array variable.
December 10th, 2008 at 2:26 pm
Peter says:

Sorry, I’m not completely sure what you’re trying to do. Try posting your problem on a different forum where you can post your full code including the form, the form processor, the database structure, and your specific intentions.
December 15th, 2008 at 2:25 am
Bobeck says:

Hi Peter. Can you give me some tip on how to perform form validation on the "bookinfo" field? I am still at a loss in performing form validation on multiple rows update. Thanks.

Reply from Peter: Hi Bobeck. If you are using the model outlined in the post, the key is to do the validation within the “while” loop, directly after each $bookinfo variable is defined. You can put any validation statements there. This way, if validation fails, you can stop the “update” query at that specific row, based on that specific field value.
December 15th, 2008 at 10:59 am
Fuzz Martin says:

Thanks Peter. That worked great. I appreciate it.
December 19th, 2008 at 3:15 pm
Bobeck says:

Not exactly the same framework because I separeted the form submission code from the database access code using objects. Thank you for your feedback though. That helped a lot. It works.
January 4th, 2009 at 5:01 am
Very Grateful says:

Very nice! Like others who have posted above, this has saved me a lot of time and effort. THANK YOU!…

I do have one question that may be able to reduce processing time:
Because I don’t know what "Books" I want to change, my first query displays the complete list, and I added a checkBox at the beginning of each Row. I would like to have the update process ONLY update the rows where I checked the box, and skip all of the rest. Can you suggest how this could be coded?

Thanks in advance,
JTG
January 4th, 2009 at 12:30 pm
Very Grateful says:

It’s amazing just how much you can do when highly- cafienated… here’s the code to use a CheckBox to pick a subset of records to update, plus the code needed to update a TEXTAREA field (this was not intuitive): click here for the code
January 10th, 2009 at 5:15 pm
Restless says:

Thank you sooo much! I’ve been trying for hours to get my script to work and this simple while loop solution was exactly what I needed. Thank you a thousand times over!
January 15th, 2009 at 6:30 pm
rm says:

Thank you! it’s amazing! very nice! Great! Awesome! Simple and Clear! and easy to understand.

by the way peter do you have a code for multiple paging like..

100 pages then this will devided into 4…

<<previous 1 2 3 4…. next>>
January 18th, 2009 at 4:00 pm
Peter says:

Hi rm, see this post for an outline on pagination.
February 12th, 2009 at 9:21 pm
jamied says:

Peter-

your crap in golden!!!

thanks

j
February 23rd, 2009 at 8:57 pm
dea says:

Peter,
Thanks a million for the code. One question: Is there a way to set up the update response so it doesn’t repeat for every entry? In other words, how can I have it say "Update successful" only once?

Even better would be for it to identify only the entry that was modified and say update successful for bookinfo X.

I hope my question is clear and that I’m not missing something from the original code.

Many thanks,
d

Reply from Peter: you can use the function mysql_affected_rows() after the query. In this case, if that function returns 1, you can provide the appropriate feedback for that given update.
February 23rd, 2009 at 10:33 pm
dea says:

Thanks Peter!

In the interest of contributing to the knowledge base here, what I ended up adding was:

$affected = mysql_affected_rows();

if ($affected > 0)
{
print "$var1 / $var2 / $var3 – <em>Updated!</em><br><br>";
}

++$i;
}
if ($affected == 0)
{
echo "No updates made!";
}

I’m running this with 3 variable (as you can see). If the affected rows is greater than 0 then it spits out each line that was changed. If the affected is 0 (which is all other records and could be a lot) it only echos "no updates made" once.
February 26th, 2009 at 1:16 am
jk says:

THANK YOU!! had really got stuck not getting the loop to work and now its perfect… my final code, which might be useful for someone…
March 2nd, 2009 at 4:52 am
john says:

when i visit the page i just get a submit button – no records…

i think i’m getting confused with the bookinfo field?

basically i have ID, Restaurant and Verify fields in a table called Alabama…

i just want to display the Verify fields if 0 and change them to 1 if i want to display the record…

See John’s code here
March 18th, 2009 at 2:27 am
Casper Gørup says:

YES! – thanks for posting this! It worked first time i built it in my code ;o) nice
April 1st, 2009 at 3:29 pm
fedya says:

Thank you so much, exactly what I was looking for.
April 3rd, 2009 at 10:41 am
dforeigner says:

Hey Peter, thanks a lot for sharing your code.
I’ve got a question, is there a way to hide the database login details? Maybe by keeping that specific part of the code in another non public file. I’m a newbie at this, sorry if the question doesn’t make sense.

Cheers

Reply from Peter: Generally, with a well-validated and secure script, the public shouldn’t be able to hack your code and view arbitrary variables. However, you could include the database connection credentials in a separate class, where they are only used in that class. If you do an Internet search for private and protected PHP variables, you can probably find some free tutorials and sample code about that.
April 18th, 2009 at 5:33 pm
Carl says:

Hi Peter,

Thanks for the tut, i’m a beginner in php starting with dreamweaver but im finding it limited but a good starting point.
I have managed to implement your tut with success works great.

I am having issues trying to work out how i can update only the records that have a tick in the checkbox.

Thanks.

Reply from Peter: Please see this comment and the ones that follow it.
May 7th, 2009 at 8:38 pm
robkir says:

Hi Peter,
Thanks for not only giving us a clever program but also for the clear and concise explanations. It’s refreshing to get such a complete guide on how to set up and tweak the program for our own use. You’re a good teacher. I hope you have other program ideas to share.

Cheers,
robkir
May 15th, 2009 at 3:07 am
Equinoxe5 says:

Hi,
Excuses for my bad english. I try to expose my problème.
I have a table with seven fields.
Imagine the ID are 1 2 3 4 5 6 and 7.
If i update the seven fields, OK, my script (It is almost like your’s) works fine.
But, if i want tu update Only a few fields, i have a problem.
Imagine, i want tu update only fields 2 5 and 6…. my update is done on fields 1, 2 and 3…. because my count [$i] take the first field and the second, and the third….
Have you a solution ?

Reply from Peter: See the discussions about using checkboxes.
June 11th, 2009 at 11:25 am
Janice says:

Thank you so much for this! It was exactly what I needed and it worked perfectly!
June 18th, 2009 at 2:43 am
djjjozsi says:

@Equinoxe5 :

if you pass the ID numbers in a hidden field, they will identify which ID’s needed to be updated. Use the foreach to get the checkboxes states,
$_POST["ids"] stores the ID list,
$_POST["checkbox1"] and $_POST["checkbox2"] is the two checkbox field.

foreach($_POST["ids"] AS $key)
{
$val1=empty($_POST["checkbox1"][$key]) ? 0 : 1;
$val2=empty($_POST["checkbox2"][$key]) ? 0 : 1;
$sql="update table set `field1`=’$val1′, `field2`=’$val2’";
mysql_query($sql) or die(mysql_error());
}
print "Updated succesfully…";

i’v got a multiple updater script (designer), here:
http://phpcode.hu/teszt/multiple_updater/
June 25th, 2009 at 9:49 am
Chuck says:

Peter!
I Yahoo’d for "mysql php update multiple rows" and you were number 1. Congrats on that!
Love the code. I only had to make a few changes because I use single quotes instead of double quotes (ie: name="orderby['.$i.']") and it works wonderfully!!

Thanks so much for the code.
It saved me so much time.
June 30th, 2009 at 4:12 am
Jester says:

Can anyone help me my code is here.

The problem is that i am newbie with php and i don’t know how to update the table.. when i click submit an error occured "duplicate entry 90066" Can anyone help me out.. please..

Reply from Peter: There’s a great, online, free PHP book that also covers basic MySQL stuff here. The good stuff on working with databases is at about here.
July 21st, 2009 at 6:30 pm
Lewis says:

Just to say thank you for your awesome code. I’ve been trying to figure this one out for a long time and this is the first time I have got it to work.

Must have tried every code snippet and tutorial I could find out there folks and Peter’s code is the only one that works and is easy to understand. This page should come up higher in google searches for this type of script, would have saved me hours fruitless searching.

I have used it to change the sort order field in my database. Was very tired of editing each integer one by one and adjusting the numbers to achieve my desired sort order. Now I can update them all at once! Thanks again!
September 3rd, 2009 at 1:14 pm
Mark says:

That’s a good piece of code.

Using it for editing rows 10 fields wide and it works very well :o)
September 11th, 2009 at 12:15 pm
Mohamad says:

Editing Mulitple rows with PHP is like a nightmare for me, never tried to make it, after googling I found ur post, and i am very glad, i will certainly work on it tomorrow at work.
Good luck AND many thanks
October 14th, 2009 at 5:58 am
Chris says:

Hi Peter

Excellent piece of code. Brilliant, well done & thanks.

I’m trying apply a common value to many records (probably coming from radiobutton).
If I have some time I might see if I can tweak it to work with :

UPDATE survey SET `bookinfo` = ‘$bookinfo’ WHERE `id`IN (‘$id’, ‘$id’, ‘$id’, ‘$id’,……. )

Doing it this way would significantly reduce the calls to MySql.
What do you thing?

Regards from sunny Scotland.
Chris

Reply from Peter: That would definitely be efficient, unless you have a really massive statement (so massive that it would cause memory issues).
January 8th, 2010 at 1:03 am
John says:

Seems to be working great for everyone else but it just won’t run out of the box for me.
Using MySQL client version: 5.0.51a
I am getting:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource
Using:
$connection = mysql_connect(localhost, john, redrider1, peterscrap)
OR die("Database connection failed: " . mysql_error());
and nothing else, I am connecting to the database. It throws the error at the:
while ($books = mysql_fetch_array($result)) {
line of code.
For some reason the $result is unusable to mysql_fetch_array.
Has anyone else experience the same? If so could you tell me how you fixed it? Thanks.
January 10th, 2010 at 9:56 am
John says:

Ok, I figured it out. It was my database connection. I was using a mysqli setup for mysql. Changed everything to mysql syntax and it works. Thanks
January 13th, 2010 at 9:06 am
julieann says:

hi there.. i’m a student from the philippines, thank you for your post it really helped me out.
March 18th, 2010 at 11:36 am
Trevor Johnson says:

Great! Thanks! been looking for hours for this i was having a really hard time try to add a title and description filed for my image uploads and this did the trick because i have my pages broken into three parts

1) Upload Images
2) put in the title and description
3) and save!

And Again Thanks!

Best Regards Trevor….
March 26th, 2010 at 4:55 am
chand says:

hey thanks man…………….you saved my day!!!!!!!!!!!!!!!
April 22nd, 2010 at 1:40 pm
Michael says:

Thx alot for that sweet piece of code Helping alot.
June 29th, 2010 at 12:33 am
Erik says:

Hello everyone,

I now have this script to work, but here is my question. How do you adjust the script to edit only 1 row? This script is for every row in a table, but i need to edit 1 row at the time.

Could somebody please explain to me how to adjust this script for that purpose?

Thanks already.

Reply from Peter: Modify that very first “SELECT” statement with the appropriate WHERE condition for your needs.
October 14th, 2010 at 11:21 am
Alfredo Gonzalez says:

Dude, thanks for this code, i adapted it to my project and worked fine
November 12th, 2010 at 8:35 pm
owain says:

Thanks so much! Spent a lot of time trying tutorials and this is the only one that has worked for me.

I did have to comment out the mysql_close(); lines though for some reason. Before I did so, I couldn’t view the page at all. It gave me some "Website not available" error….

Anyway, apart from that, great success!. Thanks so much!
December 16th, 2010 at 12:39 pm
zach says:

Awesome tutorial! I did have a few issues though:

I copied and pasted your code after a while because of frustration, and then tried to change the print commands to echo commands, but for some reason it wouldn’t work.

Does anyone know why echo wouldn’t work instead of print, here?

Reply from Peter: echo and print in PHP are nearly the same; syntax-wise they are the same. So, the problem is likely due to something else.
January 29th, 2011 at 2:05 pm
splurp mcgrup says:

In regards to the poster saying this script isnt safe. it’s plenty safe, it’s not intended for anyone but the developer himself to use.

i just used a script i based off this one to process 500,000 entries so whoever said it would choke at 20,000 doesn’t know what they are talking about.

it’s a solution, albeit inelegant, that’s what programming sometimes entails. most of the comments here seem to be from people that are bored armchair programmers.
March 17th, 2011 at 8:52 am
rkayd says:

Thanks for the life saver. I had to set up a form for a client using 7 frames with a single form with a submit button in each frame (which meant having to have 7 different (but nearly identical forms on the website). It looked horribly amateur but with this code it looks and works great. The table that the public sees and the table they use for updating look almost identical except for the input field in the update field.
March 24th, 2011 at 6:18 pm
Daniel says:

Hey I am trying to do this but I have an html table and when I try to setup the name using $i is not workng how can I do it?

I tried this:
<label for="clientetabla"></label>
<input name="clientetabla <?php [$i]; ?>" type="text" id="clientetabla" value="<?php echo $row_select['CLIENTE']; ?>" size="10" readonly="readonly" />

but it is not working, how can I do it?

Reply from Peter: You should print the square brackets outside of the PHP call.
April 17th, 2011 at 3:54 am
Brad says:

Hey, I know there are a couple of answers on here about updating multiple fields but I couldn’t make sense of them (Being an amateur).

I have 3 fields for multiple rows that can be edited but still not sure how to get the code to work. So far the code I have will only update the first row?

Any ideas and help would be great! Thanks
April 22nd, 2011 at 3:20 am
Mic says:

I wonder if running 500k UPDATE queries as someone mentioned here is a good idea. I doubt it. Wouldn’t be faster to use a temp table and than an update query (with the join)?
April 22nd, 2011 at 6:01 am
Mic says:

2Mic: yeah, copying the data twice would be much faster (irony). And you talk to yourself…

Seriously, I wish there would be some benchmark of various possible solutions to update data, showing which is faster in what case… and then there is a PDO thing…
July 12th, 2011 at 5:49 am
Brod says:

how can i use both in one php file.

Reply from Peter: In general terms, wrap both parts in an “if” statement. The processing code would fire if the POST variable “submit” is present (based on the value of the submit button). The “else” fallback would be the form itself.
August 3rd, 2011 at 11:07 am
Marc says:

Hi Peter,

Your tutorial works fine. I have implemented it to update the sailing calendar on our club. It works like a treat i.e. reducing the amount of work.
September 1st, 2011 at 1:22 am
ibar says:

Thanks a lot for this query. I’ve been trying out several different update queries you can find on the net, but this one was the only one that actually worked.
Really great job and keep it going
November 8th, 2011 at 12:09 pm
Aslam says:

Really thanks a lot….
Past few days i was searching for this and tried out a lot but did’t worked anything. Thanks again.
November 14th, 2011 at 2:45 pm
Jason says:

After searching for hours finally found your way and it worked. Thanks!
December 1st, 2011 at 10:08 pm
popsdaddy says:

Updated my 211 rows and 5 columns correctly. Saved a lot of time vice doing each record individually. Thanks!
February 24th, 2012 at 2:57 pm
someone says:

Thank you so much!
February 28th, 2012 at 9:41 pm
ozillae says:

It’s good tutorial, … Can you help me????
I want to make attendance list, column 1 to 31, I try with for statement to generate it.
And rows I use paging 50 rows every page. I don’t know, how to update it….
I need your help please….. thanks
May 15th, 2012 at 6:40 am
Matthias says:

Dude, you just saved my life. Excellent job, thanks a lot
June 17th, 2012 at 4:05 am
prakriti says:

i m having problem to update multiple arrray in the same query
the code u suggested gives error saying error in query
$bookinfo= $_POST['bookinfo'][$i];
this value is not getting while it is printed and not updated in database too
November 14th, 2012 at 11:53 pm
Lilu says:

I really appreciate your teaching! I got a problem keeping the selected option ‘selected’ after updated~~
Anyone can help me? My code as below:

print "<td width=’80px’ align=’center’ valign=’top’>
<select name=’yos_deal[$i]‘ id=’yos_deal[$i]‘>
<option value=’未處理’ if(!(strcmp(‘未處理’,{$books['yos_deal']}))){echo ‘selected=’selected”;} >未處理</option>
<option value=’有庫存’ if(!(strcmp(‘有庫存’,{$books['yos_deal']}))){echo ‘selected=’selected”;} >有庫存</option>
<option value=’追加中’ if(!(strcmp(‘追加中’,{$books['yos_deal']}))){echo ‘selected=’selected”;} >追加中</option>
<option value=’已出貨’ if(!(strcmp(‘已出貨’,{$books['yos_deal']}))){echo ‘selected=’selected”;} >已出貨</option>
<option value=’暫缺貨’ if(!(strcmp(‘暫缺貨’,{$books['yos_deal']}))){echo ‘selected=’selected”;} >暫缺貨</option>
<option value=’已斷貨’ if(!(strcmp(‘已斷貨’,{$books['yos_deal']}))){echo ‘selected=’selected”;} >已斷貨</option>
</select>
</td>\n";