mysql - move records up/down

[gavinmcnamee]

Hi,

I have basic php/mysql knowledge and am trying to change the order of records.

I want to move the order of records using up/down buttons to change the order.

Can point me to a soultion?

THanks

[Demaestro]

Lets say you are getting the records from a table named "items" and you want to sort on last name

So it looks like loosely like this:

var sort_direction = asc
var sort_on = last_name
sql = "select * from items order by $sort_on $sort_direction"

so you want to add links that will refresh the page but change the value of $sort_on and $sort_direction

<a href="/?sort_on=first_name&sort_direction=asc">UpArrow</a> ¦
<a href="/?sort_on=first_name&sort_direction=desc">DownArrow</a>

Hope this helps.

[gavinmcnamee]

Thanks Demaestro for your quick response.

I think i explained wrongly.

Is it possible to change order eg

1: oranges
2: apples
3: lemon
4: grapes

I want to then, say move record 3:lemons up to replace 4:apples

so new order

1: oranges
3: lemon
2: apples
4: grapes

Basically move the order of any record up or down 1 row?

Can this be done easily?

[seabird505]

Add another column in the table that will control the display order of records

ID Name Displayorder
1: oranges 1
2: apples 2
3: lemon 3
4: grapes 4

If 3:lemon is desired to be moved up the final picture will be

1: oranges 1
3: lemon 2
2: apples 3
4: grapes 4

1. Find display order of lemon (3).
2. Find record that has display order of one up (2) i.e. apple.
3. Update the table set displayorder=2 where id=3
4. Update the table set displayorder=3 where id=2

Query the table with Order by displayorder

[ag_47]

If you want to actually change the order inside the database, then do what seabird505 said.

If you just need to change it on a webpage, for a particular user, why not use JavaScript (or PHP if you really want to). Basically output your results into a javascript array, and create a function that will move the displayed rows up/down. Shouldn't be too hard.