Use bound parameter multiple times

Question

I'm trying to implement a pretty basic search engine for my database where the user may include different kinds of information. The search itself consists of a couple of a union selects where the results are always merged into 3 columns.

The returning data however is being fetched from different tables.

Each query uses $term for matchmaking, and I've bound it to ":term" as a prepared parameter.

Now, the manual says:

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name twice in a prepared statement.

I figured that instead of replacing each :term parameter with :termX (x for term = n++) there must be a be a better solution?

Or do I just have to bind X number of :termX?

Edit Posting my solution to this:

$query = "SELECT ... FROM table WHERE name LIKE :term OR number LIKE :term";  $term = "hello world"; $termX = 0; $query = preg_replace_callback("/\:term/", function ($matches) use (&$termX) { $termX++; return $matches[0] . ($termX - 1); }, $query);  $pdo->prepare($query);  for ($i = 0; $i < $termX; $i++)     $pdo->bindValue(":term$i", "%$term%", PDO::PARAM_STR); 

Alright, here is a sample. I don't have time for sqlfiddle but I will add one later if it is necessary.

(     SELECT         t1.`name` AS resultText     FROM table1 AS t1     WHERE         t1.parent = :userID         AND         (             t1.`name` LIKE :term             OR             t1.`number` LIKE :term             AND             t1.`status` = :flagStatus         ) ) UNION (     SELECT         t2.`name` AS resultText     FROM table2 AS t2     WHERE         t2.parent = :userParentID         AND         (             t2.`name` LIKE :term             OR             t2.`ticket` LIKE :term             AND             t1.`state` = :flagTicket         ) ) 

Answer 1

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.