php - 如何从其他表格中选择实体？

我有一个评论系统，我将新闻ID存储在评论表中以进行引用并从新闻表中获取值，我的两个表就是这样。

新表

CREATE TABLE  `news` (
`id` int(20)  NOT NULL auto_increment,
`timestamp` int(20) NOT NULL,
`title` varchar(255) NOT NULL,
`content` text NULL,
`pic_title` varchar(255) NOT NULL,
`pic_brief` varchar(255) NOT NULL,
`pic_detail` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

评论表

CREATE TABLE `comments` (
`id` int(20) NOT NULL auto_increment,
`timestamp` int(20) NOT NULL,
`title` varchar(255) NOT NULL,
`name` varchar(50) NOT NULL,
`email` varchar(50) NOT NULL,
`phone` int(11) NULL,
`location` varchar(50) NOT NULL,
`comment` text NOT NULL,
`approve` tinyint(1) NOT NULL,
`news_id` int(20) NOT NULL,
PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我在评论表的news_id中存储新闻的ID，我想从评论表中进行选择查询，它应该从新闻中选择news.title并引用评论表中的news_id，

我做了这样的事情。

                $query = "SELECT comments.id,
                 comments.timestamp,
                 comments.name,
                 comments.email,
                 comments.phone,
                 comments.location,
                 comments.comment,
                 news.title FROM
                 comments, news ORDER BY id DESC LIMIT $from, " . COMM_POST_NUMBER;

我如何使其仅从news.title中获取标题，并引用注释表中的news_id的ID？

最佳答案

您需要join两个表：

$query = "SELECT comments.id,
         comments.timestamp,
         comments.name,
         comments.email,
         comments.phone,
         comments.location,
         comments.comment,
         news.title
         FROM comments INNER JOIN news ON comments.news_id = news.id
         ORDER BY id DESC LIMIT $from, " . COMM_POST_NUMBER;

另一个表示法是：

FROM comments, news WHERE comments.news_id = news.id

附言请确保清理输入内容，不要依赖$from为整数，而要强制其为整数：

$from = intval($from);