我正在运行引导选项卡，在第二个选项卡上，我想显示该类别中的所有其他产品（大约15条记录），下面的查询返回1条包含类别ID的记录。
类别ID在产品中可用，但不确定如何编写第二个查询以返回这些记录。
我会像一样把它回声出来
echo "<p>Part #: " . $row["partNo"]. " - Name: " . $row["prodName"]. "</p>";
任何帮助都是好的。
表格结构
tbl产品
- id
- partNo
- prodName
- 类别ID_FK
tbl类别
- id
- catName
products.php
<?php include "db_conn.php"; ?>
<?php include "inc_products.php"; ?>
<div class="container">
<h2><?php echo $prodName; ?></h2>
<ul class="nav nav-tabs">
<li><a data-toggle="tab" href="#home">Details</a></li>
<li><a data-toggle="tab" href="#menu1">Categories</a></li>
</ul>
<div class="tab-content">
<div id="home" class="tab-pane fade in active">
<h3>Product # <?php echo $partNo; ?></h3>
<p>Description: <?php echo $prodDesc; ?></p>
<p>Category: <?php echo $catName; ?></p>
<p>Cost: $<?php echo $unitPrice; ?></p>
<p>Active: <?php echo ($discontinued == 0) ? "Yes" : "No"; ?></p>
</div>
<div id="menu1" class="tab-pane fade">
<h3>Menu 1</h3>
<?php echo $outp; ?>
</div>
<div id="menu2" class="tab-pane fade">
<h3>Menu 2</h3>
<p> </p>
</div>
<div id="menu3" class="tab-pane fade">
<h3>Menu 3</h3>
<p> </p>
</div>
</div>
</div>
inc_products.php
<?php
$partNoid = intval($_GET['partNo']);
$query = "Select Distinct
*
From
tblProducts Inner Join
tblCategories On tblCategories.id = tblProducts.categoryID_FK Inner Join
tblSuppliers On tblSuppliers.id = tblProducts.supplierID_FK Inner Join
users On users.id = tblProducts.userID_FK
Where partNo = $partNoid";
$result = $conn->query($query);
if($result === false) {
trigger_error('Wrong SQL: ' . $query . ' Error: ' . $conn->error, E_USER_ERROR);
} else {
$result->data_seek(0);
if($rs = $result->fetch_array(MYSQLI_ASSOC)) {
$partNo = $rs["partNo"];
$prodName = $rs["prodName"];
$prodDesc = $rs["prodDesc"];
$unitPrice = $rs["unitPrice"];
$catName = $rs["catName"];
$discontinued = $rs["discontinued"];
$catID = $rs["categoryID_FK"];
}
}
$result->free();
$query2 = "Select
*
From
tblProducts Inner Join
tblCategories On tblCategories.id = tblProducts.categoryID_FK
Where $partNoid AND tblProducts.categoryID_FK = $catID";
$result2 = $conn->query($query2);
if($result2 === false) {
trigger_error('Wrong SQL: ' . $query2 . ' Error: ' . $conn->error, E_USER_ERROR);
} else {
$result2->data_seek(0);
while($rs2 = $result2->fetch_array(MYSQLI_ASSOC)) {
$outp .= $rs2["partNo"] ;
$outp .= ' - ' . $rs2["prodName"] . '<br>';
}
}
$result2->free();
$conn->close();
？>
根据原始查询，如果您想从该行获取类别id，并通过第二个查询选择该类别的所有其他产品，请尝试以下操作：
$secondQuery = "Select Distinct
*
From
tblProducts Inner Join
tblCategories On tblCategories.id = tblProducts.categoryID_FK
Where partNo = $partNoid AND tblProducts.catName = $catName";
//this is the same query as before, but now with a filter for category
$allCategoryResults = $conn->query($secondQuery);
//Then just loop through the results, as suggested by RamRaider in the comments.
while($tmp = $allCategoryResults->fetch_assoc()){
//do whatever with your results
}
更好的方法是在查询中使用类别id而不是类别名称，但如果您愿意，可以很容易地将它们交换掉。