php - 计数函数()

我有这两张表，里面有一些数据样本。我想找出每个类别中的分类数。我试了一下，每一个都有2个错误。希望有人能帮我。

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `name` text COLLATE utf8_unicode_ci NOT NULL,
  `subcategory_id` int(2) NOT NULL DEFAULT '0',
  `parent_id` int(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=27 ;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`id`, `name`, `subcategory_id`, `parent_id`) VALUES
(1, 'Announcements', 0, 0),
(2, 'Employment', 0, 0),
(3, 'Items For Sale', 0, 0),
(4, 'Services', 0, 0),
(5, 'Garage Sales', 0, 0),
(6, 'Automobiles', 0, 0),
(7, 'Announcement1', 1, 1),
(8, 'Announcement2', 1, 1),

--
-- Table structure for table `classifieds`
--

CREATE TABLE IF NOT EXISTS `classifieds` (
  `classified_id` int(255) NOT NULL AUTO_INCREMENT,
  `title` text COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci NOT NULL,
  `category_id` int(10) NOT NULL,
  `name` text COLLATE utf8_unicode_ci NOT NULL,
  `authorized` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`adid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=177 ;

--
-- Dumping data for table `classifieds`
--

INSERT INTO `classifieds` (`classified_id`, `title`, `description`, `category_id`, `name`, `authorized`) VALUES
(1, 'Test Classified', 'Here is the First Test classified listing.', 1, 1);

INSERT INTO `classifieds` (`classified_id`, `title`, `description`, `category_id`, `name`, `authorized`) VALUES
(2, 'GMC For Sell', 'Looks like new 1979 GMC.', 6, 1);

在这里

$query = "SELECT category_id, COUNT(title) FROM classifieds GROUP BY category_id";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result)
$num_items_in_category = $row['COUNT(title)'];
echo "<><a href='category-".$row['id'].".php' >".$row['name'].$num_items_in_category."</a></li>";

谢谢

最佳答案

稍微更改一下SQL，然后循环查看结果？

$query = "SELECT c.id, c.name,
                 COUNT(cl.category_id) AS num_items_in_category
              FROM category_id c
                 LEFT JOIN aclassifieds cl ON cl.category_id=c.id
              GROUP BY c.id";
$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
    echo "<li><a href='category-".$row['id'].".php' >".$row['name'].$row['num_items_in_category']."</."</a></li>";
}

关于php - 计数函数()，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/10544628/