sql query to check for dupes...got it working, but not exact...

[spewn]

here's the code:

$passedvariable='whatevernextinloop';

  $sth = $dbh->prepare("SELECT uniqueid FROM my_table WHERE infofield1 = '$passedvariable'");
  $sth->execute;
  $count = $sth->rows;

   if ($count > 0) {$dupeleadcount++;$dispo='dupe';}
   else {$loadtotalcount++;$dispo='';}

The idea being that on the first pass, the passed variable would be clean, but on the second pass it would match.

This works, but not fully, as it doesn't catch all dupes and I haven't figured out the pattern of error to figure it out.

Anyway, I think it might be the way i'm getting the $passedvariable, as this:

  $sth = $dbh->prepare("SELECT infofield1, COUNT(infofield1) FROM my_table GROUP BY infofield1 HAVING ( COUNT(infofield1) > 1 )");
  $sth->execute;

works exactly to show me the dupes in my_table, but doesn't match a couple that are exactly the same to me, visually.

i'm pulling the information from a text file, where each entry is on its own line, and it goes line by line. I thought that it might by a newline issue, so I chomped the line variable before matching/saving in DB.

this is a more complete code example:

open (ULFNAME, $myfilelocation);
binmode ULFNAME;
@lines = <ULFNAME>;
@lines = sort(@lines);
 foreach $line (@lines) {
 chomp($line);
  if ($line ne '') {
  $sth = $dbh->prepare("SELECT uniqueid FROM my_table WHERE infofield1 = '$line'");
  $sth->execute;
  $count = $sth->rows;
   if ($count > 0) {$dupecount++;$dispo='dupe';}
   else {$dispo='unique';}
  $sth2 = $dbh->prepare("insert into my_table values ('0','$dispo','$line')");
  $sth2->execute;
  }
 }

any help would be appreciated. like i said, even the SQL matcher wasn't recognizing two entries that are visually the same to me.

thanks!

- g

 

[PinkeyNBrain]

You referenced 'visually the same' - two ideas:
1 - Use the 'like' operator versus = and see if your counts change. Leading spaces, trailing spaces, and non-printable characters can be hard to find.

2 - Does your font present an obvious difference between O's (ohs) and 0's (zeros)? l's (ells) and 1's (ones) - etc. You may have two entries that look the same but are not.