Search code examples
phpmysqlrecord

Work out missing row in MySQL

I have a table that will eventually get a row missing through deletion of records.

How could I check for that missing row in that table then fill this missing row in PHP.

I guess its using looping however I just cant work out the correct loop to use

Cheers

Solution

  • One way to find a missing item in a sequence is to compare count(*) and max(id). It should give you an idea of how many are missing, then start including ranges that your checking as in a binary search.

    Alternate way is just iterate over the rows ordered by id, and trigger your insert when the sequence jumps.

    $lastId = 0;
foreach ($rows as $row)
{
    if ($lastId + 1 != $row['id']) {
        fillInRows($lastId + 1, $row['id'] -1);
    }
    $lastId = $row['id'];
}

function fillInRows($min, $max)
{
   for ($i = $min; $i <= $max; $i++) {
       // exercise left for the reader
   }
}