Binding several values in PDO SQL Statement (PDOStatement)

Sometimes we need to search a single value through several columns when building SQL instructions.

Suppose the table below

• [ENTITY_ONE]
  • ID
  • COLUMN_A
  • COLUMN_B
  • COLUMN_C
  • COLUMN_D
  • COLUMN_E

If we need search a single value on columns B,D and E we will need use the following instruction

SELECT ID, COLUMN_A,(...COLUMNS..), COLUMN_E 
FROM ENTITY_ONE WHERE COLUMN_B LIKE '%search_value%' OR COLUMN_D LIKE '%search_value%' OR COLUMN_E LIKE '%search_value%';

In PHP code we can do

$q = '%search_value%';
$sql = "SELECT * FROM ENTITY_ONE WHERE COLUMN_B LIKE '$q' OR COLUMN_D LIKE '$q' OR COLUMN_E LIKE '$q'; 
$pdo = new PDO($dsn, $user, $pass); 
$stmt = $pdo->query($sql);

Well, this can work but we know that isn’t the best approach. We need use Binding Values to avoid SQL injection and other malicious treats.

So, the code can be modified to

$q = '%search_value%'; 
$sql = "SELECT * FROM ENTITY_ONE WHERE COLUMN_B LIKE ? OR COLUMN_D LIKE ? OR COLUMN_E LIKE ?; 
$args = [$q, $q, $q]; // We need one entry for each "?" on instruction
$pdo = new PDO($dsn, $user, $pass); 
$stmt = $pdo->prepare($sql);
$stmt->execute($args);

Much better, but, when building complex SQL instruction, things can be confusing with lots of arguments and don’t forget: ORDER MATTERS.

Happily PDO can bind values in different order when using named bindings.

$q = '%search_value%'; 
$sql = "SELECT * FROM ENTITY_ONE WHERE COLUMN_B LIKE :first_arg OR COLUMN_D LIKE :second_arg OR COLUMN_E LIKE :third_arg; 

$pdo = new PDO($dsn, $user, $pass); 
$stmt = $pdo->prepare($sql); 
// One way
$args = [':first_arg' => $q, ':third_arg' =>$q, ':second_arg' => $q]; // We need one entry for each ":name" on instruction 
$stmt->execute($args); 
// Another way
$stmt->bindValue(':third_arg'. $q);
$stmt->bindValue(':first_arg', $q); 
$stmt->bindValue(':second_arg', $q);

$stmt->execute(); 

Hmm, seems that this isn’t good enough. We only change the use of 1-indexed placeholder to a :named placeholder. There’s no gain beyond of code readable and the possibility to bind in any order.

Yes, but now we can do the best approach when using one unique search term in several columns. We can use only one bind to one or more :named placeholders ’cause PDO is smart and clever. Look our final code here.

$q = '%search_value%'; 
$sql = "SELECT * FROM ENTITY_ONE WHERE COLUMN_B LIKE :unique_arg OR COLUMN_D LIKE :unique_arg OR COLUMN_E LIKE :unique_arg; 
$pdo = new PDO($dsn, $user, $pass); 
$stmt = $pdo->prepare($sql); 
// One way 
$args = [':unique_arg' => $q]; // We can bind all :name with only one term 
$stmt->execute($args); 
// Another way 
$stmt->bindValue(':unique_arg', $q); 
$stmt->execute(); 

Can save a lot of typing when writing many SQL instruction using same argument.