如何在单击类别时显示产品，从数据库中列出类别

Question

我不知道如何显示所选类别的产品。以及如何在该页面上使用有限的产品来做到这一点，可以单击下一个或上一个以查看同一页面中的产品。

这是我数据库中的表：

类别

category_id
category_name category_description

产品

product_id 类型

product_name
product_price 图像

product_description product_date

<?php
// show categories list
$connect = mysqli_connect("localhost", "root", "", "test");
$q = mysqli_query($connect,"select * from category ORDER BY category_name 
ASC");
while ($res = mysqli_fetch_assoc($q))
{
echo '<li> <a href="counter.php?category_id='. $res['category_id'] 
.'">'.$res['category_name'].'</a></li>';
}
?>

<?php
    if(isset($_GET['category_id'])){
    $cat_id = $_GET['category_id'];
        $conn = mysqli_connect("localhost", "root", "", "test");
        $query = "SELECT product_id, product_name, product_price, image FROM product WHERE type = '$cat_id' ORDER BY product_id ASC ";
        $result = mysqli_query($conn,$query);
        $line = mysqli_fetch_array($result);
        if (!$line) echo '';
        $previd = -1;
        $currid = $line[0];
        if (isset($_GET['id'])) {
                $previous_ids = array();
            do {
                $previous_ids[] = $line[0];
                $currid = $line[0];
                if ($currid == $_GET['id']) break;
                $previd = end($previous_ids);
                $line = mysqli_fetch_array($result);
            } while ($line);
        }

        if ($previd > -1){
            echo '<a href="counter.php?cat_id='.$cat_id.'&amp;id='.$previd.'" class="prev_pic"><span>Prev</span></a>';
            echo str_repeat('&nbsp;', 5);
            $line = mysqli_fetch_array($result);
            $query = "SELECT product_id, product_name, product_price, image FROM product WHERE type = '$cat_id' ORDER BY product_id ASC RAND() LIMIT 1";
            $result = mysqli_query($connect, $query);
            while ($row = mysqli_fetch_array($result)){
                echo '<a href="counter.php?cat_id='.$cat_id.'&amp;id='.$row['id'].'"class="random">Random</a>';
            }
            echo str_repeat('&nbsp;', 5);
            if ($line) echo '<a href="counter.php?cat_id='.$cat_id.'&amp;id='.$line[0].'" class="next_pic"><span>Next</span> </a><br /><br />';
                echo "</div>\r";
            }
        if(mysqli_num_rows($result) > 0) {
            while ($row = mysqli_fetch_array($result)) {
                ?>
                <div class="col-md-3">
                    <form method="post" action="counter.php?action=add&id=<?php echo $row["product_id"]; ?>">
                        <div class="product">
                            <img src="img/<?php echo $row["image"]; ?>" style="width:100px; height:100px">
                            <h5 class="text-info"><?php echo $row["product_name"]; ?></h5>
                            <h5 class="text-danger"><?php echo "RM " . $row["product_price"]; ?></h5>
                            <input type="text" name="quantity" class="form-control" value="1">
                            <input type="hidden" name="hidden_name" value="<?php echo $row["product_name"]; ?>">
                            <input type="hidden" name="hidden_price" value="<?php echo $row["product_price"]; ?>">
                            <input type="submit" name="add" style="margin-top: 5px;" class="btn btn-success" value="+">
                            <input type="submit" name="minus" style="margin-top: 5px;" class="btn btn-success" value="-">
                        </div>
                    </form>
                </div>
                <?php
            }
        }
    }
    ?> 

预期结果应在单击类别列表时显示产品。但是现在实际结果不是显示产品，它是空的。

Answer 1

交换

<a href="counter.php?product_id='. $res['category_id'] 
.'">

为

<a href="counter.php?category_id='. $res['category_id'] 
.'">

获取与恢复

Answer 2

似乎您有两个问题。首先，您尝试遍历相同的结果集。这是不可能的。一旦你循环它，你这样做的方式，结果就会被抛弃。可以使用此方法将所有结果作为 ASSOC 数组返回，然后可以多次循环此数组。

$query = "SELECT product_id, product_name, product_price, image FROM product WHERE type = ? ORDER BY product_id ASC";
$stmt = mysqli_prepare($conn, $query);
$stmt->bind_param('i', $cat_id);
$stmt->execute();
$result = $stmt->get_result();
$rows = $result->fetch_all(MYSQLI_ASSOC);
foreach ($rows as $key => $value) {
...
}
foreach ($rows as $key => $value) {
...
}

其次，您似乎想要对产品的结果进行分页。您可以为此使用 LIMIT。我会传递一个名为 page 的参数，然后使用它来过滤有限的结果。如果您希望每页 20 个结果，则限制查询将如下所示。

$numPerPage = 20;
$pageNum = isset($_GET['p']) ? preg_replace('/[^0-9]/', '', $_GET['p']) : 1;
# Get Count
$query = "SELECT product_id, product_name, product_price, image FROM product WHERE type = ? ORDER BY product_id ASC";
$stmt = mysqli_prepare($conn, $query);
$stmt->bind_param('i', $cat_id);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_row();
$count = array_shift($row);
$lastPage = ceil($count/$numPerPage);
if ($lastPage < 1) {
  $lastPage = 1;
}
# Check to make sure user isn't higher than set pages
if ($pageNum < 1) { 
    $pageNum = 1; 
} else if ($pageNum > $lastPage) { 
    $pageNum = $lastPage; 
}
# Get Results
$query = "SELECT product_id, product_name, product_price, image FROM product WHERE type = ? ORDER BY product_id ASC";
$limit = ' LIMIT ' .($pageNum - 1) * $numPerPage .',' .$numPerPage;
$stmt = mysqli_prepare($conn, $query . $limit);
$stmt->bind_param('i', $cat_id);
$stmt->execute();
$result = $stmt->get_result();
$rows = $result->fetch_all(MYSQLI_ASSOC);
foreach ($rows as $key => $value) {
...
}
foreach ($rows as $key => $value) {
...
}

我希望这有助于解决您遇到的问题。