How do you tell mysql to number the id properly?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
aditya2071990
Forum Contributor
Posts: 106
Joined: Thu May 22, 2008 11:30 am
Location: Hyderabad, India
Contact:

How do you tell mysql to number the id properly?

Post by aditya2071990 »

I set a primary key column in my mysql table, and called it "id"; the basic idea was to use it as a serial number. But when I delete any one of the rows, say the sixth row, then the ids don't get rearranged. It still shows 5, then 7. Usually this ain't a problem, but because I am using them as a serial number for my tables when I display them, it looks awkward. Is there a simple mysql query that is able to quickly rearrange all the ids properly? And I forgot to mention, I am using auto_increment for that column too.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: How do you tell mysql to number the id properly?

Post by requinix »

So, you're okay with reusing old serial numbers?

What if the next Windows was called Windows 95 simply because the name "Windows 95" wasn't in use anymore?
How about making next New Year's Day be January 1st, 1009? Why not, we're not using that year anymore!

In case you didn't catch my drift, I think your idea is silly. But in case you really have to do it, issue two ALTER TABLE queries: one to delete the column, another to recreate it.
User avatar
aditya2071990
Forum Contributor
Posts: 106
Joined: Thu May 22, 2008 11:30 am
Location: Hyderabad, India
Contact:

Re: How do you tell mysql to number the id properly?

Post by aditya2071990 »

In my application, which happens to be a simplistic cms for a real estate site who want to list their available properties, I am not using the id column for anything really important...so I thought it was okay...

Still, thanks for the advice, and also for the hidden knowledge on how to do it :D. I will now make a new column, which is an auto_increment too, but I will not make it the primary key. I guess that settles the matter.

P.S., In case its not clear by now, the serial numbers I want are simply for 'decorative' purposes (a numbered table looks dubiously decent), and they don't bear any significance, either in the application or the viewers mind...
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: How do you tell mysql to number the id properly?

Post by onion2k »

Removing the column and then recreating it will only fix it once. The next time an id is deleted they'll be wrong again. A better solution is just to echo $i++ instead of the id.
User avatar
aditya2071990
Forum Contributor
Posts: 106
Joined: Thu May 22, 2008 11:30 am
Location: Hyderabad, India
Contact:

Re: How do you tell mysql to number the id properly?

Post by aditya2071990 »

Hmm well, I can make the script do the deleting and recreating the column every time an id is deleted....

But instead of nasty mysql queries for every silly thing, and i++ would be nice indeed... I will try and implement that.... Thanks!
Hannes2k
Forum Contributor
Posts: 102
Joined: Fri Oct 24, 2008 12:22 pm

Re: How do you tell mysql to number the id properly?

Post by Hannes2k »

Hi,
you never should change the key for a data record. If you do so, you had not understand the aim of using keys. A key should identify the record, and not just until the next delete operation is done.
Imagine your social security number is changed every time a person die...

Using $i++ is here the best solution.
User avatar
aditya2071990
Forum Contributor
Posts: 106
Joined: Thu May 22, 2008 11:30 am
Location: Hyderabad, India
Contact:

Re: How do you tell mysql to number the id properly?

Post by aditya2071990 »

Thanks for the reply :)
Post Reply