this is alittle complex..

TimTimTimma

is it possible to select X amount of rows from a mysql database, organize them via a particular field from largest to smallest, and then UPDATE that field based on the order the rows were pulled in ONE query?

the reason for wanting to put all of this into one query because the only other way i know how to achieve what i want to achieve is to update the rows in one query and then select the rows in a seperate query sorting them from smallest to largest and run a while loop with an update query that updates the rows one by one, the only problem with this is I plan on doing THOUSANDS of rows in a single update every 24 hours, and im pretty sure a few hundred thousand queries would probably crash the server, or slow it extremly at the very least.

So, any suggests or help here would be greatly appreciated

TimTimTimma

Ok, I have done alittle research, and let me rephrase what it is I am trying to do...

UPDATE table
SET rank=AUTO_INCREMENT_STARTING_WITH_1_HERE
ORDER BY rating

I am wanting a table to be ordered a particular way a then a field given a number based on the order it was recieved starting with 1. So if it was ordered first, it would get rank=1, if it was ordered second, it would get rank=2, and so on and so fourth.

A few people have told me its not possible, but there has got to be a way to do it 🙁

herve

Maybe by using some subquery ?

Cf http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

solodesignz

With PHP i dont think it is possible unless you do a query then update for each row... I would look into stored precedures... might have to do the same way(row by row), but it will be handled all in SQL and not PHP, just make the call to the procedure and the DB server will handle it.... prolly be alot faster

Roger_Ramjet

The only way this is going to work is to create a new table and fill it from the old table with a select query with the required order by, normal autoinc column will handle itself. Then rename tables. If you want to do this every 24 hours then you'll need to write a script that creates the new table, then deletes the old table and renames the new table.

solodesignz

Your still dealing with the same problem though... say you need to update a 10,000 row table.... that will take awhile (ive tried before) =P

Roger_Ramjet

No. You NEVER loop through a resultset and run a query for each row - NEVER. That is what takes the time you are worried about. 10K records is nothing for a query to update - take less than 2 secs on my slow shared server.


$sql = "INSERT INTO new_table SELECT col1, col2, col3, col4 FROM old_table ORDER BY col2 DESC"

Make sure that you select every column except the autoinc ID column. This will be populated as the data is added, and the number sequence will be the same as your order by column.

At most you are talking about 1 Query to create the new table; 1 query, as above, to fill it; 1 query to DROP the old table; then 1 query to rename the new table. Bet you the whole thing takes less than 5 seconds to run all of them.