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

Hi Monks;
I have a piece of my code here to show and to ask for help understanding why my SELECT statement doesn't work when I am selecting it using ORDER BY, it works if I query the DB it self but in my perl code it doesn't print the result in the order I am specifying on the code.
Here is the code I know this isn't the place for SQL question but I guess some of you might had similar problems like that before.
Thanks for the help, here is the code:
foreach my $key (%num) {

            
            if($order == 2 ){
   
        $sql = "SELECT * FROM admin WHERE day = '$d' AND month = '$m_n
+um' and year = '$y' AND NUM = '$num' AND status= '2' ORDER BY name";
           
           }else{
   
            $sql = "SELECT * FROM admin WHERE day = '$d' AND month = '
+$m_num' and year = '$y' AND NUM = '$num' AND status= '2'";           
+ 
            
            }
            
        $sth = $dbh->prepare($sql);
        $sth->execute() || die $sth->errstr;
        
                        
        while ($pointer = $sth->fetchrow_hashref)
        {
         my $num=$pointer->{'NUM'};
         my $name=$pointer->{'name'};
        }
        
        print "NAME = $name<br>";
        
}

[download]

Replies are listed 'Best First'.
Re: Perl AND SQL Help
by injunjoel (Priest) on May 24, 2004 at 16:50 UTC
    Greetings all,
    Here are a few suggestions for your original code from what I could tell was going on. It looks close just a few things I would change.
    my $sql = "SELECT * FROM admin WHERE day = ? AND month = ? and year = 
+? AND NUM = ? AND status= ?";
$sql .= " ORDER BY name" if ($order == 2);

$sth = $dbh->prepare($sql) || die $dbh->errstr;
my $rv = $sth->execute($d, $m_num, $y, $num, '2') || die $sth->errstr;
if($rv && ($rv ne '0E0')){
   while(my $pointer = $sth->fetchrow_hashref()){
      print "NAME = ".$pointer->{'name'}."<br />";
   }
}

    [download]
    First: Use placeholder '?' for your sql statements, they allow you to prepare a statement once and reuse it.
    Second: As others have stated watch out for the scope of your variables. my $name is only visible (scoped) within the while loop so the other reference to $name is not the $name from within the while loop. I would suggest useing warnings and strict
    #!/usr/bin/perl -w 
use strict;

    [download]
    to find variable conflicts like this.
    Third: check the return values of your db executes for success

    that is all for my suggestions. As for why your data is not being sorted the way its ordered from the db I would suggest either printing from within the while loop or create an array of hashes (though this might not be the best idea depending on how many results you are looking at potentially getting from this query). Either way although the hash itself will not be ordered at least its position relative to the others (sql order by) will be.
    -injunjoel
[reply]
[d/l]
[select]
      Hi, it still ignores the ORDER BY code...
[reply]
Re: Perl AND SQL Help
by jZed (Prior) on May 24, 2004 at 16:01 UTC
    Aside from the already mentioned fact that you are trying to print $name outside of its scope, I see two potential problems: 1) NUM = '$num' ... numeric values shouldn't be quoted in SQL although your backend may or may not care (which backend, btw?) 2) You can't necessarily depend on the case of the keys in a fetchrow_hashref() unless you have previously declared FetchHashKeyName, see the DBI docs on FetchHashKeyName.
[reply]
      I have to say that $num inside of the while loop by my mistake shouln'd be there, it sould be any other name for the new value returned from the DB, like $new_name.
[reply]
Re: Perl AND SQL Help
by bradcathey (Prior) on May 24, 2004 at 16:21 UTC
    I have found better success with: fetchall_arrayref({}) as suggested by Happy-the-monk, especially if you are expected expecting more rows. Try something like this: 
    . . .
$pointer = $sth -> fetchall_arrayref({});

    for my $i (0 .. $#$pointer) {
        my $num = $pointer->[$i]{'NUM'};
        my $name = $pointer->[$i]{'name'};
        
        print "NAME = $name<br />";
    }

    [download]
    Suggested reading.

    —Brad
    "A little yeast leavens the whole dough."
[reply]
[d/l]
[select]
Re: Perl AND SQL Help
by Happy-the-monk (Canon) on May 24, 2004 at 15:34 UTC

    but in my perl code it doesn't print the result in the order I am specifying on the code.

    My vague attempt at an answer is: hashes in Perl aren't in a specific order, even if you feed an ordered list into a hash. So chose a different method,   fetchrow_arrayref   maybe.

    Cheers, Sören

[reply]
      Umm, fetchrow_hashref() will return the columns in a row in a possibly non-predictable order, but what the OP is asking about is not the order of the columns in a row, but rather the order of the rows in the table.
[reply]
Re: Perl AND SQL Help
by Anonymous Monk on May 24, 2004 at 15:42 UTC
    The first problem I see is that $name is local to the while block.
    It looks like you are expecting only one row from this query?
    If so you don't need a while
    Also, you don't test for error on the fetch.
    try this...
    $pointer = $sth->fetchrow_hashref;
die $sth->errstr if $sth->errstr;
print "NAME = $pointer->{name}<br>";

    [download]
[reply]
[d/l]
      I do need more rows from this query, I am only showing one as an exemple.
[reply]
        I that case, shouldn't your print statement be inside the while clause?
[reply]

        You will get better answers if you show us the actual code instead of just this exemple.

         

[reply]
Re: Perl AND SQL Help
by iburrell (Chaplain) on May 24, 2004 at 23:02 UTC
    One thing to try is print out the sql before it gets executed. It is possible that $order is not set how you think and you are executing the SQL without the ORDER BY.
[reply]

Back to Seekers of Perl Wisdom