SQL LIMIT：PDOStatement :: execute（）：SQLSTATE [HY093]：参数号无效：绑定变量数与令牌数不匹配

Question

I am using PDO to retrieve data from php. I am trying to use limit function but sadly it did not work. I searched around the forum for similar questions and found answers similar to what I tried below. But I get the same warning. I am relatively new to PDO. Am I doing something wrong?

$limit = 5;
$users = $db->prepare("SELECT code,name from Portion where name LIKE '%$t%' LIMIT :limit");
$users->bindParam(':limit', $limit, PDO::PARAM_INT);
$users->execute(['query' => "{$_GET['query']}%"]);

Answer 1

When you want to fill in parameters of a PDO query, you either

• Use bindParam() or bindValue() to bind them before calling execute(), or
• Provide an array of values as an argument to execute().

You can't mix them -- when you supply the array argument, that overrides the bindParam settings. Since you're passing an array argument to execute() (although for no apparent reason, since there's no :query parameter in the SQL), the :limit parameter is being lost.

Change your code to:

$limit = 5;
$users = $db->prepare("SELECT code,name from Portion where name LIKE CONCAT('%', :pattern, '%') LIMIT :limit");
$users->execute([':pattern' => $t, ':limit' => $limit]);