Re^2: Explain SQL statement

Thanks, I am running this SQL code inside of a foreach loop like:

foreach $key_pal (sort keys %pal) {
$sql ="SELECT * FROM app_admin WHERE day = '$d' AND month = '$m_num' A
+ND year = '$y' AND NUMALL IN (".join(',',keys %pal).") AND status= '2
+' ";

if($order == 2 ) {
    $sql .= " ORDER BY c_name";
}
elsif ($order == 3 ) {
    $sql .= " ORDER BY NUMALL";            
}

$sth = $dbh->prepare($sql);

foreach my $key_expal (keys %expal) {
    $sth->execute($key_expal) || die $sth->errstr;

    while ($pointer = $sth->fetchrow_hashref) {
        my $expalnum  = $pointer->{'expal'};
        my $comp      = $pointer->{'c_name'};        
        my $cltime    = $pointer->{'time'};
        my $cltime2   = $pointer->{'time2'};
        my $last_name = $pointer->{'last_name'};
        my $clnum     = $pointer->{'c_number'};
        my $status    = $pointer->{'status'};
        print "<br>L 265 - <b>$comp</b> $expalnum";
    }
}
[download]

Why is it printing multiple versions of the results?
Thank you!

Comment on Re^2: Explain SQL statement Download Code

Replies are listed 'Best First'.
Re^3: Explain SQL statement by hardburn (Abbot) on May 26, 2004 at 14:00 UTC
Why is it printing multiple versions of the results? Because you asked it to: `foreach my $key_expal (keys %expal) { $sth->execute($key_expal) \|\| die $sth->errstr; . . .` [download] This means that for every entry in the `%expal` hash, the statement will be executed with that argument. Since you don't have any placeholders in your statement, `execute` ought to fail in this case, since the params passed are used to fill-in placeholders. ---- send money to your kernel via the boot loader.. This and more wisdom available from Markov Hardburn.	[reply] [d/l] [select]
Re^3: Explain SQL statement by herveus (Prior) on May 26, 2004 at 13:58 UTC
Howdy! It returns the same set of results once for each key in %expal. You pass a value to execute, but there are no placeholders in the query to receive the value. If you change `AND NUMALL IN (".join(',',keys %pal).")` to `AND NUMALL = ?`, the select will return only the rows where NUMALL is that value in each pass. yours, Michael	[reply] [d/l] [select]
Re^4: Explain SQL statement by Anonymous Monk on May 26, 2004 at 14:06 UTC
Tried and ruturned nothing `$sql ="SELECT * FROM app_admin WHERE day = '$d' AND month = '$m_num' A +ND year = '$y' AND NUMALL ='?' AND status= '2' ";` [download] Tks...	[reply] [d/l]
Re^5: Explain SQL statement by herveus (Prior) on May 26, 2004 at 14:08 UTC
Howdy! You quoted the question mark. Don't do that. Read my previous note carefully. The naked ? gets replaced by the value you pass to the execute command, and DBI makes sure it is properly quoted. You want `NUMALL = ?`, not `NUMALL = '?'`. yours, Michael	[reply] [d/l] [select]
Re^6: Explain SQL statement by Anonymous Monk on May 26, 2004 at 14:58 UTC
Re^4: Explain SQL statement by Anonymous Monk on May 26, 2004 at 14:29 UTC
Whould guess why the code is returning nothing?	[reply]
Re^3: Explain SQL statement by Roy Johnson (Monsignor) on May 26, 2004 at 14:35 UTC
The IN grabs all the values you want in one query, so you should not have the foreach loop. Just one execute and the while/fetch. The PerlMonk `tr///` Advocate	[reply]