Here is something that might be helpful to someone and it's dirt simple to implement. Often you want to do a report of "missing numbers" in a sequence, like missing checks. I'm not going to explain too much on this because the coding is very obvious once you see the trick involved, which is a LEFT JOIN of the table to itself on either the next greatest check number (provides a maximum if the join is null) or the next lesser check number (provides minimum if join is null). the SQL table creation and data is as follows:
CREATE TABLE `_breaks` (
`ID` int(9) unsigned NOT NULL auto_increment,
`checknumber` int(9) unsigned NOT NULL default '0',
PRIMARY KEY (`ID`)
) TYPE=MyISAM AUTO_INCREMENT=12 ;
#
# Dumping data for table `_breaks`
#
INSERT INTO `_breaks` VALUES (1, 1000);
INSERT INTO `_breaks` VALUES (2, 1001);
INSERT INTO `_breaks` VALUES (3, 1007);
INSERT INTO `_breaks` VALUES (4, 1008);
INSERT INTO `_breaks` VALUES (5, 1009);
INSERT INTO `_breaks` VALUES (6, 1010);
INSERT INTO `_breaks` VALUES (7, 1011);
INSERT INTO `_breaks` VALUES (8, 1012);
INSERT INTO `_breaks` VALUES (9, 1014);
INSERT INTO `_breaks` VALUES (10, 1020);
INSERT INTO `_breaks` VALUES (11, 1021);
Be sure and index your checknumber field!!!!
Next we query the table twice as follows:
/** Range Minimums **/
SELECT a.checknumber
FROM _breaks a
LEFT JOIN _breaks b ON
a.checknumber = b.checknumber +1
WHERE b.checknumber IS NULL
/** Range Maximums **/
SELECT a.checknumber
FROM _breaks a
LEFT JOIN _breaks b ON
a.checknumber = b.checknumber -1
WHERE b.checknumber IS NULL
I'll skip the php-mysql scripting to contact the table, you can figure that out, but you'll need to get the minimums and maximums as two arrays as follows:
$minimums= array(1000,1007,1014,1020);
$maximums= array(1001,1012,1014,1021);
Now here's the simple code to put the contiguous sequences into an array, and an example of its use:
foreach($minimums as $v){
$i++;
$ranges[$i]['min']=$v;
do{
//nothing needed here
}while( (list(,$w)=each($maximums))<$v);
$ranges[$i]['max']=$w;
}
echo 'Contiguous Sequences:<br>';
foreach($ranges as $n=>$v){
echo ($v['min']==$v['max']) ? 'Check '.$v['min'] : 'Checks '.$v['min'].'-'.$v['max'];
echo '<br>';
}
The end result:
Contiguous Sequences:
Checks 1000-1001
Checks 1007-1012
Check 1014
Checks 1020-1021
That's it! So if your client ever wants a report on a range of contiguous numbers like a missing check report, this will be helpful.
Samuel