CODEWITHSUNDEEP
mysqlpdo
peace_love
peace_love

Reputation: 6471

How can I use GROUP_CONCAT with multiple values?

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.

Upvotes: 1

Views: 1474

Answers (2)

Manav
Manav

Reputation: 1367

This

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

worked for me

Upvotes: 1

etsa
etsa

Reputation: 5060

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>

Upvotes: 5

Related Questions