[SOLVED] Auto Increment

[SJames]

So, you know how when you have an auto-incremented mysql list? And you know how when you delete an entry, the entry's auto-increment value just gets dropped and skipped?

 

What I need to know is if there is a way to sort of re-number the list, preferably using a Query, such as an ALTER command or something.

 

I have a page that lists the entries like so:

 

1 Title A

2 Title B

3 Title C

4 Title D

 

When an entry is deleted, it looks like this:

 

1 Title A

3 Title C

4 Title D

 

I need to be able to change the previous into this:

 

1 Title A

2 Title C

3 Title D

 

 

I also need to know if there is a way to re-number the list, but  alphabetically by title. (This would be a seperate command.)

 

For Example:

 

1 Title B

2 Title C

3 Title A

 

Would become:

 

1 Title A

2 Title B

3 Title C

[Jessica]

If you change the numbers, then any other table which references those rows will be meaningless. There is a good reason to keep the numbers the same. Why do you want to change them?

 

You can sort them in PHP to make new arrays, but don't try to edit the tables in MySQL.

[teng84]

we dont know your reason but you can!!! using some php stuff and update clause but like jesirose was saying its not proper

[Jessica]

You can't update an auto-increment field, AFAIK. It will break the table.

[SJames]

Yeah, I never really thought about any of that. I guess you're right. Any pages that reference the database would be messed up... Thanks.

[AndyB]

The whole point of a relational database is relationships. And relationships are established from record ids in different tables.  The best way to destroy the usefulness of a database is to mess around with record numbers.  Imagine that auto-incrmented ids are owned by MySQL not you, and you'll be a lot less likely to suffer a database disaster.  If what you really need is a counter, make one in the loop you use to retrieve and display data.