assigning a Row_Number to my MySQL selection

Question

I have a problem of assigning a Row_Number to my MySQL selection, while ago I came across a piece of code that have proposed a solution for this situations. I tried the code in MySQL console and it was working well . But when I tried to use it in my PHP application as a query with PDO it returned this "SQLSTATE[HY000]: General error".

The code is as following

SET @row_count= NULL;
SELECT ad_ID , rn from (
    select ad_ID ,@row_count :=IFNULL (@row_count,0)+1 as rn 
    from ad_table
    order by rn ) OT
WHERE rn IN (1, 10 ,20 );

I've checked the Databse connection and it was OK. What am I doing Wrong ? And if it is not the suitable way of solving this problem, how can I accomplish this task??

Answer 1

You need to use exec() for 1st line. ie

$STH = $DBH->exec('SET @row_count= NULL');

Then

$sql ="SELECT id , rn from (  select id ,@row_count :=IFNULL (@row_count,0)+1 as rn from mytable order by rn ) OT WHERE rn  IN (1, 10 ,20 )";
$STH = $DBH->query($sql);