$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.

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!

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.

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

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

It builds an SQL statement headed for a database. And does it in a dangerous way.

• SELECT * is slow, and the order the rows come out is not guarenteed. The rows you want to return should be explicitly named in almost every case.
• Constructs like day = '$d' are dangerous, due to a potential SQL-injection attack. It also negates the use of caching in common cases. Placeholders should be used instead.
• It has a generally poor style. A few newlines and good indentation would make it a lot easier to read.

My suggested style would be:

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

However, note that the above only addresses style issues--the other problems mentioned are not fixed. Further, each programmer is going to have their own style, so I'm sure someone else might format the above in a different way. I am sure, though, that everyone would agree that the above is signficantly better style than the orginal.

In short, do not take this bit of code as anything resembling good coding practice. Judging from the above, I wouldn't trust anything that particular programmer wrote.

For examples of good database practice, I recommend A Short Guide to DBI on perl.com.

For what it's worth, "this particular programmer" was only illustrating how the use of IN would change the code, not providing a complete, recommended solution.

---

The PerlMonk tr/// Advocate

Could I try to place the values in alphabetic order after since I can't figure it out on the SQL query.
Like to a SORT on the returned values from the "fetchrow_hashref"
Do a SORT here in one of the returned values


$comp=$pointer->{'comp_name'};
[download]

$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]

Abigail

join( ',' , keys( %expal ) )

it takes the keys from hash %expal and puts them into a string, separated by commas ",".

my $sql = "SELECT * FROM tbl_admin WHERE day = '$d' ....";
my $sth = $dbh->prepare($sql);
$dbh->exec();
[download]

my $sql = "SELECT * FROM tbl_admin WHERE day = ? ....";
# note the ? (question mark) placeholder
my $sth = $dbh->prepare($sql);
$dbh->exec($d);
[download]