Building a query with if condition

Hi folks,

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

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
     ,':bid' => $bid));

another try , but getting parameter count error

$params="':client_id' => " . $client_id ;
     $params=$params . ",':bid' => " . $bid;

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’

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 " ;
        $sqlstr .= "AND branch_id = :bid " ;
        $params['bid'] = $bid ;
    $sqlstr .= "ORDER BY date,time DESC" ;
    $sql = prepare->$db($sqlstr) ;
    $sql->execute($params) ;
1 Like

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

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

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

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

        $query .= "AND branch_id=:bid ";

   $query .= "AND position_id=:pid ";

   $query .= " AND country=:cid ";

//echo $query . "<br>";

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

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:

Your code would look something like:

$queryBuilder = $conn->createQueryBuilder();

  ->andWhere('client_id = :client_id')

if ($branchId) {
    ->andWhere('branch_id = :branch_id')
if ($countryId) {
    ->andWhere('country_id = :country_id')

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.


Hi ahundiak,

Awesome! Thank you for the introduction of query builder.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.