GROUP_CONCAT multiple values from table

Question

I just added a table which holds a url and description for each item in place. The below query works ok ... but it's only returning the url and I need the desc also. I m not sure how to make this work.

$query_str = "SELECT      a.userid, a.cat, a.id, a.name, a.image, 
                          a.desc, a.country, b.user_id, c.username, 
                          c.fbook, d.cat_id,
                          (
                               SELECT GROUP_CONCAT(url) 
                               FROM one_add o 
                               WHERE a.id=o.one_id

                          ) AS url,
                          (
                               SELECT COUNT(id) 
                               FROM one_msg m 
                               WHERE m.guest_id = ? AND 
                                     m.one_id=a.id 
                          ) AS count
               FROM place a
                            LEFT OUTER JOIN wait b 
                                 ON a.id=b.post_id AND 
                                    b.user_id= ?
                            JOIN users c
                                 ON a.userid=c.id
                            LEFT JOIN ( 
                                       SELECT userid, user_id, 
                                              GROUP_CONCAT( cat_id ) AS cat_id 
                                       FROM user_cat 
                                       WHERE userid='$user_id' 
                                       GROUP BY user_id 
                                      ) AS d ON d.userid='$user_id' AND 
                                                d.user_id=a.userid
              WHERE a.cat != ? ORDER BY a.date desc";

This is what I want to accomplish: <a href="url">desc</a>

Omesh · Accepted Answer · 2012-08-01T13:05:33.330

1

You can achibe this by using CONCAT inside GROUP_CONCAT function as:

(SELECT GROUP_CONCAT(CONCAT('<a href=\"',url,'\">',desc,'</a>')) 
 FROM one_add o 
 WHERE a.id=o.one_id) AS url

full query:

(SELECT COUNT(id) FROM one_msg m WHERE m.guest_id = ? AND m.one_id=a.id ) AS count
FROM place a
LEFT OUTER JOIN wait b ON a.id=b.post_id AND b.user_id= ?
JOIN users c ON a.userid=c.id
LEFT JOIN ( SELECT userid, user_id, GROUP_CONCAT( cat_id ) AS cat_id FROM user_cat WHERE userid='$user_id' GROUP BY user_id ) AS d ON d.userid='$user_id' AND d.user_id=a.userid
WHERE a.cat != ? ORDER BY a.date desc";

edited Aug 01 '12 at 13:05

answered Aug 01 '12 at 12:07

Omesh

27,801
6
42
51

Hi. Not sure why but it's giving me this: HTTP Error 500 (Internal Server Error): An unexpected condition was encountered while the server was attempting to fulfill the request. – Ciprian Aug 01 '12 at 12:16
Ok ... not sure if this is correct but I made it work like this: `(SELECT GROUP_CONCAT(CONCAT('$desc'))` And set up these two variables `$url ="url"; $desc = "desc";` – Ciprian Aug 01 '12 at 12:25
My mistake ... it s not working. That just returns the actual words url and desc. – Ciprian Aug 01 '12 at 12:39
that's because you need to escape quotes " like \" while assigning string top variable in PHP, see I have modified first query. – Omesh Aug 01 '12 at 13:06
Is there any way to limit these ? Like LIMIT 5 or something – Ciprian Aug 01 '12 at 20:14
have a look at http://stackoverflow.com/questions/1522509/how-to-hack-mysql-group-concat-to-fetch-a-limited-number-of-rows – Omesh Aug 02 '12 at 05:34

GROUP_CONCAT multiple values from table

1 Answers1