PHP –– auto increment while filling in gaps
written by: admin
Date Written: 12/21/07
Last Updated: 3/23/11
This code assumes that the ID column for your table is
not set to auto increment when a row is inserted into your table. You should set ID as key in your table. If you delete a row and submit a new row your mysql will normally continue on from the last file entered when using the auto increment setting.
For example if you entered five rows into a table it will be listed as
1,2,3,4,5. However, if you deleted a row, let's say row
3 and submitted a new row into the database the auto–increment command will give the submitted row an ID# of
6 so that the table now contains the following rows:
1,2,4,5,6.
Now if you have a habit of deleting files occasionally you will have an increasing number of gaps in your table. The following code will scan your table for the first gap in your ID sequence and assign your ID that ID#. Then the next time you submit a row into your table the gaps will be filled first and then auto–increment if there are no gaps.
$ID = 0;
$get_list1 ="SELECT ID FROM tablename ORDER BY ID ASC";
$get_list_res1 = mysql_query($get_list1) or die(mysql_error());
$num_rows = mysql_num_rows($get_list_res1);
while ($recs1 = mysql_fetch_array($get_list_res1)) {
$IDa = $recs1['ID'];
if ($num_rows==0) {break;}
if ($num_rows>0 and $IDa>0 and $ID=="0") {break;}
if (($IDa-$ID)>0) {break;}
$ID++;
}
There are three reasons for the wile loop to be broken.
First, if there are no rows in the table then the row# will be 0.
Second, if the gap in the table is row 0 then break with a row# of 0.
Third, if there is a gap anywhere else then the row # will be in the lowest numbered unused row.
lastly, after all of the rows have been looked at and there is no gap then the row number to be created next will be after the highest numbered row created.
The reason I created this code is because if I want to edit the url and search my database I don't want there to be a bunch of urls that don't work before I get one that does.
TAGS: php