how to remove duplicated columns and group column

Question

Hello guys i currently have a MySQL query that works perfectly fine, but the only thing i don't like is that when I get returned data, some rows come duplicated except for the last column of the row .

I guess the reason why is because when I'm selecting everything including the left join, the commenttext column contains the data for a post submitted by a user.

For example) If I post a post with id of 1 and 5 people comment on that post MySQL query will bring up 5 rows with all the data in every column the same except for the last column containing the different comment pertaining to the post.

So far here is my MySQL query. How can I make it where it doesn't bring back duplicated data but only the comments grouped with the id of the post or how can i store all the comments to an array so when I run a foreach loop I can then run the comments array inside with a while loop. The reason I would use a foreach loop is to export the data with html .

SELECT
    b.id
    , b.from_user
    , b.dateadded
    , b.posttype
    , b.posttext
    , b.photoname
    , b.blahchoice
    , b.commentschoice
    , b.mood
    , c.defaultphoto
    , d.firstn
    , d.lastn
    , e.status
    , f.topostid
    , f.commenttext 
FROM
    t_board b 
INNER JOIN
    t_userprofiles c ON b.from_user = c.user_id  
INNER JOIN
    t_users d ON b.from_user = d.id
INNER JOIN
    t_friendship e ON e.friend_ids = b.from_user
LEFT JOIN
    t_postcomments f ON f.topostid = b.id
WHERE
    e.status = 'Friend' 
    AND e.user_ids = :id
ORDER BY
    b.id DESC

Can you PLEASE format the query better? Also, you might want to create an [SQL fiddle](http://sqlfiddle.com) for us to play with — Niklas B., May 27 '14 at 09:55
possible duplicate of [How do I (or can I) SELECT DISTINCT on multiple columns?](http://stackoverflow.com/questions/54418/how-do-i-or-can-i-select-distinct-on-multiple-columns) — Peon, May 27 '14 at 09:55
Why don't you just run multiple queries and do it in php then ? — Aliendroid, May 27 '14 at 09:56
I Did but i want to make it easier for ajax to fetch at one time @Aliendroid — this.Tony, May 27 '14 at 09:57
I don't affect your ajax, no one said you can only run one query in a single call. @this.Tony — Aliendroid, May 27 '14 at 09:59
@this-tony You can have several queries in php on your server and aggregate them on the server so that your ajax query still returns a single JSON object, with an array of posts having each a sub-array of comments. — CCH, May 27 '14 at 09:59
i understand lol but i was wondering if its possible and would it make it for better practice ... to make my code short?@CCH — this.Tony, May 27 '14 at 10:01

Abhik Chakraborty · Accepted Answer · 2014-05-27T10:08:14.573

2

One way to do it is group_concat() the comments and while you run inside the loop explode() the comments and then do foreach to display the the comments under the id

SELECT b.id, 
b.from_user, 
b.dateadded, 
b.posttype, 
b.posttext, 
b.photoname, 
b.blahchoice, 
b.commentschoice, 
b.mood, 
c.defaultphoto, 
d.firstn, 
d.lastn, 
e.status, 
f.topostid, 
group_concat(f.commenttext) as  commenttext
FROM t_board b 
INNER JOIN t_userprofiles c ON b.from_user = c.user_id  
INNER JOIN t_users d ON b.from_user = d.id
INNER JOIN t_friendship e ON e.friend_ids = b.from_user
LEFT JOIN t_postcomments f ON f.topostid = b.id
WHERE e.status = 'Friend' 
AND e.user_ids = :id
group by b.id
ORDER BY b.id DESC

NOTE that while you execute the query on PHP commenttext will all be comma separated and inside the loop to fetch the data for each b.id you will have all the comments for that id as comma - separated string , you can explode it to generate another array and display them.

Also some of your comments may have comma in it so you may need to provide a separator for group_concat something as below or can choose any custom operator.

group_concat(f.commenttext SEPARATOR '||' ) as  commenttext

AND explode the data with ||

NOTE : The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024 http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

edited May 27 '14 at 10:08

answered May 27 '14 at 10:02

Abhik Chakraborty

44,654
6
52
63

This won't work well if any comments contain a comma. You need to specify a separator that doesn't appear in any comments. And you'll probably need to make `group_concat_max_len` very large to avoid truncation. – Barmar May 27 '14 at 10:05
Yes I was about to update the answer, I have updated it. – Abhik Chakraborty May 27 '14 at 10:08
no you do not need to change anything in the DB column since the data is concated at the time of select, however if the comment sizes are really big then you may need to change the mysql setting to increase group_concat_max_len value to a big number and restart the mysql. Its not needed if your comments are of small size and after concat it is less than 1024 chars. The config file is on `/etc/mysql/my.cf` in debian and in windows WAMP you can modify it via the GUI. – Abhik Chakraborty May 27 '14 at 10:25
i Tested it works good, now say i want to grab the data for the comment how would i go about like who posted the comment and at what time the comment was posted? – this.Tony May 27 '14 at 10:31
hmm that I suppose will not work with this query since its one-to-many and you will be left with multiple rows i.e. for each comment you will be having one row. And the comments along with user and date time can also be concated but this will require a subquery to perform the operation. – Abhik Chakraborty May 27 '14 at 10:35
would you rather recommend me to instead do two different queries instead? – this.Tony May 27 '14 at 10:39
I would not recommend multiple queries unless there is no way, but for one-to-many I would suggest to select all the data then with PHP do the manipulation, the above answer is just specific to grouping the comments as one string per id and then display them so that you do not get multiple rows one for each comment. – Abhik Chakraborty May 27 '14 at 10:47

score 2 · Answer 2 · answered May 27 '14 at 10:04

Combine the entries for the same post in your PHP fetch loop:

$results = array();
while ($row = $stmt->fetch()) {
    if (isset($results[$id]) {
        $results[$id]['comments'][] = $row['commenttext'];
    } else {
        $row[$id] = $row;
        $row['comments'] = array($row['commenttext']);
    }
}

how to remove duplicated columns and group column

2 Answers2