PDO - Problem with Fetchall

Question

Good afternoon friends, my problem today is the following. I have the following query:

set @row_number = 0;
SELECT @row_number:=@row_number+1 AS row_number,il.* from itemloja il order by il.LojaId

As you can see, it’s two that depend on each other. I want to get her result in PHP, so I use

$results = $go->fetchAll(PDO::FETCH_ASSOC);

But he’s making the following mistake:

General error' Pdostatement->fetchAll(2)

I’ve tried O FETCH_ORI_FIRST with the FETCH_ORI_NEXT It didn’t help either. Does anyone know how I can fix this?

EDIT

This is my code

    $Query = "set @row_number = 0; 
              SELECT @row_number:=@row_number+1 AS row_number,il.* 
              from itemloja il order by il.LojaId";

    $go = $pdo->prepare($Query);
    $go->execute();
    $results = $go->fetchAll(PDO::FETCH_ASSOC);

Answer 1

You are running multiple queries with PDO, which is usually not supported.

The first query consists of set @row_number = 0; (note the ; at the end of statement) and the second query is

SELECT @row_number:=@row_number+1 AS row_number,il.* 
from itemloja il order by il.LojaId";

Your options are a) use PHP to count row by row or b) enable multiple queries in PDO.

Add a counter through PHP

// Query simples, sem vários statements
$Query = "SELECT il.* FROM itemloja il ORDER BY il.LojaId";

$go = $pdo->prepare($Query);
$go->execute();
$results = $go->fetchAll(PDO::FETCH_ASSOC);
$i = 0;
foreach($results as $it){
    $it->rowNumber = ++$i;
}

---

Working with Multiple Queries with PDO_MYSQLND

Content originally posted by Sam Dark(modified by Your Common Sense) in the Stackoverflow English and translated by myself

To execute multiple queries you will need

• PHP 5.3+
• Mysqlnd
• Emulator of Prepared Statement. Rest assured that PDO::ATTR_EMULATE_PREPARES is configured as 1 (default). Another option is to use $pdo->exec directly.

exec

$db = new PDO("mysql:host=localhost;dbname=test", 'root', '');

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);

$sql = "set @row_number = 0; 
        SELECT @row_number:=@row_number+1 AS row_number,il.* 
        from itemloja il order by il.LojaId";

try {
    $db->exec($sql);
} catch (PDOException $e) {
    echo $e->getMessage();
    die();
}

statements

$db = new PDO("mysql:host=localhost;dbname=test", 'root', '');

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);

$sql = "set @row_number = 0; 
        SELECT @row_number:=@row_number+1 AS row_number,il.* 
        from itemloja il order by il.LojaId";
try {
    $stmt = $db->prepare($sql);
    $stmt->execute();
} catch (PDOException $e) {
    echo $e->getMessage();
    die();
}

Answer 2

gives a print of phpinfo() at the part where is the PDO and pdo_mysqlnd or any other that you are using to "connect" the Pdo to mysql;

if you are sure that it is multiquery, add the line:

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);

before QUERY.

REMINDING, this enabled multiqueries function makes SQL Injection attacks more manageable....