PHP/MySQL Moving Row Data Help!

I'm trying to move row data around

[seomike2003]

I have a table with about 600 rows of data in it. I'm trying to figure out how to move row 600 up to row 300. But I can't seem to figure out how.

Help would be much appreciated!

[coopster]

Why do you need to?

[seomike2003]

Each entry is being tagged to a user id. These entries are being pulled into a page from older info first and new info last via a while( ) loop.

Anyways one of these users has 6 different items that were lined up like this
1
2
3
4
5
6

She deleted item 1 on accident and then recreated it so now the results show up like this
2
3
4
5
6
1

so i'm trying to move 1 back to where it should be. Is there a way to do it?

[coopster]

I know you are probably looking for just a simple answer (not that I have it yet), but what are you going to do when this happens again? Why not use an ORDER BY clause when reading the data so that it will always be in the correct order, no matter what the user throws at you?

[seomike2003]

There's nothing I could use to ORDER BY I've already thought about that.

2 row 356
3 row 360
4 row 362
5 ''
6 ''
1 row 600 <-- the info here used to be on row 355 so it showed up first.

In the 3 years that this site has been up this is the first accurence of a person being this picky about the way their info is shown.

[coopster]

But you said it is tied to a user_id, so your table would actually look like this, correct?

userid722 2 row 356 
userid722 3 row 360 
userid722 4 row 362 
userid722 5 '' 
userid722 6 '' 
userid722 1 row 600 <-- the info here used to be on row 355 so it showed up first.

And if the columns were userid, sequence, row_number, why not:

SELECT ... ORDER BY userid, sequence

?

[coopster]

Or is it that you don't have a sequence number? Your data is simply

userid text
userid text
userid text

If so, now I see your predicament.

[coopster]

If this is indeed the case, first, I would consider altering the table to get a sequence number in there. It's just a thought, but there is obviously a need. Even if you don't implement a resequencing solution for your users yet, at least it will be available for you to make changes quickly in the future if (dare I say when) this happens again).

That said, about the best way I can figure to get things back in order for now would be...

1. Create a new table, exactly like the old table (except add a sequence number column to it; and you could set the sequence number to auto_increment as this would at least give order to your users when we populate this table)
   
2. Use MySQL's INSERT ... SELECT [mysql.com] statement to populate this new table with the data from your current table, using NULL for the auto_increment sequence number column.
   
3. DROP the old table (or DELETE FROM table to get rid of all the current data, and ALTER table to insert the new sequence number column, without the auto_increment keyword...I'd just DROP it instead, see next step for details).
   
4. CREATE the "live production" table if you decided to use DROP TABLE rather than ALTER TABLE (it's probably quicker just to DROP it, especially if you are doing this from the command line as now you can just recall the previous CREATE TABLE statement and change the table name and remove the auto_increment keyword from the sequence number).
   
5. Execute another INSERT ... SELECT to bring the data back from the "work" table we first created.
   
6. UPDATE row 600 in this new "live production" table for the userid that is out of order by assigning the value 1 to the sequence number.
   
7. DROP the "work" table (after confirming that all is well in your new file, of course).

Don't forget to CREATE INDEX on this new file (userid, seq_nbr).

This will probably only take a few minutes to do. You may want to LOCK TABLES [mysql.com] before starting though.

I can't think of any other alternatives right now.

[brucec]

Good suggestions, Coopster, but let me offer this suggestion as well.

Ok, I had a similar situation where my boss is constantly asking me to change the order of categories on our web site. He asks me to change the order of this category field all the time and it has nothing to do with alphabetical or numerical order. So, you can see how this would be a pain.

What I did to solve this was add a display_order field to the database and do an ORDER BY clause on the display_order field. And I don't differ the records in this field by one. I differ them by 50 so I can switch records in and out at his every whim.

You can increment this field out any number you want, but I suggest going no lower then 10.

So, in case this happens again, you can take that 1 and give it a value in the display_order field of 10, the next one can be 11,12, 15, 18 , etc. This way, you have slots in between those records to switch records in quickly and without hassle.