php - 如何从不同的表中选择实体？

Question

我有一个评论系统，我将新闻 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_id的新闻中选择news.title，

我做了这样的事情。

                $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？

Answer 1

您需要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);