多个条件使用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);

        }

Answer 1

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.