Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

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

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?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://972840]
Front-paged by Arunbear
[1nickt]: Yaerox probably best to whip up an SSCCE demonstrating the failure in what you have now and post to SoPW as a question.
[Corion]: Yaerox: That's a somewhat hard problem. Encode solves the conversion part, but for guessing what encoding a file is in, that's the hard part
[Corion]: Yaerox: There is Encode::Guess, but that needs a limited set of inputs, and it also cannot handle multiple single-byte encodings
[Corion]: If you have a BOM, that's a really easy way to recognize UTF-8. Otherwise, you can try to decode a file from UTF-8, and if that works OK and doesn't crash, most likely the file was valid UTF-8
[Corion]: But as "ansi" (Latin-1?) is a single-byte encoding, any file is a valid ANSI file

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (10)
As of 2017-03-28 13:13 GMT
Find Nodes?
    Voting Booth?
    Should Pluto Get Its Planethood Back?

    Results (332 votes). Check out past polls.