Ignore particular WHERE criteria

Posted on

Ignore particular WHERE criteria – Here in this article, we will share some of the most common and frequently asked about PHP problem in programming with detailed answers and code samples. There’s nothing quite so frustrating as being faced with PHP errors and being unable to figure out what is preventing your website from functioning as it should like php and mysql . If you have an existing PHP-based website or application that is experiencing performance issues, let’s get thinking about Ignore particular WHERE criteria.

I want to execute a parameterized query to perform a search by user-supplied parameters. There are quite a few parameters and not all of them are going to be supplied all the time. How can I make a standard query that specifies all possible parameters, but ignore some of these parameters if the user didn’t choose a meaningful parameter value?

Here’s an imaginary example to illustrate what I’m going for

$sql = 'SELECT * FROM people WHERE first_name = :first_name AND last_name = :last_name AND age = :age AND sex = :sex';
$query = $db->prepare($sql);
$query->execute(array(':first_name' => 'John', ':age' => '27');

Obviously, this will not work because the number of provided parameters does not match the number of expected parameters. Do I have to craft the query every time with only the specified parameters being included in the WHERE clause, or is there a way to get some of these parameters to be ignored or always return true when checked?

Solution :

SELECT * FROM people 
WHERE (first_name = :first_name or :first_name is null)
AND (last_name = :last_name or :last_name is null)
AND (age = :age or :age is null)
AND (sex = :sex or :sex is null)

When passing parameters, supply null for the ones you don’t need.

Note that to be able to run a query this way, emulation mode for PDO have to be turned ON

First, start by changing your $sql string to simply:

$sql = 'SELECT * FROM people WHERE 1 = 1';

The WHERE 1 = 1 will allow you to not include any additional parameters…

Next, selectively concatenate to your $sql string any additional parameter that has a meaningful value:

$sql .= ' AND first_name = :first_name'
$sql .= ' AND age = :age'

Your $sql string now only contains the parameters that you plan on providing, so you can proceed as before:

$query = $db->prepare($sql);
$query->execute(array(':first_name' => 'John', ':age' => '27');

If you can’t solve your problem by changing your query… There are several libraries that help with assembling queries. I’ve used Zend_Db_Select in the past but every framework likely has something similar:

$select = new Zend_Db_Select;

$select->from('people');

if (!empty($lastName)) {
  $select->where('lastname = ?', $lastname);
}

$select->order('lastname desc')->limit(10);

echo $select; // SELECT * FROM people WHERE lastname = '...' ORDER BY lastname desc LIMIT 10

I’ve tested the solution given by @juergen but it gives a PDOException since number of bound variables does not match. The following (not so elegant) code works regardless of no of parameters:

function searchPeople( $inputArr )
{
  $allowed = array(':first_name'=>'first_name', ':last_name'=>'last_name', ':age'=>'age', ':sex'=>'sex');

  $sql  = 'SELECT * FROM sf_guard_user WHERE 1 = 1';

  foreach($allowed AS $key => $val)
  {
      if( array_key_exists( $key, $inputArr ) ){
          $sql .= ' AND '. $val .' = '. $key;
      }
  }

  $query = $db->prepare( $sql );
  $query->execute( $inputArr );
  return $query->fetchAll();
}

Usage:

$result = searchPeople(array(':first_name' => 'John', ':age' => '27'));

Leave a Reply

Your email address will not be published. Required fields are marked *