PDO为不同的查询准备了一次语句绑定参数

Question

I am using PDO prepared statements to execute two queries:

SELECT count(*) FROM vocabulary WHERE  `type` = :type AND `lesson` = :lesson;
SELECT * FROM vocabulary WHERE  `type` = :type AND `lesson` = :lesson limit 100;

The first query to get the count works as expected and i get the row count.

$stmt = $this->connection->prepare($sql);
foreach ($params as $key => $value)
    $stmt->bindValue(":" . $key, $value, PDO::PARAM_STR);

$stmt->execute();
$count = $stmt->fetchColumn();

$sql .= " limit $limit;";
$sql = str_replace("count(*)", $columns, $sql);
$stmt = $this->connection->prepare($sql);
$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_CLASS, $class);

But when executing the second query i get:

SQLSTATE[HY093]: Invalid parameter number: no parameters were bound

Therefore, I would like to know, if I have multiple queries where the parameters are exactly the same ,if I need to bind the same parameters again using

foreach ($params as $key => $value)
    $stmt->bindValue(":" . $key, $value, PDO::PARAM_STR);

or if there is a way to bind parameters only once.

Answer 1

There is no need to modify your SQL like this. Your code basically comes down to this:

$stmt = $this->connection->prepare('SELECT count(*) FROM vocabulary WHERE  `type` = :type AND `lesson` = :lesson');
$stmt->execute($params);
$count = $stmt->fetchColumn();

$stmt = $this->connection->prepare('SELECT * FROM vocabulary WHERE  `type` = :type AND `lesson` = :lesson limit 100');
$stmt->execute($params);
$result = $stmt->fetchAll(PDO::FETCH_CLASS, $class);