I'm using the following code to create a search function for my site using PHP/MYSQL.
This code will search for sizes and colors if the checkboxes are selected and will display them in on my page.
example:
checkbox 1 = small
checkbox 2 = large
checkbox 3 = xxlarge
checkbox 4 = red
checkbox 5 = black
etc etc ......
if the users select small and large as the size and red as the color, my PHP will return the products that have those credentials.
This is the PHP code:
<?php
error_reporting(-1);
ini_set('display_errors', 'On');
include "config/connect.php";
$searchList = "";
$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)){
##NOPE##$sql .= $clause."`".$c."` LIKE '%{$c}%'";
$sql .= $clause . " (ATTRIBUTES.sizes LIKE BINARY '$c' OR ATTRIBUTES.colors LIKE BINARY '$c')";
$clause = " OR ";//Change to OR after 1st WHERE
}
}
//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);
} 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.'';
}
}
}
}
}
?>
THE ISSUE THAT I HAVE:
if A product has small and large (both) as the credentials, and the users search for small and large, my PHP will return the same item twice.
how can I stop the php returning the same product twice in cases like above?
any advise would be appreciated.
I have used GROUP BY as suggested like so:
$sql="SELECT *
FROM `yt` GROUP BY yt.id
INNER JOIN `ATTRIBUTES` ON yt.id=ATTRIBUTES.id"
but that throws the following error:
ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN `ATTRIBUTES` ON yt.id=ATTRIBUTES.id WHERE (ATTRIBUTES.sizes LIKE BIN' at line 3
You need to use SQL "GROUP BY" function. If yt.id is your product id,that'd be "GROUP BY yt.id" appended to your query.
Try:
<?php
error_reporting(-1);
ini_set('display_errors', 'On');
include "config/connect.php";
$searchList = "";
$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)){
##NOPE##$sql .= $clause."`".$c."` LIKE '%{$c}%'";
$sql .= $clause . " (ATTRIBUTES.sizes LIKE BINARY '$c' OR ATTRIBUTES.colors LIKE BINARY '$c')";
$clause = " OR ";//Change to OR after 1st WHERE
}
}
//append "GROUP BY"
$sql .= " GROUP BY yt.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);
} 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.'';
}
}
}
}
}
?>