sorting of unique column

knucklehead

hello...
i was searching for this solution around the web but i couldn't find anything usable really... anyway, i'm sure that i saw somewhere the solution (long time ago) but can't find it anymore...

so...
i have a simple table

[CODE]ID | NAME

1 | One
2 | Two
3 | Five
4 | Ten

etc.[/CODE]

ok, now i want to give to user possibility to re-arrange column NAME with that UP and DOWN arrows, you know.....
and at the end i would re-arrange column ID so i could always use query like:

SELECT * FROM table ORDER BY ID

so, i would like to table looks for example like this at the end:

[CODE]ID | NAME

1 | Ten
2 | One
3 | Five
4 | Two

etc.[/CODE]

thanks for help...

laserlight

To sort by name:

SELECT * FROM table ORDER BY NAME

To sort by name in descending order:

SELECT * FROM table ORDER BY NAME DESC

So you need to have two incoming variables: one to determine what column to sort by, and other to determine ascending/descending order.

leatherback

You want to have an ID colum, a value and a sortorder colum.
If you move an item down, you need to swap values in the sortcolum with the item below, if you move up, you swap sortorder with the item above.

knucklehead

laserlight thanks but that part i know 🙂 obviously i didn't explain my problem good enough... i was thinking more like leatherback's idea... anyway, is it necessary to add third column (sortorder) or is it possible to somehow make a 'temporary array' and then update the table with whole new values? if i remember correctly i have seen the example with javascript and everything was done 'on the fly', without refreshing page etc... and the ID column was perfectly arranging IDs but i cannot find that tutorial anymore 🙁
thanks for help!

leatherback

You could, but if you use this information anywhere else on your system (e.g., as a lookup for something) you do not really wanna mess with the ID of the item.

As for doing the JS.. Yes. You could do it with js /AJAX: Update the list using a JS swap item function, and use AJAX to tell the server every time what has happened. Quite straightforward if your js/AJAX skills are up to scratch (Mine are not even close 😃)

compbry15

Honestly the safest and probably best way to do this for the long or short term is leatherback's original suggestion.

Changing ID's like that is bad, it sort of goes against a lot of the rules that make DB's so efficient.

The best situation, as leatherback suggested, is to create a new SortOrder column. You can then even use js or ajax to move this around and save. The user would be none-the-wiser, and your system will be more stable than what you were originally asking.

For mysql it is as simple as

alter table simple_table add column SortOrder tinyint(1) not null default 0;

or some other value other than tinyint, if you are sorting many different records.

I strongly suggest not moving around ID=>value pairs.