Mysql row number function cannot work with PDO php

Question

Why does the code below does not work

$db = new PDO(...); //init the PDO

$query = $db->query("
             Set @ids := 0;
             SELECT username, @ids := @ids + 1 as id FROM user 
           ");

$query->execute();

If I didn't use mysql row number function it work find. So does it mean I cannot use mysql row number function with php?

You are using multiple queries. See http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd — Clarkey, Dec 04 '14 at 08:32

score 2 · Accepted Answer · answered Dec 04 '14 at 08:35

PDO doesn't allow multiple queries in a single call. You could do it by calling $db->query() multiple times: first do the SET query, then do the SELECT.

However, instead of multiple statements, you can do it all in a single query with a JOIN:

SELECT username, @ids := @ids + 1 AS id
FROM user
JOIN (SELECT @ids := 0) AS var

Mysql row number function cannot work with PDO php

1 Answers1