User determined ordering or sorting with php mysql

Question

What is the best way to assign orders to mysql table rows? If I have rows 1 to 5 and I want to remove 4, how can I make 5 the new 4? If I make make 3 the new 1, I need to add 1 to each of the other rows. There could be 1 or 2 rows or a hundred.

Is there a simpler way than manually programming each contingency?

Thanks in advance.

additional: I have an interface where I add packages for customers to see. They are automatically ordered ascending by id. I can reorder them by price, package name, or whatever, but I want to arbitrarily order them by my own preference from time to time.

Thanks again.

I added an order field, independent of id, and am about to program it the long way, but hoped someone had a better way. — Altimus Prime
– Altimus Prime, Commented Oct 11, 2012 at 2:54
sort by ORDER, but don't use the order id in the db as the displayed order counter. or in other words don't mix the logic of data structure with the business requirement of some numbering system. — user557846
– user557846, Commented Oct 11, 2012 at 2:55

Mike · Accepted Answer · 2012-10-11 03:22:40Z

3

Assuming you have a unique order_column column in your database:

To add a new row at position x:

Lock tables
update all rows where position >= x and add 1
Then insert the new row at position x
Unlock tables

To swap positions x and y:

UPDATE table SET x=(@temp:=x), x = y, y = @temp;

(source)

To remove a row at position x:

Lock tables
Remove row at position x
update all rows where position > x and subtract 1
Unlock tables

To display data:
Just ORDER BY by the order_column column.

edited Oct 11, 2012 at 3:22

answered Oct 11, 2012 at 3:00

Mike

24.5k14 gold badges84 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mike Over a year ago

If you're going to down vote at least tell me what's wrong with my answer.

bfavaretto Over a year ago

This is very well a possible answer to this question (which is unclear).

Altimus Prime Over a year ago

Yea, this is the best answer. Thank you for taking the time to point me the right way Mike.

user1660098 · Accepted Answer · 2012-10-11 02:53:32Z

1

Basic approaches to ordering usually amount to ordering on a specific column either alphabetically or numerically. Alternatively if the the order that you need can not be created based on the data you have then you need to add a column exculisevly for ordering purposes, then create an interface to populate that with data. Sounds like you need to do the later.

answered Oct 11, 2012 at 2:53

user1660098

563 bronze badges

Comments

bfavaretto · Accepted Answer · 2012-10-11 02:57:11Z

0

Unless I completely misunderstood the question:

Leave the table alone and just use ORDER BY to determine sorting when SELECTing rows.

answered Oct 11, 2012 at 2:57

bfavaretto

72k18 gold badges118 silver badges162 bronze badges

Collectives™ on Stack Overflow

User determined ordering or sorting with php mysql

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related