Dynamically sorting MysQL table rows with links?

[jimmy_jazz]

Hi

Im fairly new to php but have been making much progress recently.
I've just created a simple CMS but there's a certain functionality I want but dont know where to begin.

Ive created a page that lists the records from a table in my database. That works fine. But what I now want to be able to do is have 2 links next to each record that moves the desired record up or down in the listing.

I assume that this is a case of swapping the record id numbers so the order of the database information is changed? Does anyone have any knowledge of this or could point me in the right direction to help me out?

Any help appreciated!

Jimmy

[lobo235]

I usually create another field in the table called "order" or something like that. Then I just swap those numbers instead of messing with the record id. Be sure to not make the "order" column UNIQUE because for a split second, the records will need to have the same "order" number. Just use a SELECT statement to get the record just before or just after the record being moved and read it's order number. Then use 2 UPDATE statements to swap the order numbers of the two.

Hope this helps.

[coopster]

Welcome to WebmasterWorld, jimmy_jazz.

I agree, having a sequence number of sorts is going to be much easier than trying to update your primary key. Some related threads:

Changing The Order of Database Items [webmasterworld.com]
primary key and another field the same.... how to? [webmasterworld.com]