mysql numbering problem

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
yoji
Forum Commoner
Posts: 25
Joined: Sun Oct 19, 2008 3:09 am

mysql numbering problem

Post by yoji »

hi. I have created this column 'ID' which is primary auto, and auto increasing. The problem is that if I delete any record from database, then the new record will have the 'ID' after the deleted one. Let's say I deleted the first record, now when I add the second record (no records exist) the 'ID' of this second record is "2" not "1" . How can I give it according to the actual row number? If it involves PHP no problem...
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: mysql numbering problem

Post by requinix »

What good reason could you have for doing that? And does it really matter if the numbers are sequential?

There's a very easy way to do it but it's probably the wrong solution to the wrong problem.
yoji
Forum Commoner
Posts: 25
Joined: Sun Oct 19, 2008 3:09 am

Re: mysql numbering problem

Post by yoji »

I have tried using this approach. This time the column is only int.

Code: Select all

 
$idresult = mysql_query("SELECT * FROM links");
    while($idrow = mysql_fetch_array($idresult))
        {
        $id=$idrow['ID'];
 
        }
        if($id==NULL)
        {
            $dbid=1;
        }
            else
        {
            $dbid=$id+1;
        }
 
here $dbid is the variable having the ID. This approach is no better either. If I delete a record from between the rest of the records still have the old ID... Any way I can update all record's ID to maintain continuity in records?
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: mysql numbering problem

Post by jaoudestudios »

Thats correct. Sql will never use the same ID more than once. What is wrong with using the next ID... afraid you are going to run out :P . You can optimize ( I think that is correct, I dont usually do this) the table which will start the increments from the next available one, i.e. If you delete ID 1 and then optimise when you do your next INSERT it will start from ID 1 again. However I would not advise it. Just use a BIGINT for ID and you will be fine.
yoji
Forum Commoner
Posts: 25
Joined: Sun Oct 19, 2008 3:09 am

Re: mysql numbering problem

Post by yoji »

Actually the number of records AND the data I will be inserting is very small... ANy ways I have figured out a better way: I am going to use ID by counting total records rather than extracting it from a column.. Thanks anyway.
Post Reply