我将合并MySQL查询,以便用户可以选择多个下拉列表并根据这些下拉列表进行过滤。正如我使用$ echo查询所检查的那样,这可以按预期工作。但是,除第一个单词外,我需要在单词“ WHERE”的每次出现之前插入一个“ AND”。现在,如果我选择了多个下放,我的查询就会这样显示:
SELECT a.MembershipID,
a.FirstName,
a.Surname,
t.RaceID,
t.Time,
r.RaceID,
r.RaceName,
r.Distance,
r.ClubYear
FROM Athlete AS a
INNER JOIN Time AS t ON a.MembershipID=t.MembershipID
INNER JOIN Race AS r ON t.RaceID=r.RaceID
WHERE r.RaceName= 'Parkrun' WHERE r.Distance= '5 kilometres'
ORDER BY a.Surname
正如您在第4行看到的那样,有两个没有AND的WHERE查询。如何在第二个WHERE之前插入AND?除了第一个以后,我如何才能在随后的所有WHERE中执行此操作?我考虑过使用explode来创建子字符串,插入AND,然后将它们重新连接在一起,但这似乎有点过头了。有什么帮助吗?谢谢。
编辑:添加生成上面的PHP代码:
$query = "SELECT a.MembershipID, a.FirstName, a.Surname, t.RaceID, t.Time, r.RaceID, r.RaceName, r.Distance, r.ClubYear
FROM Athlete AS a
INNER JOIN Time AS t
ON a.MembershipID=t.MembershipID
INNER JOIN Race AS r
ON t.RaceID=r.RaceID";
if ($_POST['raceName']!= null) {
$raceName = mysqli_real_escape_string($mysqli, $_POST["raceName"]);
$filter = " WHERE r.RaceName= '$raceName'";
$query .= $filter;
}
if ($_POST['athlete']!= null) {
$athlete = mysqli_real_escape_string($mysqli, $_POST["athlete"]);
$firstWord = explode(' ',trim($athlete));
$firstName = $firstWord[0];
$lastWord = explode(' ', $athlete);
$surname = array_pop($lastWord);
$filter = " WHERE a.FirstName= '$firstName' AND a.Surname = '$surname'";
$query .= $filter;
}
if ($_POST['distance']!= null) {
$distance = mysqli_real_escape_string($mysqli, $_POST["distance"]);
$filter = " WHERE r.Distance= '$distance'";
$query .= $filter;
}
if ($_POST['clubYear']!= null) {
$clubYear = mysqli_real_escape_string($mysqli, $_POST["clubYear"]);
$filter = " WHERE r.ClubYear= '$clubYear'";
$query .= $filter;
}
$fullquery = $query . " ORDER BY a.Surname";
一个简单的解决办法是先添加一个总是真实的陈述一样1=1
,所以你不必检查,如果它是第一个语句或没有,然后继续通过追加建设你查询AND CONDITION_N
相应
$query = "SELECT a.MembershipID, a.FirstName, a.Surname, t.RaceID, t.Time, r.RaceID, r.RaceName, r.Distance, r.ClubYear
FROM Athlete AS a
INNER JOIN Time AS t
ON a.MembershipID=t.MembershipID
INNER JOIN Race AS r
ON t.RaceID=r.RaceID";
$query .= " WHERE 1 = 1 ";
if ($_POST['raceName']!= null) {
$raceName = mysqli_real_escape_string($mysqli, $_POST["raceName"]);
$filter = " AND r.RaceName= '$raceName'";
$query .= $filter;
}
解释:
if
条件为真,则您的查询将是您遵循的初始查询的任何内容,与最初没有WHERE 1=1
该查询的情况完全相同如果您的条件是true
:
INITIAL_QUERY WHERE 1=1 AND CONDITION_N
对于多个条件为真的情况,它将是:
INITIAL_QUERY WHERE 1=1 AND CONDITION_N AND CONDITION_N+1
这使您的PHP代码更加整洁和快速,因为您不必检查是否是第一个条件来决定是否使用它WHERE
或AND
何时将其附加到select语句上。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句