多个条件使用OR
问题描述：
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);
}
我的SQL代码出现问题。 我需要针对输入是可选的不同列测试多个条件。 目前，除非两个输入字段都具有值，否则代码将不会执行。 我的代码如下： p>
if（（empty（$ fname）=== false或empty（$ lname）=== false）＆amp;＆amp; $ date1 == null＆amp;＆amp; $ date2 == null）
{
$ result = mysqli_query（$ con，“SELECT u.FirstName AS'First Name'，u.LastName AS'Last Name'，CAST（n.CreatedOn AS 日期）AS'日期'，s.SportName AS'Sport'，n.DocumentID AS'文件ID'，n.DocumentName AS'PDF'
来自用户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-＆gt; ID' ）AND
（u.FirstName LIKE'％$ fname％'或
u.LastName LIKE'％$ lname％'）
GROUP BY u.LastName
ORDER BY n.CreatedOn“）;
echo $ fname;
echo $ lname;
show_results（$ result）;
}
code> pre>
div>
答
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.