CODEWITHSUNDEEP
phpmysql
ahlarsen
ahlarsen

Reputation: 31

PHP, MySQL selecting posts and comments

I'm having problems with my script which need to select all my posts and related comments. Right now I've following query:

$sql = "SELECT posts.post_title, posts.post_modified, post_content,update_modified, update_content 
    FROM   posts 
    LEFT JOIN updates
    ON posts.post_ID = updates.update_post_ID";

The query works great besides if the post has multiple comments it gives me multiple entries. I've searched around but unfortunately I wasn't able to re-script my query for my needs. I really hope someone can help me out?

Upvotes: 2

Views: 582

Answers (2)

Sean
Sean

Reputation: 12433

If you want to return only 1 row per post, with all the comments with the post, the easiest way is using GROUP_CONCAT(). This returns a csv of all the column data. Assuming that update_content is the post comments, try something like -

SELECT posts.post_title, posts.post_modified, post_content, GROUP_CONCAT(update_modified), GROUP_CONCAT(update_content) 
FROM   posts 
LEFT JOIN updates
ON posts.post_ID = updates.update_post_ID
GROUP BY updates.update_post_ID

note - GROUP_CONCAT() has a group_concat_max_len default of 1024. If your comments become too long you will want to increase this before running the GROUP_CONCAT() query or the comments will be truncated - 

SET [GLOBAL | SESSION] group_concat_max_len = 10240; // must be in multiples of 1024
SELECT id, name
GROUP_CONCAT(comment) AS comment
FROM table
GROUP BY name;

you will also need to be aware of max_allowed_packet as this is the limit you can set var_group_concat_max_len to.

Upvotes: 1

Skrivener
Skrivener

Reputation: 1023

I think you want the DISTINCT keyword, used as SELECT DISTINCT ... to avoid duplicates. However if I understand correctly your comments are in the updates table and you're pulling update_modified and update_content into your recordset. So assuming those are (potentially) unique values then DISTINCT will not collapse them down. It might be best to only pull updates.update_post_ID with DISTINCT, then pull whatever you need from updates based on the IDs you retrieve when you need it.

Upvotes: 1

Related Questions