how do i select entity from a different table?

Question

Hi, i have a commenting system where i am storing the News ID in the comments table to refer and to fetch the value from the newstable, my two table is like this.

New Table,

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;

Comments table

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;

in the news_id in the comments table i am storing the id of the news, i want to make the select query from the comments table and it should select the news.title from news referring the news_id in the comments table,

i did something like this.

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

how do i make it to fetch only the title from news.title referring the ID from news_id in the comments table?

Answer 1

You need a where clause before the ORDER BY:

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

Answer 2

using JOIN

SELECT comments.id,
             comments.timestamp,
             comments.name,
             comments.email,
             comments.phone,
             comments.location,
             comments.comment,
             news.title 
FROM 
             comments
join news on news.id=comments.news_id
ORDER BY id DESC 
LIMIT .................

Answer 3

$query = 'SELECT comments.*, news.title
            FROM comments
       JOIN LEFT news
              ON news.id = comments.news_id
        ORDER BY id DESC
           LIMIT ' . (int) $from . ', ' . COMM_POST_NUMBER;

Answer 4

You need to join both tables:

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

Another notation is:

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

P.S. be sure to sanitize your input, don't rely on $from to be an integer, force it to be an integer:

$from = intval($from);