Receiving Incomplete MySQL Result Set in Perl only

[consultant1027]

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>";

 

[consultant1027]

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.

 

[Kirsle]

Try using != instead of <>.

-------------
Cuvou.com | My personal homepage

perl -e '$|=$i=1;print" oo\n<|>\n_|_";x:sleep$|;print"\b",$i++%2?"/":"_";goto x;'

 

[consultant1027]

Thanks for the suggestion but unfortunately, doesn't fix it.

 

[audiopro]

my $model_exclude="LARGE";
my $dept="centre";
my $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";

produces

SELECT brand,name,collection,dept FROM models2 WHERE status < 8 && top <> [red]''LARGE[/red] and dept = 'centre' ORDER BY RAND() LIMIT 5

<> should be != and the speech marks should be round the var $model_exclude.
Using && and 'and in a sigle statement confused me too.


Keith

http://www.studiosoft.co.uk

 

[consultant1027]

That was an earlier misprint of the code. Sorry. This is the code:

$model_exclude = "and name != 'bozo'";
$dept = 'LEATH';
$model = 'home';
$sql_stmt = "SELECT brand,name,collection,dept FROM models2 WHERE status < 8 and 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>";
while (@dbfield = $sth->fetchrow_array())
{
print "found $dbfield[1]<br>";
}


$dbh->disconnect ();


Produces:


SELECT brand,name,collection,dept FROM models2 WHERE status < 8 and top != ''and name != 'bozo' and dept = 'LEATH' ORDER BY RAND() LIMIT 5
found 0


Copying and pasting the above into PhpMyAdmin (without the 'found 0' of course) produces:

Showing rows 0 - 4 (5 total, Query took 0.0071 sec)

SQL query: SELECT brand, name, collection, dept
FROM models2
WHERE STATUS <8
AND top != ''
AND name != 'bozo'
AND dept = 'LEATH'
ORDER BY RAND( )
LIMIT 5

 

[consultant1027]

I even tried removing LIMIT 5 to see if that makes a difference and it doesn't. I can only get results if I remove the top != ''

Interestingly if I remove the top != '' and the LIMIT 5, the Perl script produces 133 records and the direct call via PHPMyAdmin results in only 130 records.

Defintely something wacky going on with MySQL and Perl

 

[consultant1027]

Crap, I've got some weird stuff going on I've never noticed before. I created a more simple statement:

SELECT brand,name,collection,dept FROM models2 WHERE top != ''

In Perl I get 40 records, in PHPMyAdmin I get 69. There's actually 69 matching records if I examine all of them. Wondering why Perl is missing some records?

 

[audiopro]

What type of field is top?

Keith

http://www.studiosoft.co.uk

 

[consultant1027]

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.

 

[audiopro]

A case of wood and trees?
Glad you sorted it.
We all do stupid stuff - takes someone special to stand up and admit it.


Keith

http://www.studiosoft.co.uk

 

[KevinADC]

We all do stupid stuff

$audiopro = 'We all do stupid stuff';
$audiopro = reverse $audiopro;
$audiopro =~ s/lla eW/syawla I/;
$speak_for_yourself = reverse $audiopro;
print $speak_for_yourself;

------------------------------------------
- Kevin, perl coder unexceptional!

 

[audiopro]

What a complicated way of doing a simple task

Kevin, perl coder unexceptional

Now come on Kevin.
You mean you have never stared at something for ages, trying all kinds of complicated solutions only to discover a silly error.

That would make you exceptional.

Keith

http://www.studiosoft.co.uk

 

[KevinADC]

I never noticed it says unexceptional!







------------------------------------------
- Kevin, perl coder unexceptional!