MySql - Manage a display order of data

Question

OK, a database of information. A column dedicated to a display order. If I make changes, like move rows or if I delete rows or add rows to the end of the database then move them up or down.

I would prefer to keep the display order in sequential order with no gaps. like 1-2-3-4-5-6-7-8-9 if I delete a row 1-2-3-4-5-7-8-9 I may have problem later if I want to add a row after row 5 (in position 6). I would think to keep these numbers sequential with no gaps. Is that right? I have tried:

ALTER TABLE data DROP id;

ALTER TABLE data ADD id int(10) AUTO_INCREMENT;

I know that's not a good thing. Besides, when testing, it didn't keep my data in order.

Any ideas on managing a display order? Am I going to have to load all data into an array and manage it there? Seems like MySql would have a solution already???

Answer 1

I'm afraid AUTO_INCREMENT fields doesnt reorder them, you'd be best off adding a sort field.

Answer 2

SQL has the "ORDER BY" syntax for this very purpose.

No reational database guarentees anything about the order the data is stored in or the order the data is retrieved in unless an " ORDER BY " is specified in the select statement.

If you simply want to preserve the order the data was entered in then a simple timestamp would acheive this.

Answer 3

For this reason there is the "ORDER BY" command in queries:

SELECT * FROM 'TABLE' WHERE 'TABLE.FIELD' = 'XY' ORDER BY 'TABLE.FIELD';

Answer 4

The solution I worked out was to have a column for displaying the data in order. I use the AUTO_INCREMENT column to keep track of the specific_id's for each entry.

The columns are: auto_id | display_order | specific_id | category | subject | text

When entering new data. I INSERT a new row to the end of the table. Then using mysql_insert_id() I retrieve the AUTO_INCREMENT number, I use SELECT MAX(display_order) to get the high number of the display_order, then UPDATE the new row with my data and put the AUTO_INCREMENT number in to the specific_id column because I actually need/use that number.

Using the display_order column as reference, I can move the information (specific_id | category | subject | text) to wherever. Instead of just changing the display_order number, I choose to move the information and leave the display_order column in sequence (mostly) with the AUTO_INCREMENT column. Just personal preference.

When deleting a row. Again, using the display_order I move the information down (numerically) and then DELETE the last row of the table. Again, personal preference, keeps things neat.

The benefits are: 1) Everything is in one table. 2) I use the AUTO_INCREMENT number to give each data entry a specific_id, which was a must have. 3) No gaps in the display_order column, makes finding/referencing, calculating positions and shuffling the information error free.

Other notes: When referencing chunks of data I use "ORDER BY display_id" and "ORDER BY display_id DESC" to keep things in order.