PHP:避免显示同一产品两次？

Question

我使用以下代码通过 PHP/MYSQL 为我的网站创建搜索功能。

如果选中复选框，此代码将搜索尺寸和颜色并将其显示在我的页面上。

示例:

checkbox 1 = small
checkbox 2 = large
checkbox 3 = xxlarge
checkbox 4 = red
checkbox 5 = black
etc etc ......

如果用户选择小号和大号作为尺寸，红色作为颜色，我的 PHP 将返回具有这些凭据的产品。

这是 PHP 代码:

<?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.'';
                }
            }
        }
    }
}
?> 

我遇到的问题:

如果产品有小和大(两者)作为凭据，并且用户搜索小和大，我的 PHP 将返回相同的项目两次。

在上述情况下，如何阻止 php 返回同一产品两次？

如有任何建议，我们将不胜感激。

我已经按照建议使用了 GROUP BY，如下所示:

$sql="SELECT *
FROM `yt` GROUP BY yt.id
INNER JOIN `ATTRIBUTES` ON yt.id=ATTRIBUTES.id"

但这会引发以下错误:

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

Answer 1

您需要使用SQL“GROUP BY”函数。如果 yt.id 是您的产品 ID，则会将“GROUP BY yt.id”附加到您的查询中。

尝试:

<?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.'';
                }
            }
        }
    }
}
?>