Pdo Limit and Offset

Php pdo and offset=0

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.

LIMIT using offset error

Your :skip gets wrapped in quotes as if it's a string (because the default setting is PDO::PARAM_STR), which is wrong, You now get:

LIMIT '5',5;

According to documentation, you can change a type to int:

$stmt->bindParam(":skip",$offset, PDO::PARAM_INT);

The reason this works in your PhpMyAdmin is because you don't add the single quotes.

Adding An Offset To Prepared SQL Statement PDO

Do all your variable manipulation prior to the bindValue calls. (Trim is probably unnecessary as you're casting to an int.)

try
{
     $DB_con = new PDO("mysql:host={$db_host};dbname={$db_name}",$db_user,$db_pass);
     $DB_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $exception)
{
     echo $exception->getMessage();
}

$limit  = (intval($_GET['limit']) != 0) ? (int) $_GET['limit'] : 5;
$offset = (intval($_GET['offset']) != 0) ? (int) $_GET['offset'] : 0;

try {
    $stmt = $DB_con->prepare("SELECT * FROM wuno_inventory LIMIT :limit OFFSET :offset");
    $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();
    $results = $stmt->fetchAll();
}
catch (Exception $ex) {
    echo $ex->getMessage();
}
if (count($results) > 0) {
    foreach ($results as $res) {
        echo '<tr class="invent">';
        echo '<td>' . $res['wuno_product'] . '</td>';
        echo '<td>' . $res['wuno_alternates'] . '</td>';
        echo '<td>' . $res['wuno_description'] . '</td>';
        echo '<td>' . $res['wuno_onhand'] . '</td>';
        echo '<td>' . $res['wuno_condition'] . '</td>';
        echo '</tr>';
    }
}

How to use prepared statement to set fetch & offset values with PDO sqlsrv?

Try this:

$statement = $pdo->prepare('SELECT * FROM livro ORDER BY id OFFSET CAST(:offset AS INT) ROWS FETCH NEXT CAST(:limit AS INT) ROWS ONLY');
$statement->execute(['offset' => 0, 'limit' => 5]);
$livro = $statement->fetch();

---

Related Topics