in reply to Explain SQL statement

Howdy! 

$sql = "SELECT * 
        FROM tbl_admin 
        WHERE day = '$d' 
            AND month = '$m_num' 
            and year = '$y' 
            AND expal IN ("
                .join(',',keys %expal).
         ") AND status= '2'";

[download]

I've reformatted this to make it clearer. The WHERE clause includes an "x in (list)" expression. The join command is constructing the list from the keys of the hash, separated by commas. If the hash happens to be empty, you get invalid or dubious SQL.

The SQL is being constructed by concatenating three expressions, one of which is a Perl join statement.

yours,
Michael

Replies are listed 'Best First'.
Re^2: Explain SQL statement
by Anonymous Monk on May 26, 2004 at 13:42 UTC
    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!
[reply]
[d/l]

      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]
      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]
        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]
        Whould guess why the code is returning nothing?
[reply]
      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]

In Section Seekers of Perl Wisdom