MySQL PDO limit issue

Question

I'm getting an error when running a PDO MySQL query and can't work out why

If I do the following

    $sql = 'SELECT * FROM ' . $table;
    if ($where !== false)
        $sql .= ' WHERE ' . implode(' AND ', $where);
    $sql .= ' LIMIT 0, 1';
    $variables = array('index');

    $this->DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );

    $statement = $this->DBH->prepare($sql);
    $statement->execute($variables);
    $statement->setFetchMode(PDO::FETCH_ASSOC);
    $rows = $statement->fetch()

It works fine, and I get the expected result. But if I change

    $sql .= ' LIMIT 0, 1';
    $variables = array('index');

To

    $sql .= ' LIMIT ?, ?';
    $variables = array('index', 0, 1);

$rows returns an empty array and I the following warning;

Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0', '1'' at line 1 in file.php on line 96("$statement->execute($variables);"

The outputted $sql and $variables are:

SELECT * FROM ma_pages WHERE url = ? LIMIT ?, ? Array ( [0] => index [1] => 0 [2] => 1 )

score 1 · Accepted Answer · answered Dec 18 '15 at 06:05

1

Try this out:

$sql = 'SELECT * FROM ' . $table;
if ($where !== false)
    $sql .= ' WHERE ' . implode(' AND ', $where);
$sql .= ' LIMIT :skip, :max';
$variables = array('index');

$this->DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );

$statement = $this->DBH->prepare($sql);
$statement->bindValue(':skip', 0, PDO::PARAM_INT);
$statement->bindValue(':max', 1, PDO::PARAM_INT);
$statement->execute();
$statement->setFetchMode(PDO::FETCH_ASSOC);
$rows = $statement->fetch()

answered Dec 18 '15 at 06:05

zedfoxus

35,121
5
64
63

I don't want to use blindValue – milky_jay Dec 18 '15 at 06:12
Have you tried skipping bindValue and trying `$statement->execute(array(':skip'=>0, ':max'=>1));`? – zedfoxus Dec 18 '15 at 06:14
1

Just under your setAttribute statement, can you add this and try again? `$this->DBH->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );` – zedfoxus Dec 18 '15 at 06:24
Excellent. Here's a good explanation that might help you: http://stackoverflow.com/questions/10113562/pdo-mysql-use-pdoattr-emulate-prepares-or-not. Also, if you are satisfied with the answers, please feel free to mark one of the answers as accepted to give closure to your question. Thank you. – zedfoxus Dec 18 '15 at 06:35

Thamilhan · Answer 2 · 2015-12-18T06:13:05.013

0

You have to use PDO::PARAM_INT like this:

$sql .= " LIMIT :start, :end";

$statement->bindParam(':start', $start, PDO::PARAM_INT);
$statement->bindParam(':end', $end, PDO::PARAM_INT);

edited Dec 18 '15 at 06:13

answered Dec 18 '15 at 06:04

Thamilhan

13,040
5
37
59

MySQL PDO limit issue

2 Answers2