Why can't I add items to the database?

How can I add items to a list, with last added being shown first which can be sorted?

• So I have a list of items, that are being added to the database. I'll outline the process briefly: Item is added to database for a particular list When page is loaded, items are retrieved for the particular list. I want the last added items to be shown first I have managed to do this through "ORDER BY 'id' DESC" But now I want to make this list sort-able, therefore their positions will change, but I cannot rely on ID for the list position. What would be an ideal design for this problem?

• Answer:

  I found the phrasing of your question a little confusing, but here goes: Initially you wanted to show items, ordered last-added to first-added (ORDER BY id DESC). OK. Now you want to order by another criterion? Then just ORDER BY <expression> (e.g. ORDER BY name). If you can describe in English what the new ordering criterion is, we can answer more specifically. In some cases you may want to introduce a new numeric column to define a specific order (then ORDER BY that column). —— Update: (I'm not sure why this shows Anon User; I'm answering while logged in. Oh well.) I see your clarification. But what does the 'order' column mean? The answer to that will help you see how to update it. (It doesn't seem to mean "sequence added", or you could continue to use the auto-incremented id.) Do you mean that different lists can have items in different (forced) orderings? In this case, you need a third relation (list_id, item_id, rank). (I am using the less ambiguous and not-a-keyword term rank where your column is order). A sample query for a particular list X is then: SELECT item FROM items JOIN list_item_rank R USING (item_id) WHERE R.list_id = X ORDER BY R.rank; ---- Do you mean "by default, I want the last added first, but then the user can change it"? If so, you would let them fiddle around changing the order in the UI, then when they Save their changes, you rewrite the rank column according to their selected ordering (in a transaction of course). Never change the id (primary key)!! So, for your example, if your items are (ORDER BY rank DESC): John: id 4, rank 4 (last added) Paul: id 3, rank 3 Ringo: id 2, rank 2 George: id 1, rank 1 as the first added, if you use my expression below When you ADD an item, its rank can be the highest rank plus one, as you surmised (e.g. SELECT IFNULL(MAX(rank)+1, 1) FROM items). (Be careful of a race condition between SELECT/INSERT if you have multiple connections adding items.) So far, rank is equal to id, by coincidence. Don't be fooled by this, because in a moment the user is going to sort these from most interesting to least interesting Beatle. Therefore, the new state of the database is (I'm also presenting them in rank order, as the user would see): John: id 4, new rank 4 Ringo: id 2, new rank 3 George: id 1, new rank 2 Paul: id 3, new rank 1 (sorry ! but you're boring!) Notice that we simply rewrite sequential rank numbers according to the user's chosen ordering. Once again: ids do not change. Ever. Also note that rankings only get updated when the user wants to manipulate the displayed order. You don't need to touch them, for example, when deleting a list item (the deletion doesn't affect ordering). Also, you don't update any when inserting; because of your rule that last-added is first, you only need to work out the (now highest) rank of the newly inserted item. You should also put a UNIQUE constraint on the rank column.