CODEWITHSUNDEEP
mysqlsorting
Altimus Prime
Altimus Prime

Reputation: 2327

User determined ordering or sorting with php mysql

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.

Upvotes: 1

Views: 394

Answers (3)

Mike
Mike

Reputation: 24363

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.

Upvotes: 3

bfavaretto
bfavaretto

Reputation: 71908

Unless I completely misunderstood the question:

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

Upvotes: 0

user1660098
user1660098

Reputation: 56

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.

Upvotes: 1

Related Questions