User Specified Ordering

[bormuff98]

I think this is a database query rather than a coding one. So I have a table which lists product names and other assorted details. On my site I have a page which simply lists each product in a table row. Now I have initially setup the table to auto-increment on the productID field and then on the products page the output is simply ordered by this productID field.

It now turns out that the ordering of the products needs to be specified by the site owner. So imagine there are 20 products listed now and in a months time we add product 21 but this needs to be displayed between say products 5 and 6.

I have one vague kludge of an idea of how to achieve this but wondered if anyone has a simple solution. My kludge would be to add another field (call it 'weighting') which initially had values 10, 20, 30 etc for row 1, 2, 3 respectively. Then when product 21 was added it would be given value of 55 (using the above example) in the weighting field.

[eoghain]

Your answer is the correct one. There is no way to easily allow the placement of records in your database. I'd suggest that you just give each record a position field, and then let the user select which position it should go in when they enter/update the record. You'll have to add code to modify all records below the position that the new/updated record takes.

So if you have a list of 10 items (positions 1-10) and a user enters a new item and wants it to appear in position 5 then you'd have to do something like this before you inserted your record.

UPDATE table
   SET position = position + 1
 WHERE position >= 5

Now you can just insert your record since all of the ones below it have been moved to make room. Now you just need to use the position field for display order.