How to Correctly Use LIMIT in MySQL Prepared Statements with PDO?

Using LIMIT Keyword in MySQL Prepared Statement

When using prepared statements in MySQL with PDO, you may encounter issues with the LIMIT keyword. The following query:

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

will not work with PDO prepared statements, while altering it to LIMIT 0, 10 will. This issue is rooted in the way PDO handles parameters.

PDO Parameter Binding

PDOStatement::execute() treats all input parameters as strings by default. As a result, the parameters $min and $max in the LIMIT clause will be inserted as strings. MySQL will then parse the query as:

LIMIT '0', '10'

leading to a parse error.

Solutions

There are several ways to resolve this issue:

• Bind Parameters Individually: Specify the type of each parameter using bindParam():

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

• Exclude Parameters from Query: Remove the $min and $max parameters from the query and specify them as part of the query:

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

• Disable Emulated Prepares: Disable emulated prepares in the MySQL driver:

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

By implementing one of these solutions, you can successfully use the LIMIT keyword with prepared statements in MySQL.

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