How to determine order for new item?

Question

Hi,

I have a members table in MySQL

CREATE TABLE  `members` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(65) collate utf8_unicode_ci NOT NULL,
  `order` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

And I would like to let users order the members how they like. I'm storing the order in order column.

I'm wondering how to insert new user to be added to the bottom of the list. This is what I have today:

$db->query('insert into members VALUES (0, "new member", 0)');
$lastId = $db->lastInsertId();
$maxOrder = $db->fetchAll('select MAX(`order`) max_order FROM members');
$db->query('update members 
            SET 
                `order` = ?
            WHERE 
                 id = ?', 
           array(
               $maxOrder[0]['max_order'] + 1, 
               $lastId
        ));

But that's not really precise while when there are several users adding new members at the same time, it might happen the MAX(order) will return the same values.

How do you handle such cases?

Answer 1

Since you already automatically increment the id for each new member, you can order by id.

Answer 2

I am not sure I understand. If each user wants a different order how will you store individual user preferences in one single field in the "members" table?

Usually you just let users to order based on the natural order of the fields. What is the purpose of the order field?

Answer 3

It's only an example, not really usable, but wanted to make it as simple as possible. Imagine users can define the order members appear on web page. What if 2 users add new member at the same time and the order field is set the same? We have inconsistency in our database...

Answer 4

Usually I make all my select statements order by "order, name"; Then I always insert the same value for Order (either 0 or 9999999 depending on if I want them first or last). Then the user can reorder however they like.

Answer 5

InnoDB supports transactions. Before the insert do a 'begin' statement and when your finished do a commit. See this article for an explanation of transactions in mySql.

Answer 6

You can do the SELECT as part of the INSERT, such as:

INSERT INTO members SELECT 0, "new member", max(`order`)+1 FROM members;

Keep in mind that you are going to want to have an index on the order column to make the SELECT part optimized.

In addition, you might want to reconsider the tinyint for order, unless you only expect to only have 255 orders ever.

Also order is a reserved word and you will always need to write it as \order\, so you might consider renaming that column as well.

Answer 7

Thank you Harrison,

you know what's really interesting? You can't do the same the other way around:

INSERT INTO members VALUES (0, 'new member', (select MAX(`order`)+1 FROM members));

Because it's throwing 1093: You can't specify target table 'members' for update in FROM clause.

Though thank you for answering

Answer 8

What you could do is create a table with keys (member_id,position) that maps to another member_id. Then you can store the ordering in that table separate from the member list itself. (Each member retains their own list ordering, which is what I assume you want...?)

Supposing that you have a member table like this:

+-----------+--------------+
| member_id | name         |
+-----------+--------------+
|         1 | John Smith   |
|         2 | John Doe     |
|         3 | John Johnson |
|         4 | Sue Someone  |
+-----------+--------------+

Then, you could have an ordering table like this:

+---------------+----------+-----------------+
| member_id_key | position | member_id_value |
+---------------+----------+-----------------+
|             1 |        1 |               4 |
|             1 |        2 |               1 |
|             1 |        3 |               3 |
|             1 |        4 |               2 |
|             2 |        2 |               1 |
|             2 |        3 |               2 |
+---------------+----------+-----------------+

You can select the member list given the stored order by using an inner join. For example:

SELECT name
FROM members inner join orderings 
  ON members.member_id = orderings.member_id_value
WHERE orderings.member_id_key = <ID for member you want to lookup>
ORDER BY position;

As an example, the result of running this query for John Smith's list (ie, WHERE member_id_key = 1) would be:

+--------------+
| name         |
+--------------+
| Sue Someone  |
| John Smith   |
| John Johnson |
| John Doe     |
+--------------+

You can calculate position for adding to the bottom of the list by adding one to the max position value for a given id.