mysql multiple updates

Anon

I have columns with sequential data (1,2,3...) that is used to sort the rows for output.

When records are edited, I wind up with non-sequential numbering, like 1,1,2,4, etc. and need to go in and renumber the columns.

I tried using a loop that does an mysql_query update for each row, renumbering it the way I want. It works, but I wind up with a couple of hundred updates in the loop and it takes like 15 seconds or so per hundred updates.

Is there some way to renumber a lot of rows without having to repeat that mysql_query for each row?

Any help is appreciated. Below is the code I'm using now.

$result = mysql_query("SELECT * FROM album WHERE $sortfield > 0 ORDER BY $sortfield",$db);

$counter = 1;

while ($myrow = mysql_fetch_row($result)) {

mysql_query("UPDATE album SET $sortfield = '$counter' WHERE uniquefield = '$myrow[0]'");

++$counter;

}

Anon

I can't guarantee this will be faster but its worth a try. Instructions are kind of sketchy because it sounds like you know what you are doing.

Create a temporary table (a "heap" table is fastest, check the manual). Only need two columns, the uniquefield from your table and an autoincrement id column.

Insert all the uniquefield values into the heap table and let mysql set the id values with the autoincrement.

Update your table from the heap table by joining on the uniquefield and setting the sortfield to the id in the heap table for the corresponding uniquefield.

greg252

Similar to what I initally thought...
After you do all of your inserts and updates, can't you drop the cols that need to be sequential, add a new col with auto_increment and let mysql fill in the values?

Seems that this would be faster.

Anon

It's a photo album. The unique field is the file name of the image. I have 36 fields for catagories. Each record has either a 0 or a positive integer in each catagory field. Zero in a catagory field denotes that image does not belong to that catagory.

The images are displayed in a specific order, sorted by the contents of the catagory field.

An admin function allows me to change the position of an image on the page, add an image to a page, or remove an image from a page or catagory by changing the number in the catagory field.

As soon as you make any of those kind of changes, you wind up with duplicate or missing numbers within a catagory, causing rows to sort unexpectedly during future updates, so my idea is to go in immedately after each change and renumber.

Doing that though, requires me to update an average of 100 records with the new data for the affected catagory field. I thought that would be no big deal, but issuing that mysql_query("UPDATE... a hundred or two hundred times in a loop, is obviously not the way to do it.

I've been looking at tomhath's suggestions for the last couple of hours, but as far as I can tell that would still use an update loop similar to what I have now, which is where the bottleneck is.

Maybe I need to rethink the logic of the whole thing, start fresh.

John

tomhath

The update would all be done in a single statement something like the following. No looping in php is needed.

update album
set sortcol = temp.id
from temp
where temp.uniquefield = album.uniquefield

Give it a try, my guess is that it will only take a few milliseconds to update the entire table.

Anon

I couldn't get anywhere. Everything I've seen last night and today suggests:

A. I can't use two tables in an update.
B. I can't perform more than one update in a single php mysql_query.

I still just can't believe that I have to issue an individual mysql_query for each record update, where records are not being updated with common data. It seems so slow.

I had hoped at one point to find a mysql function, such as something that was aware of the record position within the record set, and could then update with that data. That would perform the renumbering for me, and could be done with a single mysql_query. I think I could write a UDF to do that.