LIMIT keyword on MySQL with prepared statement

Answers of > LIMIT keyword on MySQL with prepared statement

Within prepared statements, LIMIT parameters w3coded php mysql can be specified using ? placeholder w3coded php mysql markers.,input_parameters An array of values w3coded php mysql with as many elements as there are bound w3coded php mysql parameters in the SQL statement being executed. w3coded php mysql All values are treated as PDO::PARAM_STR.,With w3coded php mysql PDO (I'm using MAMP 2.0.5 that has Apache w3coded php mysql 2.2.21, PHP up to 5.3.6, and MySQL 5.5.9) w3coded php mysql prepared statement this doesn't work, if I w3coded php mysql change the query with,Within stored programs, w3coded php mysql LIMIT parameters can be specified using w3coded php mysql integer-valued routine parameters or local w3coded php mysql variables.

Here's the problem:

$comments = $db->prepare($query); 
/* where $db is the PDO object */ 
$comments->execute(array($post, $min, $max));

Thus your parameters are getting inserted as strings, so the final SQL code looks like this:

LIMIT '0', '10'

This is a particular case where MySQL will not cast to number but trigger a parse error:

mysql> SELECT 1 LIMIT 0, 10;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> SELECT 1 LIMIT '0', '10';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0', '10'' at line 1

Bind parameters one by one so you can set a type:

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

Do not pass those values as parameters:

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

Disable emulated prepares (the MySQL driver has a bug/feature that will make it quote numeric arguments):

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

Bind parameters one by one so you can set a type:

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

Do not pass those values as parameters:

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

Disable emulated prepares (the MySQL driver has a bug/feature that will make it quote numeric arguments):

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

You can declare a specific attribute to resolve the problem.

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

Current topics : LIMIT keyword on MySQL with prepared statement

Newly Added Questions