CODEWITHSUNDEEP
phpmysqlpdo
VeryBadUser
VeryBadUser

Reputation: 113

Php pdo and offset=0

I am trying to build an application in PHP PDO. 

$sql = 'SELECT * FROM journal where LIMIT :limit OFFSET :offset';  
$res = db_con->prepare($sql);  
$res->bindParam(':limit', $limit, PDO::PARAM_INT);
$res->bindParam(':offset', $offset, PDO::PARAM_INT);
$res->execute();

It's work. But if $offset=0, i get error

PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in

Upvotes: 1

Views: 1466

Answers (1)

Gerard Roche
Gerard Roche

Reputation: 6391

Your SQL query is invalid.

You have:

$sql = 'SELECT * FROM journal where LIMIT :limit OFFSET :offset';

As @Jessie Jackson points out, why is the "where" part empty?

The following is valid"

$sql = 'SELECT * FROM journal LIMIT :limit OFFSET :offset';

I don't why you where getting the error that were you getting:

PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in

The error with the invalid where statement should look something like this:

PDOException: 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 'LIMIT ? OFFSET ?' at line 1 in /path/to/stackoverflow/tmp/pdo.php on line 18

And to be absolutely sure, the following test works fine:

<?php

$pdo = new PDO(
    'mysql:dbname=test',
    'yser',
    'pass'
);

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

$limit = 0;
$offset = 0;

$sql = 'SELECT * FROM journal LIMIT :limit OFFSET :offset';
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$res = $stmt->execute();

var_dump($res);
var_dump($stmt->fetchAll());

So, why you are getting that error is nothing to do with $offset=0.

Update

It's possible the reason is if you have PDO::ATTR_EMULATE_PREPARES set to true, read Parametrized PDO query and LIMIT clause - not working.

Upvotes: 2

Related Questions