Use bound parameter multiple times

by

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.

More Related Contents:

  1. How to apply bindValue method in LIMIT clause?
  2. How to properly set up a PDO connection
  3. Getting raw SQL query string from PDO prepared statements
  4. PDO with INSERT INTO through prepared statements [closed]
  5. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  6. Do SQL connections opened with PDO in PHP have to be closed
  7. PDO and MySQL ‘between’
  8. PHP PDO and Mysql [closed]
  9. PDO Fetch statement only retrieves first column
  10. Using PHP data in MySQL [closed]
  11. Checking the availability of rooms
  12. PHP PDO prepared statements
  13. PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not?
  14. PDO binding values for MySQL IN statement [duplicate]
  15. Why I am getting Cannot pass parameter 2 by reference error when I am using bindParam with a constant value?
  16. Loading .sql files from within PHP
  17. mysql PDO how to bind LIKE
  18. implement LIKE query in PDO
  19. PHP PDO prepared statement — MySQL LIKE query
  20. Fetching single row, single column with PDO
  21. PDO Connection Test
  22. How do I loop through a MySQL query via PDO in PHP?
  23. Laravel 5.4 on PHP 7.0: PDO Exception – Could not find driver (MySQL)
  24. Using LIKE in bindParam for a MySQL PDO Query [duplicate]
  25. How to handle PDO exceptions [duplicate]
  26. MySql PHP select count of distinct values from comma separated data (tags)
  27. php artisan migrate throwing [PDO Exception] Could not find driver – Using Laravel
  28. PDO and MySQL Fulltext searches
  29. how do I get month from date in mysql
  30. Aggregated query without GROUP BY

Leave a Comment