Build dynamic WHERE clause in mySQL

问题:

I have this code and it works great, if I just want to search by office name. However I need to be able to search by "Office and/or First Name and/or Last Name", any combination of the three.

$firstName      = $_POST["firstName"];
$lastName       = $_POST["lastName"];
$officeName     = $_POST ["officeName"];

$query = "SELECT 
e.*,
e.id emp_id,
o.*
";
$query .= "FROM 
employee_data e,
office o,
employee_office_pivot p
"; 
$query .= "WHERE 
1=1
AND e.id=p.employee_id
AND p.office_id=o.id
AND o.office_name= '".$officeName."'
";

How can I build the WHERE clause, so that it will accept any of the three columns, or none if they are null.

Thanks, Richard

回答1:

Something like this?

$query .= "WHERE 
    1=1
    AND e.id=p.employee_id
    AND p.office_id=o.id
    AND (o.office_name= '".mysqli_real_escape_string($officeName)."'
        OR o.office_name= '".mysqli_real_escape_string($firstName)."'
        OR o.office_name= '".mysqli_real_escape_string($lastName)."')
    ";

I used mysqli_real_escape_string() here as an example, you should use the correct and necessary precautions to avoid SQL injection in your system.

回答2:

You can use arrays to dynamically construct your SQL:

/**
 *  The items you expect to receive from $_POST. I prefer defining these ahead of time - when feasible - 
 *  so that you can reference them without worrying about throwing an error if they are not set.
 */
$options = array_fill_keys(array('firstName', 'lastName', 'officeName'), false);
$post = array_merge($options, $_POST);
/**
 *  Your base SQL query.
 */
$sql = 'SELECT ...columns... FROM ...tables... WHERE 1 = 1';
$where = array();
/**
 *  If $_POST items are present, sanitize and create SQL
 */
if ( $post['firstName'] ) {
    $where[] = "employee_first_name = '".mysqli_real_escape_string($post['firstName'])."'";
}
if ( $post['lastName'] ) {
    $where[] = "employee_last_name = '".mysqli_real_escape_string($post['lastName'])."'";
}
if ( $post['officeName'] ) {
    $where[] = "office_name = '".mysqli_real_escape_string($post['officeName'])."'";
}
/**
 *  One or more $_POST items were found, so add them to the query
 */
if ( sizeof($where) > 0 ) {
    $sql .= ' AND '.implode(' AND ', $where);   
}

You can use the same technique to dynamically add columns, joined tables, etc. to the SQL. (Hint: build the entire SQL statement using an array.) You can also very easily modify this to use combinations of AND and OR.

回答3:

$values = array(
    'firstName'  => 'someFirstName',
    'lastName'   => 'someLastName',
    'officeName' => 'someOfficeName'
);

foreach( $values as $col => $val )
{
    $where .= "$key = '$balue' ";
}

Though this is SQL injection vulnerable.