Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Display row number perl DBI

by romy_mathew (Beadle)
on May 28, 2012 at 16:22 UTC ( #972840=perlquestion: print w/ replies, xml ) Need Help??
romy_mathew has asked for the wisdom of the Perl Monks concerning the following question:

Hi, Can anybody suggest how to display a row number in mysql query. I am able to get this done in mysql query by

select @rownum:=@rownum+1 AS rank, p.* from player p, (SELECT @rownum:=0) r order by score desc limit 10

But when I use this in $sth = $dbh->prepare("Select @rownum:=@rownum+1 ‘rank’, p.* from player p, (SELECT @rownum:=0) r order by score desc limit 10");

I get errors and the statement is not getting executed. ANy Suggestion ...?

Comment on Display row number perl DBI
Replies are listed 'Best First'.
Re: Display row number perl DBI
by moritz (Cardinal) on May 28, 2012 at 16:32 UTC

    Always use strict; use warnings; at the beginning of your perl code. It catches common mistakes, including the one you are making.

    The problem is that Perl replaces variable names in double-quoted strings by their value (we call that "interpolation"), and @rownum happens to look like a variable name in Perl. So use single-quote strings instead:

    $sth = $dbh->prepare('Select @rownum:=@rownum+1 rank, p.* from player +p, (SELECT @rownum:=0) r order by score desc limit 10');
      Hi Thanks that worked ...but what if I need to use a variable in limit e.g

      my $temp = 50;

      $sth = $dbh->prepare('Select @rownum:=@rownum+1 rank, p.* from player +p, (SELECT @rownum:=0) r order by score desc limit $temp');

        You use placeholders:

        $sth = $dbh->prepare('Select @rownum:=@rownum+1 rank, p.* from player ++p, (SELECT @rownum:=0) r order by score desc limit ?'); # and later $sth->execute($temp);

        This is generally safer than interpolating variables into SQL strings, and prevents SQL injection attacks.

        See also: DBI, and perlop for the rules about string interpolation and escaping.

        you can also use backslash
        $sth = $dbh->prepare("Select \@rownum:=\@rownum+1 rank, p.* from playe +r +p, (SELECT \@rownum:=0) r order by score desc limit $temp");
        but it looks a little oddly

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://972840]
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (10)
As of 2015-07-31 04:19 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (274 votes), past polls