Why Doesn\'t LIMIT Work with Prepared Statements in MySQL?

LIMIT Keyword with Prepared Statements in MySQL

This question addresses an issue where the LIMIT clause doesn't work as expected when using prepared statements with the PDO library in MySQL.

In the provided code snippet:

<pre class="brush:php;toolbar:false">
$comments = $db->prepare($query); 
/* where $db is the PDO object */ 
$comments->execute(array($post, $min, $max)); 

The parameters $min and $max are treated as strings by the PDOStatement::execute method. Consequently, the final SQL statement becomes:

LIMIT '0', '10'

which results in a syntax error because MySQL requires numerical values for the LIMIT clause.

Possible Solutions:

To resolve this issue, consider the following options:

• Bind Parameters Manually:

  • Bind each parameter with an appropriate type using bindParam.
  • For example: $comments->bindParam(2, $min, PDO::PARAM_INT);

• Use String Interpolation:

  • Embed the $min and $max values directly into the query string using sprintf.

  • Example:

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

• Disable Emulated Prepares:

  • Set the PDO::ATTR_EMULATE_PREPARES attribute to FALSE to prevent MySQL from quoting numeric arguments.
  • Example: $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);

Note:
These methods will enable you to use the LIMIT clause with prepared statements correctly.

The above is the detailed content of Why Doesn\'t LIMIT Work with Prepared Statements in MySQL?. For more information, please follow other related articles on the PHP Chinese website!