Moving rows in MySQL tables

markjia

I need to be able to move a row in a MySQL table to a different row in the same table. How can I do this?

Anon

Why on earth would you want to do that? That's not the idea for a database...A database is just a bunch of data, stored in whatever order. It's when you retrieve the data that you can specify a sort order, but why would you want to move the placement of a row within a table? It doesn't make sense...

Diego

bretticus

Do you mean move a record from one table to another by chance? I also have no clue why you would want to duplicate a record in the same table ;-)

markjia

The database is for storing the file name and attributes of images for a photo gallery. So the position of the rows corresponds to the positions of the images. If I want to rearrange the positioning of the images in the gallery, I would want the table to be displayed in sequential order (so that it is easier to get a mental picture of what it would look like). I have already made part for database administration tools (such as those for inserting new rows, editting entries, etc.), so making all the other pages display the database in a different order would be much more work.

markjia

I don't mean duplicate, I mean move either before or after another record.

Anon

Why not create a new column called "Position" in the database. So when you display the image, you just sort by position:

SELECT urls FROM images ORDER BY position

That's how databases work: store in whatever order, retrieve with specified order.

Diego

markjia

Hum...that might be a good idea, except I have already made all the other pages, so i would have to change all of them first...perhaps next time. In the meantime, I think what i can do instead of moving the rows, I can create a new table, position the rows correctly, delete the old table, and rename the new one. Since this is only done by the admin, and with pretty small tables, efficency is not a concern.