Deleting a row, but keeping them in order MySQL

programming
1

I have a table set up with an id column, a date column, and a text column. If I delete a row from the table and go to add another row, it doesn’t put it in numerical order based on id. Is there a way to say have 15 rows, delete row 7, and then, when I go to add a new row (16) it would put it right after 15 and not inbetween rows 6 and 8? Maybe I’m doing something wrong here…

help anyone?

thanks~~

2

ID numbers (primary key) aren’t meant to be used like that. When you delete a row it’s ID becomes banished from the database. For instance if you have

ID:

1, 2, 3, 4, 5

and then you delete records 3-5 and add a new row it will become

1, 2, 6

That’s just the way it works, and you should never EVER sort anything by ID. Add a date column instead.

3

The rows (tuples) in a database are a set. Meaning they are unordered and unique. Just do a query in ascending order when you need them in order.

If you are looking at the ordering in something like PHPmyAdmin don’t worry about the order. That’s just the set of tuples in the relation. It means nothing because as I said they are a set. If you are that paranoid about seeing them in order create a view and set the query for it to put them in ascending or something :stuck_out_tongue:

4

I’m only asking because when I list all the rows on a page on my site, I wanted them to be listed in the order that I entered them into the database. So, like, If I had 13 rows, I want them to be listed in the order they were put in (by id, since it’s always one higher when I add a new one). When I delete row 7, and add a new one, it’s no longer put at the end of the list, it gets shoved in.

I understand what you’re saying about why it fills it in, but I don’t know how else to do it. Maybe a hidden field with the current date, so it has something to sort it by? But if I add that extra column, how do I get it to always sort it by date? Is there a way to sort it numerically by id, or should I just ignore that all together?

5

Just put “Order By id DESC” at the end of your query. Or use ASC if it’s the wrong order.

6

Thank you very much. Worked perfectly.