Use Bound Parameter Multiple Times

Use bound parameter multiple times

I have ran over the same problem a couple of times now and I think i have found a pretty simple and good solution. In case i want to use parameters multiple times, I just store them to a MySQL User-Defined Variable.

This makes the code much more readable and you don't need any additional functions in PHP:

$sql = "SET @term = :term";

try
{
    $stmt = $dbh->prepare($sql);
    $stmt->bindValue(":term", "%$term%", PDO::PARAM_STR);
    $stmt->execute();
}
catch(PDOException $e)
{
    // error handling
}


$sql = "SELECT ... FROM table WHERE name LIKE @term OR number LIKE @term";

try
{
    $stmt = $dbh->prepare($sql);
    $stmt->execute();
    $stmt->fetchAll();
}
catch(PDOException $e)
{
    //error handling
}

The only downside might be that you need to do an additional MySQL query - but imho it's totally worth it.

Since User-Defined Variables are session-bound in MySQL there is also no need to worry about the variable @term causing side-effects in multi-user environments.

PHP pdo: Invalid Parameter Number when using a parameter twice?

Alternatively, you could change your settings to PDO::ATTR_EMULATE_PREPARES => true. This will allow you to bind the same named parameter multiple times by preparing the statements in PDO itself, rather than on the MySQL server.

Assigning the same parameter value multiple times in pdo execute

Using PDO you have the ability to use named parameters, however in your question you want to use 1 parameters for multiple values and that means emulation has to be on:

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

Now you can do the following:

$stmt = $db->prepare("SELECT * FROM table WHERE userid = :userid AND userid = :userid");

$stmt->excecute([
  ':userid' => 1
]);

Resulting in:

"SELECT * FROM table WHERE userid = 1 AND userid = 1"

PDO Parameterized Query - Reuse named placeholders?

PDO::prepare states that "you cannot use a named parameter marker of the same name twice in a prepared statement", so I guess that's a no then.

PDO: Using same bind variable multiple times

Problem was the quotes around the :status. Removed quotes and all is good.

Using one parameter multiple times in prepared mysqli-statement

Just to close the question:

The answer is no.

If you want to bind a parameter only one time and using it multiple times in a query you have to use PDO and this maybe also needs a special configuration.

But there seems to be more reasons to use PDO instead of mysqli, according to this great answer or this.

But sure there are workarounds. See the other answers to this question.

mysql - MySQL Prepared stamement bind same value multiple times using one variable

No, you can not. You should realize, that formal definition isn't same as actuall call. When you're defining your statement, you're defining placeholders and, while using it, all claimed parameters must be passes - no matter if real value is same for all of them (or for some of them).

MySQL has no idea how you will use your statement, either it will be same value when calling or not. Therefore, the only right way is to recount all your parameters during that call. That is - how it's intended to work.

---

Related Topics