in reply to Re: Display row number perl DBI
in thread Display row number perl DBI

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');

Replies are listed 'Best First'.
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]

In Section Seekers of Perl Wisdom