如果我选择该类别的新闻，如何选择该类别名称

Question

我正在设置一个查询，其中我选择了新闻的ID来显示该特定的id新闻，我在新闻表中有一个类别表的外键作为类别ID，现在我希望如果我显示新闻，我也显示类别的标题。

我尝试选择仅显示id的类别ID，并且我想显示该类别的标题。

<?php
if(isset($_GET['news_id'])){
$the_news_id = $_GET['news_id'];
}
$query = "SELECT * FROM news_title WHERE news_id = '$the_news_id'";
$select_all_news_query = mysqli_query($connection,$query);
while($row= mysqli_fetch_assoc($select_all_news_query)){
$title = $row['news_title'];
$description = $row['news_description'];
$image = $row['news_image'];
$news_cat_title= $row['news_cat_id'];
?>
<h1 class="page-header">News Page</h1>
<!-- First Blog Post -->
<h2>
<a href="#"><?php echo $title;?></a>
</h2>

<img class="img-responsive" src="image/<?php echo $image; ?>" 
alt="abc">
<hr>
<p><?php echo $description; ?></p>
<hr>
<?php  }
?>
<!-- Second Blog Post -->
<hr>
<div class="container">
<div class="row">
<h1 class="page-header">
<!--This is where i want to show title but i am getting the ID--!>
<?php echo $news_cat_title; ?>
</h1>
</div>
</div>

我希望得到这个头衔，但我得到了一个数字。

Answer 1

在不知道各种表的模式的情况下，很难确定我是否正确解释了这个问题，但我假设您要做的是连接两个表？如果您可以添加相关的表架构，这将有所帮助。

select * from news_title t
left outer join category c on c.category_id=t.category_id
where news_id = '$the_news_id'

根据你最后的评论，类别表只是简单地称为category，外键是cat_id

select * from news_title t
left outer join category c on c.cat_id=t.cat_id
where news_id = '$the_news_id'

然后，在显示记录集时，您应该能够：-

echo $row['cat_title'];

捕获和显示表架构的简单方法(在Windows上(：

• 打开cmd提示
• 键入mysql -u root -p <DBNAME>~ 更改实际数据库！
• 键入describe <TABLE>;- 按回车键
• 复制显示的信息并粘贴到文本编辑器中
• 对所有相关性表重复此操作

Answer 2

请尝试以下代码：

<?php
if(isset($_GET['news_id'])){
$the_news_id = $_GET['news_id'];
}
$query = "SELECT N.*, NC.cat_id AS news_cat_id
FROM news_title AS N
LEFT JOIN news_category AS NC
ON N.cat_id = NC.cat_id 
WHERE news_id = '$the_news_id'";
$select_all_news_query = mysqli_query($connection,$query);
while($row= mysqli_fetch_assoc($select_all_news_query)){
$title = $row['news_title'];
$description = $row['news_description'];
$image = $row['news_image'];
$news_cat_title= $row['news_cat_id'];
?>
<h1 class="page-header">News Page</h1>
<!-- First Blog Post -->
<h2>
<a href="#"><?php echo $title;?></a>
</h2>

<img class="img-responsive" src="image/<?php echo $image; ?>" alt="abc">
<hr>
<p><?php echo $description; ?></p>
<hr>
<?php } ?>
<!-- Second Blog Post -->
<hr>
<div class="container">
<div class="row">
<h1 class="page-header">
<!--This is where i want to show title but i am getting the ID--!>
<?php echo $news_cat_title; ?>
</h1>
</div>
</div>

基于您的问题和评论的查询语句可能是这样的：

$query = "SELECT N.*, NC.cat_id AS news_cat_id
FROM news_title AS N
LEFT JOIN news_category AS NC
ON N.cat_id = NC.cat_id 
WHERE news_id = '$the_news_id'";