[RESOLVED] detecting a gap in a sequence of numbers

beandip

I have a table which contains "transactions." Each Transaction contains an order #. We currently have a slight issue in which an Order # will be generated but for some reason the transactions never get written into the table. So I need to come up with a query that will allow me to detect gaps in the sequence of order #s.

The field in question is called "oid" The Order #s in "oid" are sequential and should contain NO gaps.

Unfortunately I am not adept enough in PHP or sql yet to know how to code the query in order to determine the missing order #s in the oid field.

Any ideas? Thanks so much!

bpat1434

Well, one way is to select all the items in the table, then loop through and see if item 2 is 1 item aver item 1, etc....

<?php

$conn = mysql_connect('localhost', 'username', 'password');
mysql_select_db('database', $conn);

$query = "SELECT `oid`
FROM `table`
ORDER BY `oid` ASC";
$result = mysql_query($query) or die(mysql_errno().': '.mysql_error());

$prev_oid = '';
$gaps = array();
while($row = mysql_fetch_array($result))
{
    // If $prev_oid is empty, we're in the first iteration, no need to "look" for sequence
    if(empty($prev_oid)) {
        $prev_oid = $row['oid'];
        continue;
    }

if($row['oid'] != $prev_oid+1)
{
    $gaps[] = $prev_oid . ' -> ' . $row['oid'];
}

$prev_oid = $row['oid'];
}

if(!empty($gaps))
{
    echo 'There were some gaps!! They are listed below:<br />' . implode('<br />', $gaps);
}

Hope that helps somehow....

Please note that with large databases, this can really turn into a long task....

[ EDIT ] - Fixed coding issues...

beandip

Your code did exactly what I needed! Thank you SOOO much. There is one minor typo in your code... The line that looks like this:

$gaps[] = $prev_oid . ' -> ' . $row['id'];

should look like this...

$gaps[] = $prev_oid . ' -> ' . $row['oid'];

Thank you again!

bpat1434

Ah, thanks for that. Glad to hear it worked!!