How Does MySQL's LIMIT Keyword Work with Prepared Statements and Parameter Binding?

How LIMIT Keyword Works with Prepared Statements in MySQL

The LIMIT keyword in MySQL is used to restrict the number of rows returned by a SELECT statement. It takes two integer arguments: an offset and a limit.

In the following example, the LIMIT clause is used to select the first 10 rows from the comment table, ordered by the date column in descending order:

SELECT id, content, date
FROM comment
WHERE post = ?
ORDER BY date DESC
LIMIT ?, ?

To execute this query using a PDO prepared statement, you would bind the three query parameters to the appropriate data types:

$query = $db->prepare($sql);
$query->bindParam(1, $post, PDO::PARAM_STR);
$query->bindParam(2, $min, PDO::PARAM_INT);
$query->bindParam(3, $max, PDO::PARAM_INT);
$query->execute();

However, if you try to execute this query with emulated prepares enabled (the default setting for the MySQL PDO driver), you will encounter an error. This is because the MySQL driver will automatically cast the second and third query parameters to strings, which will cause the LIMIT clause to fail.

To resolve this issue, you can either disable emulated prepares or use positional placeholders instead of named placeholders in your query.

Disabling Emulated Prepares:

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

Using Positional Placeholders:

SELECT id, content, date
FROM comment
WHERE post = ?
ORDER BY date DESC
LIMIT ? OFFSET ?

$query = $db->prepare($sql);
$query->execute([$post, $min, $max]);

The above is the detailed content of How Does MySQL's LIMIT Keyword Work with Prepared Statements and Parameter Binding?. For more information, please follow other related articles on the PHP Chinese website!