Beefy Boxes and Bandwidth Generously Provided by pair Networks Frank
Perl: the Markov chain saw
 
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
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 contemplating the Monastery: (5)
As of 2014-04-17 02:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (437 votes), past polls