PHP:使用多个选择下拉列表搜索mysql数据库? [英] PHP: Search mysql database using multiple select dropdown lists?

问题描述

我正在尝试使用页面上的多个下拉列表搜索MYSQL数据库.

I'm trying to search MYSQL database using multiple Dropdown lists on my page.

但是，此搜索功能略有改动.

However, there is a small twist in this search function.

基本上，我需要确保 All 条件(所有多个select dropdonw值)均与项目匹配，如果它们确实匹配，则显示结果！

Basically, I need to make sure All the criteria (all the multiple select dropdonw values) match the items and if they do match then show the result!

此刻，即使其中一个下拉值与我要执行的操作都不匹配，我的代码也会显示结果.

At the moment, my code shows the results even if one of the dropdown values match the items which is not what i am trying to do.

这是我的代码:

$searchList = "";
$clause = " WHERE ";//Initial clause
$sql="SELECT *
FROM `product_details`
INNER JOIN `ATTRIBUTES` ON product_details.id=ATTRIBUTES.id";//Query stub
    if(isset($_POST['keyword']) && !empty($_POST['keyword'])){

        foreach($_POST['keyword'] as $c){
            if(!empty($c)){
            $currentproduct = $_POST['product'];
            $cat = $_POST['cat'];
                ##NOPE##$sql .= $clause."`".$c."` LIKE '%{$c}%'";
                $sql .= $clause . " (ATTRIBUTES.attr LIKE BINARY '$c') AND ATTRIBUTES.sub_cat_name='$currentproduct'";
               $clause = " OR ";//Change  to OR after 1st WHERE
            }
        }
        $sql .= " GROUP BY product_details.id";
        //print "SQL Query: $sql<br />"; //<-- Debug SQl syntax.
        // Run query outside of foreach loop so it only runs one time.
        $query = mysqli_query($db_conx, $sql);

我什至尝试删除isset并做了if(!empty($_POST['keyword'])){，但即使下拉列表值之一与项目凭据匹配，我仍然会得到结果.

I even tried to remove the isset and did if(!empty($_POST['keyword'])){ but i still get results even if one of the dropdown lists values match the items credentials.

我不确定我在做什么错，因为我认为使用if(!empty($_POST['keyword'])){应该可以解决此问题，但事实并非如此.

I'm not sure what I am doing wrong here as I thought using if(!empty($_POST['keyword'])){ should solve this issue but it hasn't.

有人可以就这个问题提供建议吗?

Could someone please advise on this issue?

任何帮助将不胜感激.

我将代码更改为以下内容，但它什么也不显示:

I changed the CODE to the following and it doesn't display anything:

$clause = " WHERE ";//Initial clause
$sql="SELECT *
FROM `product_details`
INNER JOIN `ATTRIBUTES` ON product_details.id=ATTRIBUTES.id";//Query stub
            $currentproduct = $_POST['product'];
            $cat = $_POST['cat'];
            if(!empty($_POST['keyword'])){

        foreach($_POST['keyword'] as $c){
            if(!empty($c)){

                ##NOPE##$sql .= $clause."`".$c."` LIKE '%{$c}%'";
                $sql .= $clause . " (ATTRIBUTES.attr LIKE BINARY '$c') AND ATTRIBUTES.sub_cat_name='$currentproduct'";
               $clause = " AND ";//Change  to OR after 1st WHERE
            }
        }
        $sql .= " GROUP BY product_details.id";
        //print "SQL Query: $sql<br />"; //<-- Debug SQl syntax.
        // Run query outside of foreach loop so it only runs one time.
        $query = mysqli_query($db_conx, $sql);
        //var_dump($query); //<-- Debug query results.
        // Check that the query ran fine.
       if (!$query) {
            print "ERROR: " . mysqli_error($db_conx);
        }

推荐答案

$clause = " OR ";//Change  to OR after 1st WHERE

上面的OR运算符将使您的where条件选择一条记录，即使1个关键字与attr字段匹配.将其更改为"AND"，以期望所有关键字都适用.

The above OR operator will cause your where criteria to select a record even if 1 keyword matches the attr field. Change it to " AND " to expect all keywords to apply.

此外，... AND ATTRIBUTES.sub_cat_name ='$ currentproduct'条件似乎适用于所有关键字，因此应仅在循环的每次迭代中添加一次此条件. '];行也应在循环的前面移动.

Furthermore, ... AND ATTRIBUTES.sub_cat_name='$currentproduct'" criterion seems to apply to all keywords, so this criterion should be added once, not at every iteration of the loop. $currentproduct = $_POST['product']; row should also be moved in fron of the loop.

以反映将操作者更改为AND而没有返回任何行的情况.

to reflect to changing the opreator to AND and not having any rows returned.

...ATTRIBUTES.attr LIKE BINARY '$c'...

如果$ c中没有通配符，则上述条件将要求单词与attr字段匹配，就好像使用了=运算符一样，这​​不太可能发生.搜索中必须包含通配符:'％$ c％'

If there are no wildcards in $c, then the above criterion will require the word to match the attr field as if = operator had been used, which is unlikely to happen. Wildcards must be included in the search: '%$c%'

再加上一些防止sql注入的保护，也将很不错.

Plus some protection from sql injection would also be nice.

如果每个属性都存储在其自己的记录中，那么会使事情变得有些复杂，因为where条件是根据单个记录而不是它们的集合来评估的.

If each attribue is stored in its own record, then it complicates things a little bit, since the where criteria is evaluated against a single record, not a collection of them.

我将为您提供一个示例选择命令，但您必须将其合并到您的php代码中.

I'll give you a sample select command, but you will have to incorporate it into your php code.

select product_details.* FROM product_details INNER JOIN
    (select product_details.id, count(ATTRIBUTES.id) as total
     FROM `product_details`
     INNER JOIN `ATTRIBUTES` ON product_details.id=ATTRIBUTES.id
     WHERE ATTRIBUTES.attr in (...)
     GROUP BY product_details.id
     HAVING total=...) as t
on t.id=product_details.id

子查询计算产品匹配的属性数量，并消除计数不等于通过表单提交的参数数量的属性.外部查询会获取计数匹配的那些产品的产品详细信息.

The subquery counts how many attributes were matched for a product and eliminates those, where the count does not equal to the number of parameters submitted via the form. The outer query gets the product details for those, where the count matched.

对于in()子句中的...，您需要提供一个逗号分隔的，包含关键字的列表，例如:'computer'，'apple'".在php中使用implode()函数并进行串联以获取结果.

For the ... in the in() clause you need to provide a comma separated, ' enclosed list of the keywords, like: "'computer', 'apple'". Use implode() function in php and sztring concatenation to get the results.

对于……中的...子句，用$ _POST ['keyword']数组中的关键字数代替(不过，您应该在代码中检查它是数组还是单个值).

For the ... in the having clause substitute the number of keywords in the $_POST['keyword'] array (you should check in the code if it's an array or just a single value, though).

仍然，您应该考虑sql注入对代码的影响.

Still, you should consider the impact of sql injection on your code.

这篇关于PHP:使用多个选择下拉列表搜索mysql数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！