PDO mysql LIMIT with placeholder in an array

Question

This is one more on the LIMIT clause and PDO, but I could not find a solution elsewhere.

I'm getting a PDO error and I know perfectly well why.

PDO requires an integer on LIMIT clause, but as the variables are included as part of the given parameter array, PDO treats all of it as string, hence the error.

The question is: how can I set this up to avoid the error?

For general information, this is part of a " load more " set up.

SQLSTATE[42000]: Syntax error or access violation

This is the query:

$param=array();
$param = array_merge($param,$geocities);
$param[] = $limit;
$param[] = $offset;
$in  = str_repeat('?,', count($geocities) - 1) . '?';
    $sql = "SELECT ads.*, cities.city  FROM ads
            INNER JOIN cities USING(city_id)
            WHERE cities.city_id IN ($in)
            AND ads.published IS NOT NULL
            AND ads.deleted IS NULL
            ORDER BY ad_id
            LIMIT ?
            OFFSET ?";
    $stmt = $ulink->prepare($sql);
    $stmt->execute($param);

@manniL, Not at all. I know perfectly well how to bind a value in LIMIT. The problem here is the use of placeholders and , therefore, the array. — BernardA, Mar 07 '16 at 23:14
An example code for your case is given by an answer of this question (http://stackoverflow.com/a/18063491/3975480) Look into that :) — manniL, Mar 07 '16 at 23:16
Since all parameters in the array will be treated as string, simple answer is - you can't do what you want via array of parameters. You must bind it manually via `bindValue` and by specifying a proper type (`PDO::PARAM_INT`). — N.B., Mar 07 '16 at 23:17
Alternatively, you can attempt to use MySQL's `CAST()`. I didn't try it so I've no clue if it works. `LIMIT CAST(? AS UNSIGNED)` or something along those lines. — N.B., Mar 07 '16 at 23:18
@manniL. Did try setAttribute, but the driver does not support that. Nice try though. — BernardA, Mar 07 '16 at 23:23
@Don'tPanic. The field is INT but, again, it does not matter how you cast it in the array, PDO treats all as string. — BernardA, Mar 07 '16 at 23:29

score 1 · Accepted Answer · answered Mar 07 '16 at 23:31

Instead of calling execute() with an array argument, you should be able to loop over $param and bind the values as INT one at a time before executing.

$stmt = $ulink->prepare($sql);
$i = 1;
foreach ($param as $value) {
    $stmt->bindValue($i++, $value, PDO::PARAM_INT);
}
$stmt->execute();

PDO mysql LIMIT with placeholder in an array

1 Answers1