Multiple where conditions using OR

Question

I am having an issue with my SQL code. I need to test multiple conditions against different columns where the inputs are optional. Currently the code will not execute unless both input fields have values. My code is below:

        if((empty($fname) === false or empty($lname) === false) && $date1 == null && $date2 == null)
        {
        $result = mysqli_query($con,"SELECT u.FirstName AS 'First Name', u.LastName AS 'Last Name' , CAST(n.CreatedOn AS date) AS 'Date' , s.SportName AS 'Sport', n.DocumentID AS 'Document ID', n.DocumentName AS 'PDF'
            FROM users u 
            INNER JOIN ncaadocuments n
            ON u.UserID = n.UserID 
            INNER JOIN userathletes a 
            ON n.UserID = a.UserID
            INNER JOIN sport s
            ON a.SportID = s.SportID
            WHERE n.SchoolID = ('$current_user->ID') AND
            (u.FirstName LIKE '%$fname%' OR 
            u.LastName LIKE '%$lname%')
            GROUP BY u.LastName
            ORDER BY n.CreatedOn");
        echo $fname;
        echo $lname;
        show_results($result);

        }

Then why don't you write your code such that the condition is not appended when one of the fields is empty? — Nico Haase, Apr 16 '18 at 18:49
Possible duplicate of [Using group by on multiple columns](https://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns) — Andrey Kaplun, Apr 16 '18 at 19:25
this might help https://stackoverflow.com/questions/49031938/filtering-any-columns-of-a-database-table-client-t-sql-select-from-case-when-the — Andrey Kaplun, Apr 16 '18 at 19:30

score 0 · Accepted Answer · answered Apr 16 '18 at 18:55

One of the tricks I've used in the past is to write my where clauses like this:

WHERE (myColumn = @neededVal or @neededVal is null)
    and (secondColumn = @anotherVal or @anotherVal is null)

... you'll need to make sure you're not going to be taking a performance hit - sometimes the SQL engine doesn't do the best with this sort of formulation (IIRC.)

But this'll get you where you need to go. If the value is filled out, it'll filter down on the value; if it's not filled out, the condition will always be true.

Multiple where conditions using OR

1 Answers1