Display row number perl DBI

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');` [download] Perl 6 - the future is here, just unevenly distributed	[reply] [d/l] [select]
Re^2: Display row number perl DBI by romy_mathew (Beadle) on May 28, 2012 at 17:50 UTC
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');	[reply]
Re^3: Display row number perl DBI by moritz (Cardinal) on May 28, 2012 at 18:13 UTC
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);` [download] 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. Perl 6 - the future is here, just unevenly distributed	[reply] [d/l]
Re^3: Display row number perl DBI by vinian (Beadle) on May 29, 2012 at 08:45 UTC
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");` [download] but it looks a little oddly	[reply] [d/l]