php and mysql primary key question

Question

Hi All, I have one simple table. the primary key column name is id. I have almost 200 records and some how i deleted some of the records. Now the id column has following entries

1
2
4
5
6
8
9

Can any body tell me what query can I write that return me all the missing rows? For example 3,7 rows in above example. Or any php funtion that do that?

Answer 1

A quick google search could help. Check this out:

http://www.xaprb.com/blog/2005/12/06/find-missing-numbers-in-a-sequence-with-sql/

Answer 2

<?php

// CONNECT TO YOUR DB

$missingRecords = array(); //<-- array you'll store your missing records
$resultsArray = array(); //<-- log the good results
$count = 0;
$lastResult = 0;    

$query = "SELECT * FROM `your_table`";
$results = mysql_query($query);

// populate the results array with your existing id #'s
while($data = mysql_fetch_assoc($results)){
   array_push($resultsArray,$data['id']);
   $lastResult = $data['id'];
}


// cross check the id number with the iterator.  if no matches come up push the missing array
for($i=0;$i<$lastResult;$i++){
    if(cross_check($resultsArray[$i])){
        $count++;
    }else{
        array_push($missingRecords, $count); 
        $count++;
    }
}

// function to cross check iterator and result array id number
function cross_check($id){
    global $count;
    global $resultsArray;
    for($i=0;$i<count($resultsArray);$i++){
        if($resultsArray[$i] == $count){
            return true;
        }
    }
    return false;
}

// print out the missing records
for($i=1;$i<count($missingRecords);$i++){
    echo 'Missing Record: ' . $missingRecords[$i] . '<br>'; 
}

?>