Lord, good day! Faced a strange problem. There is a method

public function getResultArray($mode = PDO::FETCH_ASSOC) { $stmt = $this->db_conn->prepare("SELECT * FROM table LIMIT ?"); if (!$stmt->execute(array('1'))) var_dump($stmt->errorInfo()); }

Var dump gives the following error:

array (3) {[0] => string (5) "42000" [1] => int (1064) [2] => string (149) "check syntax; check the manual that corresponds MySQL server at line 1 "}

If the hands instead of the placeholder enter 1 then it is executed correctly. I do not understand what is wrong here

As a result, it is planned to return the result of FetchAll, but for clarity of the question, the function simplified

  • and why is the string passed in an array as a parameter? - teran

2 answers 2

All good is the binding of the arguments of the request in a simple array in the execute - simple, compact. One problem interferes with living - and that's just so easy to plunge into the limit with it. And with a completely incomprehensible text of the error.

When emulation of prepared expressions is enabled ( PDO::ATTR_EMULATE_PREPARES , enabled by default), the execute substitutes the parameters only as strings, even if PHP knows that the variable is of a numeric type. Turns out

 ... limit '1'

What the SQL parser is on the side of the DBMS is offended.

When emulation is disabled, the limit will work fine if you pass a number to execute . The default emulation is enabled, since not all DBMS that PDO can work with support the work with prepared expressions.

The second solution is to bind the parameters by explicitly calling bindValue or bindParam indicating the type. In this case, you still need to pass a number, not a string with a number, for some reason, the PDO will not bring the type)

 $stmt->bindValue(1, 1, PDO::PARAM_INT); $stmt->execute();

    Try a variant with bindParam with conversion to int :

     $stmt->bindParam(1, $limit, PDO::PARAM_INT); //$limit - в вашем случае 1 $stmt->execute();

    See more examples here.