也许我有一个思维障碍,但我似乎无法让AND子句在这个查询中正常工作。我只想返回tbl_mod_Lessons.Approved=1的结果。
$sql = "SELECT *,
(SELECT AVG(intRating) FROM tbl_mod_LessonReviews LR WHERE LR.intLessonID = LS.intLessonID) rating,
(SELECT strContentArea FROM tbl_mod_ContentAreas CA WHERE CA.intCAID = LS.intContentAreaID) strContentArea
FROM tbl_mod_Lessons LS
WHERE
(LS.strTitle LIKE $search_string) OR
(LS.strStandards LIKE $search_string) OR
(LS.strDescription LIKE $search_string) OR
(LS.strActivities LIKE $search_string) OR
EXISTS(SELECT strContentArea FROM tbl_mod_ContentAreas CA WHERE CA.intCAID = LS.intContentAreaID AND (CA.strContentArea LIKE $search_string))
AND (LS.Approved = 1)
ORDER BY ".$pager->getOrder("LS.intLessonID DESC")."
LIMIT ".$pager->PageLimits();
正如您所看到的,iv试图将其添加到中,这不会破坏查询,而是会返回Approved=0的结果。
试试下面的一个。我在你的OR子句周围加了一些括号。
$sql = "SELECT *,
(SELECT AVG(intRating) FROM tbl_mod_LessonReviews LR WHERE LR.intLessonID = LS.intLessonID) rating,
(SELECT strContentArea FROM tbl_mod_ContentAreas CA WHERE CA.intCAID = LS.intContentAreaID) strContentArea
FROM tbl_mod_Lessons LS
WHERE (
(LS.strTitle LIKE $search_string) OR
(LS.strStandards LIKE $search_string) OR
(LS.strDescription LIKE $search_string) OR
(LS.strActivities LIKE $search_string) OR
EXISTS(SELECT strContentArea FROM tbl_mod_ContentAreas CA WHERE CA.intCAID = LS.intContentAreaID AND (CA.strContentArea LIKE $search_string))
)
AND (LS.Approved = 1)
ORDER BY ".$pager->getOrder("LS.intLessonID DESC")."
LIMIT ".$pager->PageLimits();
您需要在所有OR条件周围添加这样的括号,以将它们与AND 分开
WHERE
((LS.strTitle LIKE $search_string) OR
(LS.strStandards LIKE $search_string) OR
(LS.strDescription LIKE $search_string) OR
(LS.strActivities LIKE $search_string) OR
EXISTS(SELECT strContentArea FROM tbl_mod_ContentAreas CA
WHERE CA.intCAID = LS.intContentAreaID
AND (CA.strContentArea LIKE $search_string)))
AND (LS.Approved = 1)
用( )
包围所有其他or
子句。否则,您只能AND
最后一个子句。