PDO - Problem with FetchAll

Question

PDO - Problem with FetchAll

Navigation

#1 by (2 votes)

2

Good afternoon, my friends, my problem today is this. 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, there are two that depend. I want to get the result of it in PHP, so use

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

However, it is giving the following error:

General error 'PDOStatement-> fetchAll (2)

I've already tried FETCH_ORI_FIRST with FETCH_ORI_NEXT and it did not work either. Does anyone know how I can resolve 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);

php mysql pdo

asked by anonymous 07.01.2016 / 20:54

1 answer

Routine included in ThreadPool to run in "background" does not let the application continue running Position of DIV relative to another with dynamic content

score 2 · Answer 1

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

The first query is made up of set @row_number = 0; (note the ; at the end of the 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 line by line or b) enable multiple queries in PDO.

Adding 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;
}

Work with Multiple Queries with PDO_MYSQLND

Content originally posted by Sam Dark (modified by Your Common Sense ) at StackOverflow English and translated by me

To run multiple queries you will need

PHP 5.3 +
MySQLND
Prepared Statement emulator . Make sure that PDO::ATTR_EMULATE_PREPARES is set to 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();
}