php - 如何使用 mysql 在 php 中显示每家餐厅的评论？

Question

我正在尝试显示每家餐厅的评论数量。

我制作了 2 个包含我的数据的表格。

CREATE TABLE `restaurants` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `city` varchar(200) NOT NULL,
  `country` varchar(200) NOT NULL,
  `score` int(1) NOT NULL DEFAULT '0',
  `reviews` int(1) NOT NULL DEFAULT '0',
  `slug` varchar(200) NOT NULL DEFAULT 'slug-test',
  `approved` int(1) NOT NULL DEFAULT '0',
  `description` text NOT NULL,
  `review` int(11) DEFAULT '0',
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `img_url` varchar(255) NOT NULL,
  `category` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=latin1

CREATE TABLE `reviews` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `restaurant_id` int(11) NOT NULL,
  `review_text` text NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `restaurant_id` (`restaurant_id`),
  CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

我已经在列表中显示了我的餐厅。

$query = "SELECT * FROM restaurants WHERE approved = 1 ORDER BY created_date DESC";
$result = $mysqli->query($query);

while($row = $result->fetch_array()) {
**HTML WITH MY VARIABLES**
}

这就是您当前显示我的评论的方式(我的餐厅表中的硬编码结果。

if ($reviews <= "0") {
echo "<a href='#' title='Write reviews'><p class='purple-def-color fz-12 mb-0'>write a review</p></a>";
} else if ($reviews > "1") {
echo "<p class='purple-def-color mb-0'>". $row['reviews'] ." reviews</p>";
} else {
echo "<p class='purple-def-color mb-0'>" . $row['reviews'] . " review</p>";
}

我尝试使用 JOIN 查询，但未能成功以任何方式显示评论数量。

Answer 1

根据您的表结构，您可以通过加入 reviews 表、选择评论的 COUNT 并按餐厅 ID 分组来获取每家餐厅的评论数。

按餐厅分组可以得到 aggregate (例如 count )每家餐厅的合并评论记录。

SELECT
    rs.*,
    COUNT(rv.`id`) as `reviewCount`
FROM `restaurants` rs
LEFT JOIN `reviews` rv
    ON (rv.`restaurant_id` = rs.`id`)
WHERE rs.`approved` = 1
GROUP BY rs.`id`
ORDER BY rs.`created_date` DESC;

然后，当您在 PHP 中获取行时，您可以引用每一行的评论数:

echo $row['reviewCount'];

---

为了进一步演示聚合函数，下面是一个如何选择 average 的示例, minimum , 和 maximum每家餐厅的评分:

SELECT
    rs.*,
    COUNT(rv.`id`) as `reviewCount`,
    AVG(rv.`score`) as `reviewAverageScore`,
    MIN(rv.`score`) as `reviewMinScore`,
    MAX(rv.`score`) as `reviewMaxScore`
FROM `restaurants` rs
LEFT JOIN `reviews` rv
    ON (rv.`restaurant_id` = rs.`id`)
WHERE rs.`approved` = 1
GROUP BY rs.`id`
ORDER BY rs.`created_date` DESC;