Display News And Comments

[shawy14]

Hello All, I am new here so first off hello to you all

 

Right my problem, I am trying to find a way to be able to display news items with comments under them and a form for somebody to write a comment. Like on facebook where a person has a status then comments under it.

 

I have two tables in MySql already, news and newscomments. The way I have linked the tables is by ID, newscomments has a field called News_ID which matches with the ID of a news item.

 

I can get all news items to display however I can't get the comments to display with them.

 

Please could somebody give me a hand with this?

 

Thanks

 

Richard

 

 

[wildteen88]

I guess when you view a news entry your url is like site.com/news.php?id=1. Which displays the news article with the id of 1 from your news table. If thats the cause you can use a JOIN which will query both your news and newscomments table at the same time. Example join query.

 

SELECT 
    n.title, n.author, n.article,
    c.username, c.email, c.coment
FROM news n 
LEFT JOIN newscomments c 
    ON (c.News_ID = n.ID)
WHERE n.ID = $news_id

[cyberRobot]

Note: to setup a table alias, you need to use the AS keyword:

 

SELECT 
     n.title, n.author, n.article,
     c.username, c.email, c.coment
FROM news AS n 
LEFT JOIN newscomments AS c 
     ON (c.News_ID = n.ID)
WHERE n.ID = $news_id

 

 

You could also use the full table name if you find table aliases confusing:

 

SELECT 
     news.title, news.author, news.article,
     newscomments.username, newscomments.email, newscomments.coment
FROM news 
LEFT JOIN newscomments 
     ON (newscomments.News_ID = news.ID)
WHERE news.ID = $news_id

[wildteen88]

Isn't the AS keyword optional? I've never had any problems without it.

[shawy14]

I guess when you view a news entry your url is like site.com/news.php?id=1. Which displays the news article with the id of 1 from your news table. If thats the cause you can use a JOIN which will query both your news and newscomments table at the same time. Example join query.

 

SELECT 
    n.title, n.author, n.article,
    c.username, c.email, c.coment
FROM news n 
LEFT JOIN newscomments c 
    ON (c.News_ID = n.ID)
WHERE n.ID = $news_id

 

Hi thanks for this, very fast replying. However if I put /news.php?id=1 that in it doesn't display anything at all. How would I be able to get that to happen?

 

Thanks again

[cyberRobot]

Did you change $news_id to whatever variable you're using for the ID? Maybe:

 

WHERE n.ID = $_GET['id']

[cyberRobot]

Isn't the AS keyword optional? I've never had any problems without it.

 

 

You're right; I could have sworn it was required. Personally, I find leaving out the AS keyword a little confusing. But then again, I'm not a fan of table aliases.

[shawy14]

Right got that working, it is displaying each news item with a comment underneath however if a news item has two comments it displays the news item twice. Once with the first comment and then a second time with the second comment :/

[wildteen88]

As the query returns both the news and the comments. You will need to first extract the news article and then add all the comments into an array. Then where you want your comments to display you'll then use a foreach loop to output your comments.