Page 1 of 1

reorder database row output

Posted: Thu Nov 03, 2011 12:34 am
by jbrave
Hi,

I have a very simple mysql database with a few php/html forms to add, update and delete entries. I've added a field called sort_order (someone told me that it is a very bad idea to reorder the actual data in the database) and manually punched in numbers representing the order in which the rows in the database should be displayed (in an html page, or as xml)

Now I want to have some php to allow the user move an entry up or down by incrementing or decrementing the sort_order field. I assume this means iterating through the whole database and rewriting the sort_order field. I need some help figuring out how to go about that.

Also, I tried to make the sort_order field "unique" or an "index" but PhpMyAdmin gives me an error:

[text]
Keyname Type Cardinality Action Field
PRIMARY PRIMARY 15 Edit Drop id
id UNIQUE 15 Edit Drop id
sort_index UNIQUE 15 Edit Drop sort_index
The following indexes appear to be equal and one of them should be removed: PRIMARY, id[/text]

Re: reorder database row output

Posted: Thu Nov 03, 2011 2:06 am
by Christopher
You can do it with SQL. You need to determine the range of values that you need to move up or down. The do:

UPDATE mytable SET sort_index=sort_index+1 WHERE sort_index>=range_start AND sort_index<=range_end

You just need to figure out the range you want to move and which direction. Once they are adjusted then UPDATE the record to be moved with its new sort_index.

Re: reorder database row output

Posted: Thu Nov 03, 2011 3:57 am
by twinedev
With that method, since he has sort_index locked into being a unique value, there will be conflict:

(Starting with sort_order = id)
[text]mysql> UPDATE tblTemp SET sort_index=sort_index+1 WHERE sort_index>=4 AND sort_index<=8;
1062 - Duplicate entry '5' for key 2[/text]

To do the same, assuming you allow negative numbers...
[text]UPDATE mytable SET sort_index=(-1-sort_index) WHERE sort_index>=range_start AND sort_index<=range_end

id sort_index
1 1
2 2
3 3
4 -5 <
5 -6 <
6 -7 <
7 -8 <
8 -9 <
9 9
10 10

UPDATE mytable SET sort_index=range_start WHERE sort_index=range_end+1

id sort_index
1 1
2 2
3 3
4 -5
5 -6
6 -7
7 -8
8 -9
9 4 <
10 10

UPDATE mytable SET sort_index=sort_index*-1 WHERE sort_index<0

id sort_index
1 1
2 2
3 3
4 5 <
5 6 <
6 7 <
7 8 <
8 9 <
9 4
10 10[/text]

Re: reorder database row output

Posted: Fri Nov 04, 2011 1:01 am
by Christopher
It would be easier to just remove the UNIQUE.

Re: reorder database row output

Posted: Fri Nov 04, 2011 6:12 am
by twinedev
Yup! Especially once you get to nested lists makes it a lot less code!

-Greg