Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi there,
I asked a similar question here before, and since I found that the problem is when I run the sql query inside the "FOREACH LOOP" the code completely ignores the ORDER BY piece on the SQL QUERY CODE, why is this happening, how could I still use the sql code to run inside a foreach loop without breaking the sql code or better to not ignore the ORDER BY at the end of the SQL. The values of the hash key is a list of numbers like:
0020001
1134539
1234975
3446890
3456843
2234535

[download]

It has around 150 numbers on the list.
Here is the piece of the code that is driving me crazy:
foreach my $key_expal (%expal) {
            
            if($order == 2 ){
  
            $sql = "SELECT c_name FROM tbl_admin WHERE day = '$d' AND 
+month = '$m_num' and year = '$y' AND expal = '$key_expal' AND status=
+ '2' ORDER BY c_name ";
           
           }elsif($order == 3 ){
    
            $sql = "SELECT * FROM tbl_admin WHERE day = '$d' AND month
+ = '$m_num' and year = '$y' AND expal = '$key_expal' AND status= '2' 
+ORDER BY expal";             
            
            }else{
   
            $sql = "SELECT * FROM tbl_admin WHERE day = '$d' AND month
+ = '$m_num' and year = '$y' AND expal = '$key_expal' AND status= '2'"
+;            
           
            }

        $sth = $dbh->prepare($sql);
        $sth->execute() || 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]

Thanks for the help!

Replies are listed 'Best First'.
Re: Foreach Problem! Help!!!
by halley (Prior) on May 25, 2004 at 15:09 UTC
    If you iterate through a hash as if it were a list, you'll get all the keys AND all the values. Normally, you iterate through a list of keys, OR a list of values, but not both. I think you mean something more like the following. 
    for my $var (keys %hash) { ... }

    [download]

    --
    [ e d @ h a l l e y . c c ]

[reply]
[d/l]
      It still ignores the ORDER BY at the end, and it doesn't return the values in alphabetic order. It is frustrating...
[reply]
Re: Foreach Problem! Help!!!
by Roy Johnson (Monsignor) on May 25, 2004 at 16:06 UTC
    You're re-preparing and re-executing your query on each pass through the loop. Is that really what you want to do? Does the value of $order change in the loop?

    You should be using placeholders for your variables. Something like: 

    $sql =<<"SELECT * FROM tbl_admin WHERE day = '$d' AND month = '$m_num'
+ AND year = '$y' AND expal = '?' AND status= '2'";

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

$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]
    Because %expal is a hash, your queries will be run in arbitrary order, but the results of each query should be returned in the order your query specified.

    Ordering by expal is redundant, though, when you're specifying that only one expal is acceptable. You might need to rethink your query. Do you really want to use an IN: 

    $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]
    and no outer loop?
    The PerlMonk tr/// Advocate
[reply]
[d/l]
[select]
      The $order value is there only to say what SQL Query to run when user clicks on a button, the program prints a table with values and it has a button to select everything by alphabetic order that's when I am passing the value to $order.
      My question is to find out why the code doesn't work because of the first foreach loop on this line
      foreach my $key_expal (%expal) {...

      [download]

      Where I need to extract the value of $key_expal to run the SQL query on the database.
[reply]
[d/l]
      HI,
      $order change to change the ORDER BY value.
[reply]
        But $order does not change within the loop, so there's no need to re-examine it and re-construct the query on each pass through.

        Secondly, when $order == 3, you have ORDER BY expal, but your query will only return one value for expal, so that doesn't do anything.

        The PerlMonk tr/// Advocate
[reply]
[d/l]
        It seems that the problem is when the values from the hash is evaluated on the foreach loop and to the SQL code by this 
        AND EXPAL = '$key_expal'

        [download]
[reply]
[d/l]
Re: Foreach Problem! Help!!!
by xorl (Deacon) on May 25, 2004 at 15:44 UTC
    you need to sort the keys first otherwise it will be random as noted here Basically for my $var (sort keys %hash) { ... }
[reply]

Back to Seekers of Perl Wisdom