Page 1 of 1

mysql numbering problem

Posted: Sat Nov 15, 2008 2:14 am
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...

Re: mysql numbering problem

Posted: Sat Nov 15, 2008 3:24 am
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.

Re: mysql numbering problem

Posted: Sat Nov 15, 2008 3:26 am
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?

Re: mysql numbering problem

Posted: Sat Nov 15, 2008 3:27 am
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.

Re: mysql numbering problem

Posted: Sat Nov 15, 2008 3:47 am
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.