search mysql using checkboxs?

Question

I have been working on this for hours to no avail.

I'm trying to create an advanced search feature for my site using PHP and mysql.

the advanced search only has checkboxes in it.

the checkboxes look like this:

my MYSQL database looks like this:

products table:

id  product_name  price  
5     shirt        20
6     shoe         70

I have an attributes table where i store the colors and size of the products.

this table looks like this:

idc  id  product_name  colors   sizes 
1     5     shirt        red     
2     5     shirt        back
3     5     shirt        white
4     5     shirt                small
5     5     shirt                medium
6     5     shirt                Large
7     6     shoe         brown
8     6     shoe         black
9     6     shoe                   5 
10    6     shoe                   6
11    6     shoe                   7

in my search.php page, I have this code :

include "config/connect.php"; 
$searchList = "";


foreach($_POST['keyword'] as $c){
$sql ="SELECT *
FROM `yt`
INNER JOIN `ATTRIBUTES` ON yt.id= ATTRIBUTES.id
WHERE (
     ATTRIBUTES.size LIKE '%".$c."%' OR
     ATTRIBUTES.color LIKE '%".$c."%'
      )";
$query = mysqli_query($db_conx, $sql);
}

//var_dump($query);

$productCount = mysqli_num_rows($query);
$i=0; // count the output amount
if ($productCount > 0) {
    while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){
             $id = $row["id"];
             $product_name = $row["product_name"];
             $searchList .= ''.$product_name.'';
    }
}

the code above doesn't return anything and I'm keep getting this error:

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given

which means there is something gone wrong after my sql query!

so I did var_dump($query); and that gave me bool(fales).

not much of a help as that means the same thing as the first error!

so could someone please help me out with this issue?

any advise and help would be appreciated.

EDIT:

I've changed my code to this:

 0) {
    while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){
             $id = $row["id"];
             $product_name = $row["product_name"];
             $searchList .= ''.$product_name.'';
    }
}
?>

but the code above gives me the following errors:

Notice: Undefined variable: query in on line 23 
Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, null given on line 23

am I in the right direction?

EDIT:

THIS CODE RETURNS THE DATA FROM MYSQL DATABASE BUT THE INFORMATION IS WRONG!

$clause = " WHERE ";//Initial clause
$sql="SELECT *
FROM `yt`
INNER JOIN `ATTRIBUTES` ON yt.id=ATTRIBUTES.id";//Query stub
if(isset($_POST['submit'])){
    if(isset($_POST['keyword'])){
        foreach($_POST['keyword'] as $c){
            if(!empty($c)){
                $sql .= $clause."`sizes` LIKE '%{$c}%'";
                $clause = " OR ";//Change  to OR after 1st WHERE
            }
        }
        // Run query outside of foreach loop so it only runs one time.
        $query = mysqli_query($db_conx, $sql);
        // Check that the query ran fine.
        if (!$query) {
            print "ERROR: " . mysqli_error($db_conx);
        } else {
            // Use $query inside this section to make sure $query exists.
            $productCount = mysqli_num_rows($query);
            $i=0; // count the output amount
            if ($productCount > 0) {
                while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){
                   $id = $row["id"];
                   $product_name = $row["product_name"];
                   $searchList .= ''.$product_name.'';
                }
            }
        }
    }
}

Blah Argh · Accepted Answer

Try changing it to this and see what you get:

"; //<-- 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);
        } else {
            // Use $query inside this section to make sure $query exists.
            $productCount = mysqli_num_rows($query);
            $i=0; // count the output amount
            if ($productCount > 0) {
                while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){
                   $id = $row["id"];
                   $product_name = $row["product_name"];
                   $searchList .= ''.$product_name.'';
                }
            }
        }
    }
}
?>

Edited: Fixed wrong column names. Something like that, perhaps?

search mysql using checkboxs?

Answers (1)

Related Questions