filter on multiple columns with a combination of LIKE and =

Question

I am trying to search from a table of employees from a form with 5 fields. The user can use any or all of the search fields with the following rules:

USERFORM (html/php)
EID: (textbox, exact match)
First name: (textbox, LIKE %fname%)
Last Name: (textbox, LIKE %lname%)
Email: (textbox, LIKE %email%)
Department: (dropdown, exact match)(includes a default value for not selecting amongst <options>)

The problem I am having is that when I search for firstname = %j%, it correctly filters to all employees with a firstname containing a j. However whenever I combine this with another or more search parameters it brings the maximum amount of results, rather than filtered minimum, for example firstname %j%, lastname, %b%. Will still return Joe bloggs, and joe coggs.

Same as if I were to add a department on there, I would like it to give me people with a j in their first name, a b in their last name who are a member of department 1. But instead then, it would give me all employees who are in the department regardless of the name search parameters, aswell as employees who are in another department bu have a j in their name.

And here is the query and other code:

    $eid = $_POST['eid'];
    $p_fname = $_POST['fname'];
    $p_lname = $_POST['lname'];
    $p_email = $_POST['email'];
    $depid = $_POST['depid'];
        if(empty($_POST['fname'])) { $fname = "";} else { $fname = "%$p_fname%"; }
        if(empty($_POST['lname'])) { $lname = "";} else { $lname = "%$p_lname%"; }
        if(empty($_POST['email'])) { $email = "";} else { $email = "%$p_email%"; }
    

    $query = $db->prepare("SELECT `employees`.`eid`, `employees`.`fname`, `employees`.`lname`, `employees`.`dob`, `employees`.`email`, `departments`.`depname` 
        FROM `employees` 
        INNER JOIN `departments` 
        ON `employees`.`depid` = `departments`.`id` 
        WHERE 
            `eid` = :eid || 
            `fname` LIKE :fname || 
            `lname` LIKE :lname || 
            `email` LIKE :email || 
            `depid` = :depid 
        ORDER BY `employees`.`eid` ASC 
        LIMIT :start_from, 10");
    $query->bindParam(":eid", $eid);
    $query->bindParam(":fname", $fname);
    $query->bindParam(":lname", $lname);
    $query->bindParam(":email", $email);
    $query->bindParam(":depid", $depid);
    $query->bindParam(":start_from", $start_from);
    $query->execute();

When I think about it I get why it would return the maximum results, however I have tried to change the OR to AND and used parentheses I get no results at all, because the the query is trying to return records that have a blank value in the columns which aren't defined.

I have also tried having AND for the exact values and OR for the wildcard values and this returns all results.

I really don't want to write a php script which has to define which columns to use and execute a different query for each scenario.

Please read my edit. I have already tried to use AND instead of OR with parantheses and without. This returned 0 records, presumably because it was trying to filter for columns which don't have a value when that column isn't selected. I also tried changing the parameter value to `%` if it was not used in the search, along with using AND. and this has also not iven me the result I need. — Adam Copley, Dec 12 '15 at 15:47

mickmackusa · Answer 1 · 2022-11-07T22:17:35.537

I recommend not adding needless logic to the WHERE clause. Build the dynamic statement and deliver the variables into the execute() call <- this is one of the very handy things about PDO.

$conditions = [];
$params = [];
if (!empty($_POST['eid']) {
    $conditions[] = "eid = ?";
    $params[] = $_POST['eid'];
}
if (!empty($_POST['fname']) {
    $conditions[] = "fname LIKE ?";
    $params[] = "%{$_POST['fname']}%";
}
if (!empty($_POST['lname']) {
    $conditions[] = "lname LIKE ?";
    $params[] = "%{$_POST['lname']}%";
}
if (!empty($_POST['email']) {
    $conditions[] = "email LIKE ?";
    $params[] = "%{$_POST['email']}%";
}
if (!empty($_POST['depid']) {
    $conditions[] = "depid = ?";
    $params[] = $_POST['depid'];
}
$params[] = $start_from;

Then apply the conditions to the sql string, and execute the prepared statement with the values bound to the placeholders.

$sql = "SELECT employees.eid,
               employees.fname,
               employees.lname,
               employees.dob,
               employees.email,
               departments.depname
        FROM employees 
        INNER JOIN departments ON employees.depid = departments.id
        " . ($conditions ? 'WHERE ' . implode(" AND ", $conditions) : '') . "
        ORDER BY employees.eid 
        LIMIT ?, 10";
$query = $db->prepare($sql);
$query->execute($params);

Here is a similar answer without the battery of conditionals:

Build SELECT query with dynamic number of LIKE conditions as a mysqli prepared statement

Adam Copley · Accepted Answer · 2015-12-12T19:05:24.397

I solved the issue by using LIKE and not = as the operator for, and swapping OR for AND as mentioned in the mentioned in the comment on the question.

The reason I had to use LIKE was because when the field is not in use from the form, it has to be a wildcard, and so = isn't suitable.

$p_eid = $_POST['eid'];
    $p_fname = $_POST['fname'];
    $p_lname = $_POST['lname'];
    $p_email = $_POST['email'];
    $p_depid = $_POST['depid'];
        // wildcard added when field not in use, but removed when in use
        if(empty($_POST['eid'])) { $eid = "%%";} else { $eid = "$p_eid"; }
        if(empty($_POST['fname'])) { $fname = "%%";} else { $fname = "%$p_fname%"; }
        if(empty($_POST['lname'])) { $lname = "%%";} else { $lname = "%$p_lname%"; }
        if(empty($_POST['email'])) { $email = "%%";} else { $email = "%$p_email%"; }
        // wildcard added when field not in use, bu removed when in use
        if(empty($_POST['depid'])) { $depid = "%%";} else { $depid = "$p_depid"; }

    $query = $db->prepare("SELECT `employees`.`eid`, `employees`.`fname`, `employees`.`lname`, `employees`.`dob`, `employees`.`email`, `departments`.`depname` 
        FROM `employees` 
        INNER JOIN `departments` 
        ON `employees`.`depid` = `departments`.`id` 
        WHERE 
            `eid` LIKE :eid AND    
            `fname` LIKE :fname AND  
            `lname` LIKE :lname AND  
            `email` LIKE :email AND  
            `depid` LIKE :depid
        ORDER BY `employees`.`eid` ASC 
        LIMIT :start_from, 10");
    $query->bindParam(":eid", $eid);
    $query->bindParam(":fname", $fname);
    $query->bindParam(":lname", $lname);
    $query->bindParam(":email", $email);
    $query->bindParam(":depid", $depid);
    $query->bindParam(":start_from", $start_from);
    $query->execute();

filter on multiple columns with a combination of LIKE and =

2 Answers2