how do i select entity from a different table?

Question

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?

score 1 · Answer 1 · answered Sep 08 '10 at 15:28

1

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

answered Sep 08 '10 at 15:28

hsz

148,279
62
259
315

score 1 · Accepted Answer · answered Sep 08 '10 at 15:28

1

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

answered Sep 08 '10 at 15:28

aularon

11,042
3
36
41

+1 for keeping the code neat to make me understand, if i could then i would have given you another +1 for the intval tip. i implemented it. the only confusion i am having is between the INNER JOIN and JOIN.. – Ibrahim Azhar Armar Sep 08 '10 at 15:43
@Ibrahim, check here: http://stackoverflow.com/questions/565620/difference-between-join-and-inner-join quoting @palehorse: _They function the same. INNER JOIN can be a bit more clear to read, especially if your query has other join types (e.g. LEFT or RIGHT) included in it._ – aularon Sep 08 '10 at 15:45

score 0 · Answer 3 · answered Sep 08 '10 at 15:28

0

You need a where clause before the ORDER BY:

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

answered Sep 08 '10 at 15:28

Tesserex

17,166
5
66
106

score 0 · Answer 4 · answered Sep 08 '10 at 15:28

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 .................

how do i select entity from a different table?

4 Answers4