>我试图使用 mysql_fetch_row 获取计数值,但回显是 Array9,而我只想要 9。
<?php
$result = mysql_query("SELECT * FROM `product_category` ORDER BY `product_category_name`");
while($row = mysql_fetch_array($result)) {
echo '
<li class="ui-shadow" data-count-theme="d">
<a href="' . $row['product_category_ref_page'] . '.php" data-icon="arrow-r" data-iconpos="right">' . $row['product_category_name'] . '</a><span class="ui-li-count">' .
$number = mysql_fetch_row(mysql_query("SELECT COUNT(`category_id`) FROM `products_has_product_category` WHERE `category_id`=" . $row['product_category_id'] . ""));
echo $number[0];
'</span></li>';
}
?>
将不胜感激任何帮助!
只是想在另一篇文章中发布迈克尔·伯科夫斯基给出的解决方案,我希望它能帮助其他人! 感谢Kickstart为我指明了正确的方向!
<?php
$result = mysql_query("
SELECT
a.product_category_id, a.product_category_name, a.product_category_ref_page,
catcount
FROM
product_category a
LEFT JOIN (
SELECT category_id, COUNT(*) AS catcount
FROM products_has_product_category
GROUP BY category_id
) subcount ON a.product_category_id = subcount.category_id
");
while($row = mysql_fetch_array($result)) {
echo '
<li class="ui-shadow" data-count-theme="d">
<a href="' . $row['product_category_ref_page'] . '.php" data-icon="arrow-r" data-iconpos="right">' . $row['product_category_name'] . '</a><span class="ui-li-count">' . $row['catcount'] . '</span></li>';
}
?>
不需要嵌套查询。可以使用 JOIN 来完成,如下所示:-
SELECT a.*, COUNT(b.category_id) AS CatCount
FROM `product_category` a
LEFT OUTER JOIN `products_has_product_category` b
ON a.product_category_id = b.category_id
GROUP BY a.id, a.someothercolumn, a.etc