php - PHP从具有3个连接的2个表中输出数据
问题描述
希望你能帮忙,
我有 3 个表格
文章
article_keywords_link
关键字
它们结合在一起,我想要实现 1 篇文章显示在页面上,但显示链接到文章的所有相关关键字。
我还没有格式化这个页面,我只是想让功能正常工作。
SELECT article.article_id, article.article_title, article.article_content, keywords.keyword_name
FROM article
JOIN article_keyword_link ON article.article_id = article_keyword_link.article_id
INNER JOIN keywords ON article_keyword_link.keyword_id = keywords.keyword_id
在 Phpmyadmin 中运行上述查询,我收到这些结果
Query Output from PhpmyAdmin
+------------+---------------+-------------------+--------------+
| article_id | article_title | article_content | keyword_name |
+------------+---------------+-------------------+--------------+
| 1 | Article 1 | Article 1 content | CCU |
| 1 | Article 1 | Article 1 content | WIFI |
| 1 | Article 1 | Article 1 content | Network |
| 2 | Article 2 | Article 2 content | Passenger |
| 2 | Article 2 | Article 2 content | Application |
+------------+---------------+-------------------+--------------+
这是表格的填充方式
Table: article
+------------+---------------+-------------------+
| article_id | article_title | article_content |
+------------+---------------+-------------------+
| 1 | Article 1 | Article 1 content |
| 2 | Article 2 | Article 2 content |
+------------+---------------+-------------------+
Table: article_keywords_link
+------------+------------+
| article_id | keyword_id |
+------------+------------+
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 2 | 4 |
| 1 | 7 |
+------------+------------+
Table: keywords
+------------+--------------+
| keyword_id | keyword_name |
+------------+--------------+
| 1 | CCU |
| 2 | WIFI |
| 3 | Passenger |
| 4 | Application |
| 5 | Software |
| 6 | Hardware |
| 7 | Network |
+------------+--------------+
页面来源:
<?php
include 'includes/db.php';
// Create connection
$conn = new mysqli($host, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "
SELECT article.article_id, article.article_title, article.article_content, keywords.keyword_name
FROM article
JOIN article_keyword_link ON article.article_id = article_keyword_link.article_id
INNER JOIN keywords ON article_keyword_link.keyword_id = keywords.keyword_id
";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "Article ID: " . $row["article_id"] . "<br>";
echo "Article Title: " . $row["article_title"] . "<br>";
echo "Article Content: " . $row["article_content"] . "<br>"
;
}
} else {
echo "0 results";
}
$conn->close();
?>
页面输出:
Article ID: 1
Article Title: Article 1
Article Content: Article 1 content
Article ID: 1
Article Title: Article 1
Article Content: Article 1 content
Article ID: 1
Article Title: Article 1
Article Content: Article 1 content
Article ID: 2
Article Title: Article 2
Article Content: Article 2 content
Article ID: 2
Article Title: Article 2
Article Content: Article 2 content
所以我面临两个问题。
1. 如何只检索一篇文章以显示在页面上,同时显示与该文章相关的所有关键字?
2. 如何调整我的源代码以显示关键字?如果我在关键字名称的循环中添加新的回声,则页面不会加载。
对此的任何帮助将不胜感激
解决方案
You're almost there, try this.
SELECT article.article_id, article.article_title, article.article_content, group_concat(keywords.keyword_name) as keywords
FROM article
JOIN article_keyword_link ON article.article_id = article_keyword_link.article_id
INNER JOIN keywords ON article_keyword_link.keyword_id = keywords.keyword_id
GROUP BY article.article_id
for information on the functions used to acheive this. please see.
https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html
Additionally, if you just want one article you add limit 1
to the end of your query. and if you want the newest/oldest you would add ORDER BY article_id DESC LIMIT 1
for newest and ORDER BY article_id ASC LIMIT 1
for oldest
推荐阅读
- reactjs - useState 加载状态未触发
- svg - 为什么在 PimCore v6.3.6 中不呈现 svg 预览?
- javascript - Highcharts Angular Chart 不是来自 renderer.button 事件的 redrwan
- java - JPQL/HQL 使用 spring 数据 jpa 删除查询
- django - 禁用字段导致 Django 中的表单验证错误
- javascript - 带 2 个小数点的年龄正则表达式
- java - 使用java在Apache spark中获取单列值作为平面列表
- python - 返回给定字典键的下一个键,python 3.6+
- reactjs - 为什么我得到方法“props”只意味着在单个节点上运行。在单元测试中发现 0?
- java - 如何从具有 2 个字符输出的日期字符串中获取周数