[RESOLVED] Finding "almost duplicate" records and missing numbers

swatisonee

Hi,

This seems very simple but i cant seem to get the queries right . would appreciate some guidance thanks !

a. I have a field with nos 1,2,3..... but there are some missing like say 7, 8 . How do I echo out the missing numbers ?

here's the code i have :

<?

$sql= "SELECT `CID` FROM `Customers` ORDER BY `CID` asc";
$res = mysql_query($sql);


 $missing  = array ($res);
$array_count = count($missing);
for($y=1; $y<$array_count; $y++) {
   if(isset($missing[$y])) {
   echo($missing[$y]);
   }
   else { $array_count++; }
}

?>

b. I also have records that are almost duplicates like say ABC Inc and ABC Supplies. How do I list all these out ?

Swati

liquorvicar

a. mysql_query() returns a result resource and not an array so that won't work. One way to do it would be like this:

<?php
$lastCID = 0;
$sql= "SELECT `CID` FROM `Customers` ORDER BY `CID` asc";
$query = mysql_query($sql); 
while ($result=mysql_fetch_assoc($query)) {
    if ($result['CID']>($lastCID+1)) {
        for ($missing=$lastCID+1;$lastCID==$result['CID'];$lastCID++) {
            echo('Missing: '.($missing)."<br />\r\n");
        }
    }
    $lastCID = $result['CID'];
}
?>

b. This is complicated. You could just SELECT all the entries and order them by the name field and go through them visually. Or you could use a levenshtein() comparison or other algorithm to check them automatically.

swatisonee

Umm no, i just get a blank page although if i just print the values of $result['CID] , it lists all the entries so i know the sql is ok. I also know the numbers are missing ..so i dont know what could be going wrong.

As far as find "almost duplicates" - well this is what i feared ! i'm new enough to php to be struggling with smaller issues like missing numbers so i guess i have to do it manually.

Thanks anyway for the suggestion. IOf you can think of something else i could try for $missing, please let me know .

Thanks again !
Swati

liquorvicar

My fault, this line

for ($missing=$lastCID+1;$lastCID==$result['CID'];$lastCID++) {

should probably be

for ($missing=$lastCID+1;$missing==$result['CID'];$missing++) {

swatisonee

No. Same blank sheet.

liquorvicar

Doh! My bad.
🙁
Try this

 for ($missing=$lastCID+1;$missing<$result['CID'];$missing++) {

swatisonee

Bingo ! That works like a charm ! Thank you very much !