Updating field on multiple rows

Megan posted this at 16:22 — 24th January 2007.

Joined: Jun 1999

It's me with my PHP problems again! This time I am trying to update a number of fields in my database at once.

First i have this page (took the password protection off so you can see it):

http://www.housing.uwaterloo.ca/profiles/admin/listprofiles.php

I need to check a number of boxes in the approved column, then update those records in the database. I got this working with this code:

<?php
// grab fields that were submitted
$fields = $_POST[\"approve\"]; //takes the data from a post operation and puts them into an array 
    if ($fields == \"\") {die(\"no records submitted\");} // if the fields array is empty, say that nothing was submitted

include '../library/db_connect.php'; // include database info

foreach ($fields as $ID) { // looping through the fields array
    echo $ID . \"<br />\"; // just print them to make sure it's working okay
    mysql_query(\"UPDATE profiles_individual SET approved = '1' WHERE ID = $ID\") or die('Database query error');        
}
?>

Then Liam told me it's not good to write to the database every time the loop goes through. It's better to use the loop to build up the query string then write it at once at the end. I think I can do that by adding the $ID onto a string, but I'm not sure what the query should look like in this case. I figure it should be something like this:

"UPDATE profiles_individual SET approved = '1' WHERE ID = id1, id2, id3, id4"

But that's not right. There are lots of search results for this on google but I can't seem to find the right solution.

I could just ignore this because I probably won't be releasing more than a few records at a time.

Megan
Connect with us on Facebook!