Updating multiple records

amandab

I am creating a page where I am displaying multiple records and want to be able to update these records with different values. How can I update multiple records?

Sxooter

The way I do it is to make your form have multiple sections and name them something like:

<input name="firstname[0]...
<input name="comment[0]...
<input name="field88[0]...

SKIP SOME SPACE

<input name="firstname[1]...
<input name="comment[1]...
<input name="field88[1]...

and so on. Then you can retrieve them by iterating over each set of data that comes back in the array. you can evern get fancier, and do it like this:

<input name="record[0][0]...
<input name="record[0][1]...
<input name="record[0][2]...

SKIP SOME SPACE

<input name="record[1][0]...
<input name="record[1][1]...
<input name="record[1][2]...

And then just iterate an array of arrays:

$formelements = $_POST['records']
foreach ($formelements as $k=>$f){
    foreach ($f as $k2=>$field){
        print "record number ".$k." Field number ".$k2." Field value: ".$field."<BR>\n";
    }
}

Roger_Ramjet

Depending on your version of mysql, there are a couple of tricks you can use to update multiple rows, each with a different value, in 1 query. Otherwise, the only way to avoid running a query for each row inside a loop is to use a work table.

Work Table - 3 queries
query 1
INSERT the primary key and the new data into a work table
query 2
UPDATE the main table from the work table through a join on the primary key
query 3
DELETE all the rows from the work table ready for next time

version 3.55 or higher - 1 query

REPLACE INTO table
VALUES (id=$ids[0], col1=$var1[0], ...),(id=$ids[1], col1=$var1[1],...) ....etc

This works because for can submit multiple value lists to replace multiple rows with the updated data. You will need to supply the full record, ie all columns.

version 4.1.0 or higher - 1 query

INSERT INTO table
VALUES (id=$ids[0], col1=$var1[0], ...),(id=$ids[1], col1=$var1[1],...) ....etc
ON DUPLICATE KEY UPDATE

This can be used to update the rows because the primary keys already exist.

You will still need to run a loop to extract the data from the post arrays and build the queries, but will not have to run an update query for each row. The saving will of course depend on how many rows you are processing.

Sxooter

Roger, have you tested it to see if it's really any faster? Just wondering.

I wish PostgreSQL would implement this syntax. I believe it's in the SQL spec, just no one's gotten around to it, and using prepared queries et. al. gives the same performance gain, so there's no carrot there, so to speak.

I was just wondering if it's noticeably faster in MySQL to use that way.

Roger_Ramjet

Even with only a few rows to update and when both are on the same server, you would save the overhead of parsing the sql for each additional query.
Add overhead for table-locks, opening the primary index, and task-queues; which could be quite considerable on a busy server.
Although you would be using the same db connection each time, you will still have to wait for the db server to service it each time and to respond after each query.

Essentially, however fast your connection is, the slowest part of this operation is the db update, not the php code. So running more code to build a complex query will still be faster than running multiple queries. How much faster really does depend on a battery of factors: but the more rows, the greater the saving.

Roger_Ramjet

Sxooter wrote:
Roger, have you tested it to see if it's really any faster? Just wondering.

I wish PostgreSQL would implement this syntax. I believe it's in the SQL spec, just no one's gotten around to it, and using prepared queries et. al. gives the same performance gain, so there's no carrot there, so to speak.

I was just wondering if it's noticeably faster in MySQL to use that way.

and to answer your question:

No, I have not tested or benchmarked it with mysql.

'Noticeably' is such a relative term. I'm more used to Access on a LAN where the connection overhead, and the way that JET works, makes the speed difference visible to the naked eye. Finding ways to do it in the fewest queries is a habit

My experience with mysql and web apps is limited by comparison so I rely on my existing coding style. I am sure that Apache/MySQL on the same server would not be anywhere near as noticeable, but any saving is good imho.