How to Use LIMIT with MySQL Prepared Statements in PDO?

LIMIT Keyword on MySQL with Prepared Statement

When attempting to execute a prepared statement with the LIMIT keyword, you may encounter an error if you are using the PDO (PHP Data Objects) library. This is because PDO treats all parameters as strings, which can lead to parse errors when using numeric values for the LIMIT arguments.

To resolve this issue, you can consider the following options:

1. Bind Parameters One by One:

You can manually bind each parameter with the appropriate type, ensuring that the LIMIT arguments are treated as integers:

$comments->bindParam(1, $post, PDO::PARAM_STR);
$comments->bindParam(2, $min, PDO::PARAM_INT);
$comments->bindParam(3, $min, PDO::PARAM_INT);

2. Disable Prepared Statements:

Another option is to disable emulated prepares, which can lead to arguments being quoted:

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);

3. Do Not Pass LIMIT Values:

Instead of passing the LIMIT values as parameters, you can embed them directly into the query string:

$query = sprintf('SELECT id, content, date
FROM comment
WHERE post = ?
ORDER BY date DESC
LIMIT %d, %d', $min, $max);

By avoiding the use of emulated prepares or explicitly setting parameter types, you can successfully execute prepared statements with the LIMIT keyword in MySQL.

The above is the detailed content of How to Use LIMIT with MySQL Prepared Statements in PDO?. For more information, please follow other related articles on the PHP Chinese website!