Arrow

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();
?>

Arrow

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


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


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


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


  4. 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!


  5. 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 />";


  6. Matt says:

    Peter :)

    This is great! Thanks so much :)

    Matt.


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


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


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


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


  11. matt says:

    OMG this is unsafe!!! please filter the user variables… (at least use addslashes)


  12. 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 –


  13. 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);


  14. Wendy says:

    Beautiful, thank you!


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


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


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


  18. Peter says:

    Try:

    UPDATE `jos_lmusers` SET `group_id`=2 WHERE `users_id` BETWEEN 1 AND 1000


  19. azeo says:

    Thank you so much ! it worked like a charm.


  20. Mark says:

    Good Script. I’ll like to know how can i use this script to insert new data instead of update?


  21. 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’)


  22. Virginia says:

    Muy bueno!!! Gracias! Algunos cambios que tuve que hacerle, pero buenísimo que lo compartas, me salvaste! je


  23. Simon Hodge says:

    What a fantastic script, easy to understand, i’ve been trying for days to achieve this. Thanks you


  24. stu says:

    WOW What a STAR!!! Been trying to do this for days – Very easy to understand – THANK YOU! :)


  25. 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!!!


  26. Alison says:

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

    Much appreciated thanks


  27. 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 :-)


  28. Sasha says:

    Thanks for the script! Worked fine. Tossing variables around in multiple arrays always does my head in.


  29. michael says:

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

    works great


  30. ö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.


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


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


  33. 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…


  34. Peter says:

    For multiple inserts, check out this comment and this comment.


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


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


  37. Erin says:

    EXCELLENT – Thank You!!!


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


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


  40. 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!


  41. 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;
    }


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


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


  44. Jim says:

    The “ID” is from the database


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


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


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


  48. tt says:

    ever tried:

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

    ?


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


  50. uwem says:

    thanks for the tip. God bless you


  51. 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)


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


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


  54. 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!


  55. 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;


  56. spidernik84 says:

    Thanks, your tutorial made my day, i had this need i could not satisfy for days! :D


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


  58. Chuck says:

    Walter,
    if($_POST['checkbox'] == ‘accept’) { do this; }
    else if($_POST['checkbox'] == reject) { do this; }


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


  60. no says:

    looping an update query requires more system resources than using one query…
    Instead use a case statement…


  61. Zamm says:

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

    Regard,

    Zamm,
    Malaysia


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


  63. 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).


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


  65. 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"


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


  67. 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]}.


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


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


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


  71. Fuzz Martin says:

    Thanks Peter. That worked great. I appreciate it.


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


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


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


  75. 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. :D Thank you a thousand times over!


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


  77. Peter says:

    Hi rm, see this post for an outline on pagination.


  78. jamied says:

    Peter-

    your crap in golden!!!

    thanks

    j


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


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


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


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


  83. Casper Gørup says:

    YES! – thanks for posting this! It worked first time i built it in my code ;o) nice


  84. fedya says:

    Thank you so much, exactly what I was looking for.


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


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


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


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


  89. Janice says:

    Thank you so much for this! It was exactly what I needed and it worked perfectly!


  90. 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/


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


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


  93. 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!


  94. Mark says:

    That’s a good piece of code.

    Using it for editing rows 10 fields wide and it works very well :o)


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


  96. 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).


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


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


  99. julieann says:

    hi there.. i’m a student from the philippines, thank you for your post it really helped me out.


  100. 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….


  101. chand says:

    hey thanks man…………….you saved my day!!!!!!!!!!!!!!!


  102. Michael says:

    Thx alot for that sweet piece of code :) Helping alot.


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


  104. Alfredo Gonzalez says:

    Dude, thanks for this code, i adapted it to my project and worked fine :)


  105. 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!


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


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


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


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


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


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


  112. 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…


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


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


  115. 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 :)


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


  117. Jason says:

    After searching for hours finally found your way and it worked. Thanks!


  118. popsdaddy says:

    Updated my 211 rows and 5 columns correctly. Saved a lot of time vice doing each record individually. Thanks!


  119. someone says:

    Thank you so much!


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


  121. Matthias says:

    Dude, you just saved my life. Excellent job, thanks a lot :-)


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


  123. 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";

Speak your mind

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word