我有这个代码,我不知道如何排序我的查询
function get_All_Categories(){
//new instance of mysqli
$mysqli = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME);
//if it doesn't work give an error
if (!$mysqli) {
die('There was a problem connecting to the database.');
}
$queryCats ="SELECT Catid, Catname
FROM ProductCats
ORDER BY Catid";
$querySubCats ="SELECT Subcatname, Parentid
FROM ProductSubCats, ProductCats
WHERE ProductSubCats.Parentid = ProductCats.Catid
ORDER BY Subcatname;";
if ($catResults = $mysqli->query($queryCats)){
if (!$catResults) {
echo 'Could not run query: ' . mysql_error();
exit;
}
else{
$subCatResults = $mysqli->query($querySubCats);
}
while ($rows = $catResults->fetch_assoc()) {
echo '<div class = "categoryCluster">';
echo '<a href="../index.php?Cat='.$rows["Catname"].'">'.$rows["Catname"].'</a>';
echo '<br>';
while($rowsSub = $subCatResults->fetch_assoc()){
echo '<div class="subCategoryCluster">';
echo ' '.'<a href="../index.php?Cat='.$rows["Catname"].'&'.'Subcat='.$rowsSub["Subcatname"].'">'.$rowsSub["Subcatname"].'</a>';
echo '<br>';
echo '</div>';
}
echo '</div>';
}
}
$mysqli->close();
}
显示此输出:
Header 1
Subcat 1
Subcat 2
OtherSubcat 1
Header 2
Header 3
Header 4
Header 5
Header 6
但我的问题是subcat 1和subcat 2的parentid为1,这与头1一致,而其他subcat1的parentid为5。它实际上符合标题5。
是的,在我的循环中,它显示在其他2个之后。
现在我知道为什么这是错误的,因为while循环中的while循环命令它最后出现,并将其粘贴在那里。
但我完全不知道如何修改它,使其显示:
Header 1
Subcat 1
Subcat 2
Header 2
Header 3
Header 4
Header 5
OtherSubcat 1
Header 6
示例表:
> ProductSubCats
> Subcatid | Subcatname | Parentid
> 1 | Subcat 1 | 1
> 2 | Subcat 2 | 1
> 3 | Othersubcat 3 | 5
>
>
>
> ProductCats
> Cat id | Catname
> 1 | Header 1
> 2 | Header 2
> 3 | Header 3
> 4 | Header 4
> 5 | Header 5
> 6 | Header 6
谢谢你的帮助
最佳答案
为了你所要做的,按照帕沙比茨所说的去做。
请尝试以下操作:
$categories = array();
$sub_categories = array();
while ($rows = $catResults->fetch_assoc()) {
$categories[$rows['Catid']] = $rows['Catname'];
}
while($rows = $subCatResults->fetch_assoc()){
if (!isset($sub_categories[$rows['Parentid']])) {
$sub_categories[$rows['Parentid']] = array();
}
$sub_categories[$rows['Parentid']][] = $rows['Subcatname'];
}
foreach ($categories as $cat_id => $name) {
echo '<div class = "categoryCluster">';
echo '<a href="../index.php?Cat='.$name.'">'.$name.'</a>';
echo '<br>';
if (isset($sub_categories[$cat_id])) {
foreach($sub_categories[$cat_id] as $cat_id => $sub_cat_name) {
echo '<div class="subCategoryCluster">';
echo ' '.'<a href="../index.php?Cat='.$name.'&'.'Subcat='.$sub_cat_name.'">'.$sub_cat_name.'</a>';
echo '<br>';
}
}
}
这应该能帮你解决这个问题,研究代码,这样你就能理解这里发生了什么。