[SOLVED] Displaying MySQL Join data properly

[skyer2000]

Got a question on how to display this query correctly with PHP.

 

SELECT papers.papid AS papid,papers.title AS title, keywords.keyword AS keyword
FROM papers,keywords
WHERE papers.topid = ?
AND keywords.topid = papers.topid

 

What I want to do here is only show the papid once, but this one papid may have multiple keywords that are found using this query.

 

How do I make it display multiple keywords, but only one papid? Is this the correct way to go about it?

 

[kickstart]

Hi

 

You could just loop through the returned data in php, and only display the papid and title when they change.

 

Another option would be to group the returned rows by the papid and just concatenate all the key words.

 

SELECT papers.papid AS papid,papers.title AS title, GROUP_CONCAT(keywords.keyword) AS keyword
FROM papers,keywords
WHERE papers.topid = ?
AND keywords.topid = papers.topid
GROUP BY papers.papid

 

All the best

 

Keith

[skyer2000]

Perfect! Thank you!

 

For anyone who made need more reference on that, this page does a great job explaining it: http://www.mysqlperformanceblog.com/2006/09/04/group_concat-useful-group-by-extension/