Building a query with if condition

[afridy]

Hi folks,

i am struggling to pass a parameter to a query for execution.

SELECT *
FROM visas_allocated
WHERE branch_id=:bid
AND client_id=:client_id
ORDER BY date,time DESC

one parameter need to be added only if the condition is true. i manage to do query part, but stucked at passing params .

$query->execute(array(':client_id' => $client_id
if($bid!="0"){
     ,':bid' => $bid));
}

[afridy]

another try , but getting parameter count error

$params="':client_id' => " . $client_id ;
if($bid!="0"){
     $params=$params . ",':bid' => " . $bid;
}    
$query->execute(array($params));

print $params shows
‘:client_id’ => 26,‘:bid’ => 31

but getting the

Fatal error: Uncaught exception ‘PDOException’ with message
‘SQLSTATE[HY093]: Invalid parameter number: number of bound variables
does not match number of tokens’

[SamA74]

@afridy

Invalid parameter number: number of bound variables does not match number of tokens’

They need to match, so you must build the query string using the same condition to include $bid in the query as well as the param array.

    $params = array('client_id' => $client_id) ;
    $sqlstr = "SELECT * FROM visas_allocated WHERE  client_id = :client_id " ;
    if($bid!="0"){
        $sqlstr .= "AND branch_id = :bid " ;
        $params['bid'] = $bid ;
    }
    $sqlstr .= "ORDER BY date,time DESC" ;
    $sql = prepare->$db($sqlstr) ;
    $sql->execute($params) ;

[afridy]

Thanks sam for the reply!!!
ill check it now.

[afridy]

Thank you once again SamA74,
I learned a lot from your example and my problem has been solved

//------------------------QUERY------------------------//    
$query="SELECT * 
        FROM visas_allocated 
        WHERE client_id = :client_id " ;    

$params = array('client_id' => $client_id) ;

//branch//
if($bid!="0"){
        $query .= "AND branch_id=:bid ";
        $params['bid']=$bid;
}    

//position//
if($pid!="0"){ 
   $query .= "AND position_id=:pid ";
   $params['pid']=$pid;
}

//country//
if($cid!="0"){
   $query .= " AND country=:cid ";
   $params['cid']=$cid;
}

//echo $query . "<br>";
//var_dump($params);

$query .= "ORDER BY date,time DESC";
$query = $db->prepare($query);
$query->execute($params) ;
//------------------------/QUERY------------------------//

[ahundiak]

Doing this sort of string manipulation can get to be messy as the queries get more involved. You might want to take a look at a query builder such as: http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/query-builder.html

Your code would look something like:

$queryBuilder = $conn->createQueryBuilder();
$queryBuilder
  ->select('*')
  ->from('visas_allocated');

$queryBuilder
  ->andWhere('client_id = :client_id')
  ->setParameter('client_id',$clientId);

if ($branchId) {
  $queryBuilder
    ->andWhere('branch_id = :branch_id')
    ->setParameter('branch_id',$branchId);
}
if ($countryId) {
  $queryBuilder
    ->andWhere('country_id = :country_id')
    ->setParameter('country_id',$countryId);
}
$queryBuilder->orderBy('date,time','DESC');

Using a query builder can be a bit more verbose but can make it easier to add additional clauses without worrying so much about the sql syntax as well as making it practical to share queries.

[afridy]

Hi ahundiak,

Awesome! Thank you for the introduction of query builder.