I'm converting a script that previously used mysql_ into PDO, using prepared statement whether possible. With old school mysql_, it was easy to do this kind of dynamic query using mysql_real_escape_string and concatenate all queries together into one long query.

Seems like it's a lot harder with PDO's prepared statement. I'm strugling at this dynamic query in this script below.

<?php
$filter_day=$_GET['filter_day'];
$filter_month=$_GET['filter_month'];
$filter_year=$_GET['filter_year'];
$search=$_GET['search'];

try{
  $db=new PDO("mysql:host=localhost;dbname=dbname;charset=utf8", 'dbuser', 'dbpassword');
  $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
}catch(PDOException $pe){
  die("Could not connect to the database dbname :" . $pe->getMessage());
}

$datecondition='';
if(strlen($filter_day)>0){ $datecondition.=" AND DAY(registration_date)=:filter_day ";}
if(strlen($filter_month)>0){ $datecondition.=" AND MONTH(registration_date)=:filter_month ";}
if(strlen($filter_year)>0){ $datecondition.=" AND YEAR(registration_date)=:filter_year ";}

$searchcondition='';
if(strlen($search)>2){ $searchcondition.=" AND (first_name LIKE :search OR last_name LIKE :search OR department LIKE :search OR position LIKE :search) "; }

$param=array('filter_day'=>$filter_day, 'filter_month'=>$filter_month, 'filter_year'=>$filter_year, 'search'=>"%$search%");
$q=$db->prepare("SELECT * FROM Users WHERE user_status='ENABLED' $datecondition $searchcondition LIMIT 0,30");
$q->execute($param);

?>

Case1: All parameters are included in final query.

The :search parameter only gets binded once, leaving the rest :searches still there in the final query.

SELECT * 
FROM Users 
WHERE user_status='ENABLED' 
AND DAY(registration_date)=:filter_day AND MONTH(registration_date)=:filter_month AND YEAR(registration_date)=:filter_year 
AND (first_name LIKE :search OR last_name LIKE :search OR department LIKE :search OR position LIKE :search) 
LIMIT 0,30

Case2: Not all parameters are included in final query.

SELECT * 
FROM Users 
WHERE user_status='ENABLED' 
AND (first_name LIKE :search OR last_name LIKE :search OR department LIKE :search OR position LIKE :search) 
LIMIT 0,30

Above query do only search for certain strings, but doesn't filter any date. However, the unused :filter_day :filter_month and :filter_year are included in the binding array. It can not be executed and return this error message:

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

I may put some more if/elses into the code to get away with this, but I realize that it will be too many codes just for only 1 query.

This isn't the optimal process/query, but I have no idea how to tackle it. Please help me optimize it or provide some examples. Thanks a lot!