我如何使用$row结果到mysqli_query?
$result1 = mysqli_query($connect,"SELECT subcat_ID FROM subcategories WHERE cat_ID=$cat_ID");
$row=mysqli_fetch_array($result1);
$result2 = mysqli_query($connect,"SELECT subsubcat_name FROM subsubcategories WHERE subcat_ID='".$row['subcat_ID']."'");
while ($row = mysqli_fetch_array($result2)){
if (isset($row)){
echo $row['subsubcat_name'];
echo "<br>";
}
我没有得到任何错误,但我无法打印它。
知道我做错了什么吗?
谢谢你的帮助!
==================================================================================
1日编辑
因为我的问题还没有解决,所以我要更深入地解释一下:
我的数据库:—类别:cat_ID、cat_name—子分类:subcat_ID、cat_ID、extra_cat_ID、subcat_name—subsubcategories: subsubcat_ID、subcat_ID、subsubcat_name
在某一点上,我通过url获得cat_ID (categories)的值。我想做的是,无论cat_id的类别是相同的值的cat_id或extra_cat_ID像在子类别(我也希望能够打印subcat_name),其中子类别的subcat_ID是相同的subcat_ID的子子类->打印subsubcat_name。
Categories
-------------------------
cat_ID | cat_name
------------------
4 | Baby & Kids
5 | Bicycles
6 | Boats
7 | Books & Comics
....
13 | Clothes & Accessories
....
35 | Sport & Fitness
36 | Study
....
38 | Toys & Games
....
Subcategories
-------------------------
subcat_ID | cat_ID | extra_cat_ID | subcat_name
------------------------------------------------
....
15 | 4 | 13 | Baby clothes
16 | 4 | 0 | Baby products
17 | 4 | 13 | Kids clothes
18 | 4 | 38 | Toys
19 | 5 | 0 | Bycicles
20 | 5 | 0 | Bycicle gear & Accessories
21 | 6 | 0 | Boat parts
22 | 6 | 0 | Other Boats
23 | 6 | 0 | Power Boats
24 | 6 | 0 | Sailboats
25 | 6 | 35 | Windsurf & Surfing
26 | 7 | 0 | Antiquarian
27 | 7 | 0 | Books
28 | 7 | 38 | Childrens books
29 | 7 | 0 | Comics
30 | 7 | 0 | Magazines & Newspapers
31 | 7 | 36 | Study & Training
Subsubcategories
-------------------------
subsubcat_ID | subcat_ID | subsubcat_name
-----------------------------------------
...
470 | 15 | Baptism outfits
471 | 15 | Bibs
472 | 15 | Body warmers
473 | 15 | Bodysuits
....
496 | 16 | Baby bath
497 | 16 | Baby books
498 | 16 | Baby inserts
499 | 16 | Baby monitors
....
548 | 17 | Belts
549 | 17 | Blouses & Shirts
550 | 17 | Body warmer
551 | 17 | Boots
....
....
740 | 26 | Music
741 | 26 | Navy
742 | 26 | Novel
743 | 26 | Photography
....
....
867 | 30 | Animals
868 | 30 | Arts and Culture
869 | 30 | Branch
870 | 30 | Cars
870 | 30 | Computers
....
....
etc.
与其运行两个查询,不如使用一个JOIN
.
例如$query
:
SELECT subcategories.cat_ID, subsubcategories.subsubcat_name
FROM subcategories
INNER JOIN subsubcategories
ON subcategories.cat_ID=subsubcategories.subcat_ID
WHERE subcategories.cat_ID = ?
在这种情况下准备查询更合适/安全:
/* create a prepared statement */
if ($result = mysqli_prepare($connect, $query)) {
/* bind parameters for markers */
mysqli_stmt_bind_param($result, "i", $cat_ID);
/* execute query */
mysqli_stmt_execute($result);
/* fetch data */
while ($row = mysqli_fetch_array($result)){
if (isset($row)){
echo $row['subsubcat_name'];
echo "<br>";
}
}
}
DEMO for SQL JOIN
让它工作。这是我使用的代码:
$result=mysqli_query($connect,"SELECT subcategories.cat_ID, subsubcategories.subsubcat_name FROM subcategories INNER JOIN subsubcategories ON subcategories.subcat_ID=subsubcategories.subcat_ID WHERE subcategories.cat_ID = $cat_ID OR subcategories.extra_cat_ID = $cat_ID");
while ($row = mysqli_fetch_array($result)){
echo $row['subsubcat_name'];
echo "<br>";
}
虽然不确定这是安全的方法。欢迎提出建议。
感谢media向我展示了另一种方法,尽管它不是完全相同的代码。
下面是一个关于如何使用mysqli
的示例面向对象<?php
$connect= new mysqli("localhost","user","passwd","database");
if ($connect->connect_errno){
echo "could not connect";
}
$select = "SELECT * FROM tablename";
if($result = $connect->query($select)){
while($row = $result->fetch_object()){
echo $row->rowname."<br>";
}
}
else { echo 'no result'; }
$connect->close();
?>
程序风格
<?php
$connect= mysqli_connect("localhost","user","passwd","database");
if (mysqli_connect_errno()){
echo "could not connect";
}
$select = "SELECT * FROM tablename";
if($result = mysqli_query($connect,$select)){
while($row = mysqli_fetch_object($result)){
echo $row->rowname."<br>";
}
}
else { echo 'no result'; }
mysqli_close($connect);
?>