I've got a Perl program that is returning an incomplete result set. I can output the mySQL call that Perl is executing. In the perl program it gets less records back than if I copy and paste the statement into phpMyAdmin and execute it.

Statement Example:

SELECT brand,name,collection,dept FROM models2 WHERE status < 8 && top <> '' && name <> 'home' && name <> 'Nappa Vitello Keyholder 6-Hook' and dept = 'LEATH' ORDER BY RAND() LIMIT 5

This statement returns 0 records when executing in PERL and returns 5 records when executed from within PhpMyAdmin!

Here's the code that is executing it.

{
$sql_stmt = "SELECT brand,name,collection,dept FROM models2 WHERE status < 8 && top <> ''".$model_exclude;
$sql_stmt.= " and dept = '$dept'" if ($dept);
$sql_stmt.= " ORDER BY RAND() LIMIT 5";
# print "<br><font size=1>$sql_stmt</font><br>";
$sth = $dbh->prepare ("$sql_stmt");
$sth->execute();
my $found = $sth->rows();
print "found $found<br>";

If I remove the top <> '' filter from the sql statement I get 5 results back, but that's because only about 10 records that match have top <> '' and if I take that out, about 600 records match. Top is char[1] field type and the value is either nothing '' or 'Y' For some reason when executed in PERL it gets 0 records but executed in PHPMYAdmin it returns the correct results with 5 matching records.

Oh gosh this is embarassing. For like 6 years my test site and production site were accessing the same database so I could test with the same data set. I forgot a couple months ago when I was doing a bunch of data conversion I separated it into a test and production DB. I forgot to change the connection back on the test site to the production DB so I was running the perl app on one slightly outdated DB and the PHPMyAdmin was logged into the current DB. Duh.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.