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("/\:…

Accepted Answer

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.

Use bound parameter multiple times

Solution

Problem

Related problems