Store the order of something in MySQL

Question

Let's say I have a table that's like the following:

name VARCHAR(50) PRIMARY KEY NOT NULL
ordernum TINYINT UNSIGNED NOT NULL

Also let's say I have 3 rows.

something     - 1
somethingelse - 2
somethingmore - 3

If i want to insert another entry called something1, and give it an ordernum of 2, how do I reorder all the rows that has an ordernum of 2 or greater? i.e. somethingelse becomes has an ordernum of 3, and somethingmore has an ordernum of 4?

I've heard the you can do it via FIELD(), but I'm unsure how to exactly.

If it's not possible, anywy to do it in PHP?

Answer 1

In your example:

UPDATE `table` SET `ordernum` = `ordernum` + 1 WHERE `ordernum` >= 2
INSERT INTO `table` (`name`, `ordernum`) VALUES ('something1', 2)