CODEWITHSUNDEEP
phpmysqlsqlsql-insert
ChaseMoskal
ChaseMoskal

Reputation: 7651

MySQL Row Ordering: How to set up and use a `position` column for row ordering?

Hello everybody! I reckon this shouldn't be a very hard one -- but I'm inexperienced with MySQL, and have been having a tricky time figuring the proper way to do this. I don't want to end up doing something silly.

So, I have a list of grocery items whose order I'd like to shift around.
  I'd like it to look like this:

id    label    position
--    -----    --------
81    Bread    1
82    Soup     2
83    Ham      3
84    Eggs     4

How do I initialize the position column in this way?

And how do I handle the position field when inserting new rows?
I'm still a phpMyAdmin man myself, so I need this to be laid down pretty simply.

Initially, I figured I could just auto_increment the position column just like id is -- in hopes it would serve as my initial ordering positions -- but I'm apparently not allowed because only id is.

I think once I have the position column initialized and working for newly inserted rows, shifting them around shouldn't be too hard? I'm imagining I'll make a "swap" operation, that swaps the position field for any two rows by id.
  I imagine a "shift-up" or "shift-down" swap operation would grab the row above or below's position, then do two consecutive UPDATE..'s to swap the position's. I'm not cool enough to figure out how to do this simply in one query (if that's possible?), so from PHP I'll be doing multiple queries to accomplish this with brute force.


I'm displaying the grocery list like this:
SELECT * FROM groceries WHERE 1 ORDER BY position ASC;


I'm hoping to insert new rows something like this:

INSERT INTO groceries (id,    label,              position) 
VALUES                (NULL,  "Instant Noodles",  NULL);

If I can't automate the position field like id's auto-increment, then instead of 'NULL', what should/could I set the position field to? How do I insert new rows with a position that is the same as the last-added/largest position+1?
  Should I be running another query to acquire the largest position (or zero) and add 1?

Upvotes: 1

Views: 564

Answers (2)

Dan Bracuk
Dan Bracuk

Reputation: 20804

JW showed you how to give new rows the highest value. Another method is to set a default value of 0. This gives the field a value with less work.

Upvotes: 0

John Woo
John Woo

Reputation: 263703

Since you can only set one auto_increment column in a table, you can use INSERT INTO...SELECT statement to increment the maximum value of the position.

INSERT INTO groceries (label, position) 
SELECT "Instant Noodles", COALESCE(MAX(position),0) +1
FROM groceries;

Upvotes: 4

Related Questions