使用 PHP/MySQL 进行搜索过滤
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13206530/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Search Filtering with PHP/MySQL
提问by dxenaretionx
I'm trying to create a search/filtering option in my blood donor application. Where donor can be searched by sex, name, blood group or by selecting all three. Here is my code
我正在尝试在我的献血者应用程序中创建一个搜索/过滤选项。可以通过性别、姓名、血型或选择所有三个来搜索捐赠者的位置。这是我的代码
function search_donar($_POST) {
$by_name = $_POST['by_name'];
$by_sex = $_POST['by_sex'];
$by_group = $_POST['by_group'];
$by_level = $_POST['by_level'];
$search_query = "SELECT * FROM donar WHERE";
if($by_name !="") {
$search_query .= " name='$by_name'";
}
if($by_sex !="") {
$search_query .= " sex='$by_sex'";
}
if($by_group !="") {
$search_query .= " blood_group='$by_group'";
}
if($by_level !="") {
$search_query .= " e_level='$by_level'";
}
$search_query;
$result = mysql_query($search_query);
return $result;
}
And here is the html
这是 html
if(isset($_POST['submit'])) {
$retrived_result = $donar->search_donar($_POST);
}
<form action="" method="post">
<table width="100%" border="0" style="border:none;">
<tr>
<td><label>Name: </label><input type="text" name="by_name" /></td>
<td><label>Sex: </label><input type="text" name="by_sex" /></td>
<td><label>Blood Group: </label><input type="text" name="by_group" /></td>
<td><label>Level: </label><input type="text" name="by_level" /></td>
<td><input class="button" type="submit" name="submit" value="Search" /></td>
</tr>
</table>
</form>
Single filtering works very fine. But To filter with all I used AND , but it gives me error. Can anyone help ?
单次过滤效果很好。但是要过滤我使用的所有 AND ，但它给了我错误。任何人都可以帮忙吗？
Thanks in advance
提前致谢
回答by Rejinderi
Like all the other post you will need to append all the conditions with AND like so. This is the cleanest answer so far. Remember to real escape your strings though use the mysqli OOP way instead of the old mysql. Just a suggestion.
像所有其他帖子一样，您需要像这样用 AND 附加所有条件。这是迄今为止最干净的答案。尽管使用 mysqli OOP 方式而不是旧的 mysql，但请记住要真正转义您的字符串。只是一个建议。
Heres an example of a typical query.
这是一个典型查询的示例。
The correct way:
正确的方法：
SELECT * FROM donar WHERE name='dxenaretionx' AND sex='M';
The way you are doing it
你这样做的方式
SELECT * FROM donar WHERE name='dxenaretionx' sex='M';
Code:
代码：
function search_donar($_POST) {
$by_name = $_POST['by_name'];
$by_sex = $_POST['by_sex'];
$by_group = $_POST['by_group'];
$by_level = $_POST['by_level'];
//Do real escaping here
$query = "SELECT * FROM donar";
$conditions = array();
if(! empty($by_name)) {
$conditions[] = "name='$by_name'";
}
if(! empty($by_sex)) {
$conditions[] = "sex='$by_sex'";
}
if(! empty($by_group)) {
$conditions[] = "blood_group='$by_group'";
}
if(! empty($by_level)) {
$conditions[] = "e_level='$by_level'";
}
$sql = $query;
if (count($conditions) > 0) {
$sql .= " WHERE " . implode(' AND ', $conditions);
}
$result = mysql_query($sql);
return $result;
}
回答by John Dvorak
The following code snippet:
以下代码片段：
$search_query = "SELECT * FROM donar WHERE";
if($by_name !="") {
$search_query .= " name='$by_name'";
}
if($by_sex !="") {
$search_query .= " sex='$by_sex'";
}
produces queries like
产生查询，如
SELECT * FROM donar WHERE name='nowak' sex='m'
, which are not valid because there is no logical operator between the clauses. You need to add an 'AND'. To simplify code, you can generate conditions in the form of "true and a and b ...":
，这是无效的，因为子句之间没有逻辑运算符。您需要添加一个“AND”。为了简化代码，可以生成“true and a and b ...”形式的条件：
$search_query = "SELECT * FROM donar WHERE true";
if($by_name !="") {
$search_query .= " AND name='$by_name'";
}
if($by_sex !="") {
$search_query .= " AND sex='$by_sex'";
}
...
回答by Er. Anurag Jain
There in Your code there is problem in query where condition . Here your query will be like
select * from donar where by_name = "A" by_group = "N"there is No And/Orto make where condition properly. Please try code like given below.
在您的代码中，查询 where 条件存在问题。在这里，您的查询就像
select * from donar where by_name = "A" by_group = "N"没有And/Or正确生成 where 条件一样。请尝试下面给出的代码。
$search_query = "SELECT * FROM donar";
$query_cond = "";
if($by_name !="") {
$query_cond .= " name='$by_name'";
}
if($by_sex !="") {
if(!empty($query_cond)){
$query_cond .= " AND ";
}
$query_cond .= " sex='$by_sex'";
}
if($by_group !="") {
if(!empty($query_cond)){
$query_cond .= " AND ";
}
$query_cond .= " blood_group='$by_group'";
}
if($by_level !="") {
if(!empty($query_cond)){
$query_cond .= " OR ";
}
$query_cond .= " e_level='$by_level'";
}
if(!empty($query_cond)){
$query_cond = " Where ".$query_cond;
$search_query.$query_cond;
}
Here in code First we take $query_condvariable empty and make condition according code. and manage ANDoperator according that. And in last if We found $query_condnot empty then add it to $select_query.
在代码中首先我们将$query_cond变量设为空并根据代码设置条件。并AND据此管理运营商。最后，如果我们发现$query_cond不为空，则将其添加到$select_query.
I hope it will be helpful for you.
我希望它会对你有所帮助。
thanks
谢谢
回答by Arif
In this where you don't use validation, it is recomended not to use whether Field is EMPTY or NOT. Try below code, Hope it will work
在不使用验证的情况下，建议不要使用 Field 是否为 EMPTY 或 NOT。试试下面的代码，希望它会起作用
function search_donar($_POST) {
$by_name = $_POST['by_name'];
$by_sex = $_POST['by_sex'];
$by_group = $_POST['by_group'];
$by_level = $_POST['by_level'];
$search_query = "SELECT * FROM donar WHERE name LIKE '%$by_name%' AND sex LIKE '%$by_sex%' AND blood_group LIKE '%$by_group%' AND e_level LIKE '%$by_level%' ";
$result = mysql_query($search_query);
return $result;
}
回答by Prasath Albert
Try like this:
像这样尝试：
function search_donar($_POST) {
$by_name = $_POST['by_name'];
$by_sex = $_POST['by_sex'];
$by_group = $_POST['by_group'];
$by_level = $_POST['by_level'];
$isfirst=0;
$search_query = "SELECT * FROM donar WHERE";
if($by_name !="") {
$search_query .= " name='$by_name'";
$isfirst=1;
}
if($by_sex !="") {
if($isfirst!=0)
$search_query .= " AND ";
$search_query .= " sex='$by_sex'";
$isfirst=1;
}
if($by_group !="") {
if($isfirst!=0)
$search_query .= " AND ";
$search_query .= " blood_group='$by_group'";
$isfirst=1;
}
if($by_level !="") {
if($isfirst!=0)
$search_query .= " AND ";
$search_query .= " e_level='$by_level'";
$isfirst=1;
}
$result = mysql_query($search_query);
return $result;
}
回答by kashif iqbal
function search_donar($_POST) {
$by_name = $_POST['by_name'];
$by_sex = $_POST['by_sex'];
$by_group = $_POST['by_group'];
$by_level = $_POST['by_level'];
$search_query = "SELECT * FROM donar WHERE";
$and = '';
if(count($_POST) > 1) {
$and ='AND';
}
if($_POST[0]) {
$and ='';
}
if($by_name !="") {
$search_query .= $and." name='$by_name'";
}
if($by_sex !="") {
$search_query .= $and." sex='$by_sex'";
}
if($by_group !="") {
$search_query .= $and." blood_group='$by_group'";
}
if($by_level !="") {
$search_query .= $and." e_level='$by_level'";
}
if(count($_POST) == 0) {
$search_query .= " 1 ";
}
$search_query;
$result = mysql_query($search_query);
return $result;
}
回答by Sreenath
I'd do in this way.
我会这样做。
function search_donar($_POST) {
$by_name = $_POST['by_name'];
$by_sex = $_POST['by_sex'];
$by_group = $_POST['by_group'];
$by_level = $_POST['by_level'];
$search_query = "SELECT * FROM donar WHERE 1 = 1";
if($by_name !="") {
$search_query .= " AND name='$by_name'";
}
if($by_sex !="") {
$search_query .= " AND sex='$by_sex'";
}
if($by_group !="") {
$search_query .= " AND blood_group='$by_group'";
}
if($by_level !="") {
$search_query .= " AND e_level='$by_level'";
}
$result = mysql_query($search_query);
return $result;
}
