php - 使用复选框搜索 mysql？-6ren

php - 使用复选框搜索 mysql？

转载作者：行者123 更新时间：2023-11-29 12:23:16

我已经为此工作了几个小时，但毫无结果。

我正在尝试使用 PHP 和 mysql 为我的网站创建高级搜索功能。

高级搜索中只有复选框。

复选框如下所示:

<form action="search.php"  method="POST" id="myForm"/>

<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td>&nbsp;</td>
    <td><input type="checkbox" name="keyword[]"  value="small"/></td>
    <td>Small</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td><input type="checkbox" name="keyword[]"  value="large"/></td>
    <td>Large</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td><input type="checkbox" name="keyword[]"  value="xlarge"/></td>
    <td>X-Large</td>
  </tr>
    <tr>
    <td>&nbsp;</td>
    <td><input type="checkbox" name="keyword[]"  value="xxlarge"/></td>
    <td>XX-Large</td>
  </tr>

  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
      <tr>
    <td>&nbsp;</td>
    <td><input type="checkbox" name="keyword[]"  value="red"/></td>
    <td>Red</td>
  </tr>
   <tr>
    <td>&nbsp;</td>
    <td><input type="checkbox" name="keyword[]"  value="black"/></td>
    <td>Black</td>
  </tr>
     <tr>
    <td>&nbsp;</td>
    <td><input type="checkbox" name="keyword[]"  value="white"/></td>
    <td>White</td>
  </tr>
</table>

<input type="submit" value="submit" />
</form>

我的 MYSQL 数据库如下所示:

产品表:

id  product_name  price  
5     shirt        20
6     shoe         70

我有一个属性表，用于存储产品的颜色和尺寸。

该表如下所示:

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

在我的 search.php 页面中，我有以下代码:

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

上面的代码没有返回任何内容，并且我不断收到此错误:

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

这意味着我的sql查询后出了问题!

所以我做了var_dump($query);，这给了我bool(fales)。

没什么帮助，因为这与第一个错误的含义相同!

有人可以帮我解决这个问题吗？

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

编辑:

我已将代码更改为:

<?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)){
                $sql .= $clause."`".$c."` LIKE '%{$c}%'";
                $clause = " OR ";//Change  to OR after 1st WHERE
            }   $query = mysqli_query($db_conx, $sql);
        }
    }
//echo $sql;//Remove after testing
}
//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.'';
    }
}
?>
<?php echo $searchList; ?>

但是上面的代码给了我以下错误:

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

我的方向正确吗？

编辑:

此代码从 MYSQL 数据库返回数据，但信息错误!

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

最佳答案

尝试将其更改为这样，看看您会得到什么:

<?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 '%$c%' OR ATTRIBUTES.colors LIKE '%$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 echo $searchList; ?>