How can I use GROUP_CONCAT with multiple values?

Question

I want to get some data via php from my SQL database:

GROUP_CONCAT(DISTINCT cat.name SEPARATOR ",") AS cats

this gives me a list:

sam, john, joe

I get some other data like this:

GROUP_CONCAT(DISTINCT cat.id SEPARATOR ",") AS cat_ids

here I get the result

1,2,3

Now here is my problem. I want to combine these values to get a result like this:

<a href="1">sam</a><a href="2">john</a><a href="3">joe</a>

My approach:

GROUP_CONCAT(CONCAT("<a href=\'",cat.id,"\'>",cat.name,"</a>")) AS cats

But here I only get a blank white page as a result.

I found out is working, the blank white page was caused by another reason — peace_love
– peace_love, Commented Sep 26, 2017 at 13:35

etsa · Accepted Answer · 2017-09-26 13:38:42Z

5

I just used single quotes for string and double quote for attribute html. Moreover I used a space (" ") as separator in GROUP_CONCAT

CREATE TABLE GC1 (name VARCHAR(20), id INT);

    INSERT INTO GC1 VALUES ('sam',1);
    INSERT INTO GC1 VALUES ('john',2);
    INSERT INTO GC1 VALUES ('joe',3);
    SELECT * FROM GC1;
    SELECT GROUP_CONCAT( CONCAT('<a href="',id,'">',name,'</a>') SEPARATOR " ") AS X FROM GC1;

Output:

<a href="1">sam</a> <a href="2">john</a> <a href="3">joe</a>

answered Sep 26, 2017 at 13:38

etsa

5,0501 gold badge9 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Manav · Accepted Answer · 2017-09-26 13:40:35Z

1

This

GROUP_CONCAT(CONCAT('<a href = "', `cat`.`id`, '">', `cat`.`name`, '</a>') SEPARATOR '') AS `cats`

worked for me

answered Sep 26, 2017 at 13:40

Manav

1,36710 silver badges17 bronze badges

Collectives™ on Stack Overflow

How can I use GROUP_CONCAT with multiple values?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related