Geek asks and answers

Ignore WHERE Special Criteria

I want to execute a parameterized query to perform a search on user parameters. There are quite a few options, and not all of them will be delivered all the time. How can I make a standard query that sets all possible parameters, but ignore some of these parameters if the user has not selected a meaningful parameter value?

Here is an imaginary example to illustrate what I will do

$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 parameters provided does not match the number of expected parameters. Do I have to process the request every time only the specified parameters are included in the WHERE clause, or is there a way that some of these parameters are ignored or always return true when checking?

+5
source share
4 answers
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 the parameters, set nullfor those that you do not need.

Please note that in order to be able to run the request in this way, emulation modefor PDO you need to turnON

+7
source

First, start by changing the line $sqlsimply:

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

WHERE 1 = 1 allows you to not include additional options ...

Next, selectively attach to your string $sqlany additional parameter that has a meaningful value:

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

Your line $sqlnow contains only the parameters that you plan to provide, so you can proceed as before:

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

If you cannot solve your problem by changing your request ... There are several libraries that help with the assembly of requests. I used Zend_Db_Selectin the past, but each structure probably 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
+1
source

I tested the solution given by @juergen, but it gives a PDOException as the number of related variables does not match. The following (not very elegant) code works regardless of any 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();
}

Application:

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

More articles:


All Articles