By evnix on

i have a simple SQL statement below, 

$result= db_query("SELECT * FROM {users} LIMIT :val", array(
  ':val'=>5
));

but it ends but with the following error.

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 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 ''5'' at line 1: SELECT * FROM {users} LIMIT :val; Array ( [:val] => 5 ) in

but the following code works perfectly,

    $result= db_query("SELECT * FROM users LIMIT 10");

The error occurs only when using the LIMIT keyword. I know about db_query_range() but I am wondering why my prepared statement did not work.

⋅ Categories: Drupal 7.x

Comments

Alex Komm’s picture

Alex Komm commented

When you attempt to use this statement

<?php
$result= db_query("SELECT * FROM {users} LIMIT :val", array(
  ':val'=>5
));
?>

your result SQL will be broken (look at the quotes):
SELECT * FROM users LIMIT '5';

slippast’s picture

slippast commented

I just stumbled onto problem today. Is there any way around this? Sort of frustrating. I just dropped the PHP variable right in the query and moved on with my life. Gasp! :)

Jaypan’s picture

Jaypan commented

You can use db_query_range().

geerlingguy’s picture

geerlingguy commented

And, in this case, the correct use would be:

  $result = db_query_range("SELECT * FROM {users}", 0, 5);

If you needed to add placeholders for other parts of the query, it would be like:

  $result = db_query_range("SELECT * FROM {users} WHERE mail = :mail", 0, 5, array(':mail' => $mail));

__________________
Personal site: www.jeffgeerling.com