PHP: Search mysql database using multiple select dropdown lists?

Question

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

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

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.

This is my code:

$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);

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.

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?

any help would be appreciated.

EDIT: 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);
        }

Shadow · Accepted Answer · 2015-10-05 11:43:15Z

1

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

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.

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.

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

...ATTRIBUTES.attr LIKE BINARY '$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%'

Plus some protection from sql injection would also be nice.

EDIT2: 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.

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.

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.

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).

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

edited Oct 5, 2015 at 11:43

answered Oct 5, 2015 at 10:18

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

H.HISTORY Over a year ago

When i change the "OR" to "AND" my entire code stops working and its not showing any results from the mysql!

Shadow Over a year ago

Stops working and not showing results are two entirely different things! Stops working means that the code is broken and produces an aeeror message, while not showing any results means (in this particular case) that no records satisfied the selection criteria. Can you pls be specific which happens after you make the change?

H.HISTORY Over a year ago

Yes, sure. when i change the OR to AND, i get no results at all (nothing). and I get no errors on my page either!

Shadow Over a year ago

If it stops working, then pls provide the error message. If it only returns an empty resultset, then I would like to see what's in $_POST['keyword']. My suspicion is that it contains just words like "computer". Which would mean that when you use the like operator, there are no wildcards used, so your query will only match if all keywords exactly match the attr field, which is unlikely

H.HISTORY Over a year ago

Please view my edit. also, I get no errors as i mentioned before. And yes, the value of keywords are like computers, apples, t-shirts etc etc...

|

Collectives™ on Stack Overflow

PHP: Search mysql database using multiple select dropdown lists?

1 Answer 1

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related