PDO是否可以使命名占位符匹配所有内容？

Is it possible with PHP PDO to use named placeholders that effectively match everything?

Take as example: $sql = "SELECT * FROM users WHERE targetGroup = :targetGroup AND userId = :userId"

And take $statement = $this->dbh->prepare($sql);

Can I then somehow bind values such that I get the following behaviours:

    • Original query (this can be done obviously).
    • Parameters: :targetGroup = 1, :userId = 5.
    • Resulting query: $sql = "SELECT * FROM users WHERE targetGroup = 1 AND userId = 5

  2. A query to show all users

    • Parameters: Somehow set :targetGroup and :userId to some special value ALL.
    • Resulting query: $sql = "SELECT * FROM users

I would like to use this such that I can define queries once somewhere in my program, as static strings, and then reuse them to my likings.

You can't do that. You'll have to prepare different statements for different queries.

$findOneStmt = $pdo->prepare("SELECT * FROM user WHERE target_group = :tg AND user_id = :uid");
$findAllStmt = $pdo->prepare("SELECT * FROM users");

Later, when you'll need one of this queries just call:

$findOneStmt->execute(array(':tg' => 123, ':uid' => 321));
$user = $findOneStmt->fetch();

// ...

$findAllStmt->execute();
$users = $findAllStmt->fetchAll();

You could put a conditional in your SQL that lets you show everything. For example:

SELECT *
FROM users
WHERE
    1 = :showAll OR (
        targetGroup = :targetGroup
        AND userId = :userId
    )

Then you can set the :showAll to 0 when you want to filter by :targetGroup and :userId or to 1 when you want to ignore :targetGroup and :userId.

I believe best practice however would be to have two separate queries.

The problem is that you're essentially performing two entirely different queries. They may look very similar, but a SELECT * with no where clause is quite different to one with a where clause.

Rather than define SQL queries in one place and then reuse, abstract the functionality out and use that method call as and when needed, it'll provide you with the ability to have the query defined once, but you will have two queries there now. This way is much safer and much better terms of readability and usage.