Sort Up or Down

Sorting with arrow

[frozenpeas]

Anyone know a way of having a up and down arrow to sort pages within a cms system?
The pages within the database have a sort column of numerical value.
Example:
I want to be able to click the up/down arrow and it would move the item up/down.
Therefore it would have to know the sort value of the page above it and below it before hand.
Is this done with an array?
Thanks

[eelixduppy]

Welcome to WebmasterWorld!

I would have an index value for each item in the database, and then when you click on one of the arrows(lets say down) then it will get the index of that piece of data you hit the arrow on, and then the one below it, and it will exchange the values of both. Then, of course, when you print all of the data to the screen, you have it selected from the database "order by index" (which defaultly is set to ascending).

[frozenpeas]

Thanks for the quick reply.
Is there a special query that mysql uses to find the next highest value in a column.
So say if sort_order of page1 was 10
How would the query get the next highest sort_order value from the table.
Cheers

[ahmedtheking]

in SQL, use 'ORDER BY' at the end of your query and then the col name, eg 'id' and then ASC or DESC. If you want to do the next ten, then you add a limit: 'LIMIT 10,20' at the end, where the 1st number is the start number (so 1st ten rows) and the 2nd is the end (up to the 1st twenty rows).

[frozenpeas]

Not sure if I have made myself clear.
The system already pulls the page listing as shown below and sorts it by Sort Order

Example page list

Page ID - Page Name - Sort Order
1------------Home---------10----------arrow up/arrow down
2------------About---------20----------arrow up/arrow down
3------------Products------30----------arrow up/arrow down
4------------Services------48----------arrow up/arrow down
5------------Contact-------55----------arrow up/arrow down

Clicking on Services (arrow up) it checks the sort order of the page above (Products) and swaps it with itself. This is the part that stumps me with MYSQL.
ORDER BY does not seem to pull the results correctly even with a SORT BY associated with the Sort Order column.

Example clicking on Services up arrow:
SELECT page_id, page_name, sort_order FROM menu WHERE sort_order < 48 ORDER BY sort_order ASC LIMIT 1
Problem with this is that it can sometimes pull anything that has a Sort Order of less than 48

Help please...

[frozenpeas]

Forgot to mention that when a new page is added the user may specify a sort_order for that new page.

[frozenpeas]

I hate doing this but BUMP

[jatar_k]

as you said you would have to have the sort order of the one above and below it to be able to change the order of the one they click on.

I am sure there are many ways to this. It would depend if you want to swap values as you move them up/down. If you move it down, grab the next sort order and swap that with the row you clicked on and vice versa for moving a row up.

That is probably the easiest way. You could also build a complex formula for sort orders but that might be more trouble than it's worth.

>> it can sometimes pull anything that has a Sort Order of less than 48

that's strange it should only pull the single result with the LIMIT 1

[frozenpeas]

that's strange it should only pull the single result with the LIMIT

It does only pull a single result, its just that sometimes it may pull a single result that is anything below the 48 e.g. 10 or 20 or 30 and not specifically the one above it in this case 30

Page ID - Page Name - Sort Order
1------------Home---------10----------arrow up/arrow down
2------------About---------20----------arrow up/arrow down
3------------Products------30----------arrow up/arrow down
4------------Services------48----------arrow up/arrow down
5------------Contact-------55----------arrow up/arrow down

What I need is a query that will get the next lowest value of the sort_order column.
e.g. if I am on 48 and want to move it up I need a query to know that the one above it is 30. Limit does not seem to work in this case as it seems to sometimes pick 10, 20 or 30.

[jatar_k]

hmmm, you could just pick them all with order by and then grab the first one, though this becomes heavier as there are more rows.

you could try using a limit 5 and see if the first row is always right, even when there are more than 5 rows

is the column set to an int?

[frozenpeas]

Yep column is int.
Thanks