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

. 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.