GROUP_CONCAT useful GROUP BY extension

September 4, 2006

Author

Vadim Tkachenko

Insight for Developers

Share this Post:

(There is an updated version of this post here)

MySQL has useful extention to the GROUP BY operation: function GROUP_CONCAT:

GROUP_CONCAT(expr) – This function returns a string result with the concatenated non-NULL values from a group.

Where it can be useful?

For example to get PHP array without looping inside PHP:

Table:

CREATE TABLE services (<br>id INT UNSIGNED NOT NULL, <br>client_id INT UNSIGNED NOT NULL, <br>KEY (id));<br>INSERT INTO services <br>VALUES (1,1),(1,2),(3,5),(3,6),(3,7);<br><br>SELECT id,client_id FROM services WHERE id = 3;<br>+----+-----------+<br>| id | client_id |<br>+----+-----------+<br>|  3 |         5 | <br>|  3 |         6 | <br>|  3 |         7 | <br>+----+-----------+<br><br>SELECT id,GROUP_CONCAT(client_id) FROM services WHERE id = 3 GROUP BY id;     <br>+----+-------------------------+<br>| id | GROUP_CONCAT(client_id) |<br>+----+-------------------------+<br>|  3 | 5,6,7                   | <br>+----+-------------------------+

CREATE TABLE services ( id INT UNSIGNED NOT NULL, client_id INT UNSIGNED NOT NULL, KEY (id)); INSERT INTO services VALUES (1,1),(1,2),(3,5),(3,6),(3,7); SELECT id,client_id FROM services WHERE id = 3; +----+-----------+ | id | client_id | +----+-----------+ | 3 | 5 | | 3 | 6 | | 3 | 7 | +----+-----------+ SELECT id,GROUP_CONCAT(client_id) FROM services WHERE id = 3 GROUP BY id; +----+-------------------------+ | id | GROUP_CONCAT(client_id) | +----+-------------------------+ | 3 | 5,6,7 | +----+-------------------------+

Handling in PHP:
old way:

<!--?php $res=$mysqli--->query("SELECT id,client_id FROM services WHERE id = 3");<br> while ($row = $res-&gt;fetch_array(MYSQLI_ASSOC)) {<br> $result[] = $row['client_id'];<br>}<br>$res-&gt;free();<br>?&gt;

1	<!--?php $res=$mysqli--->query("SELECT id,client_id FROM services WHERE id = 3");<br> while ($row = $res->fetch_array(MYSQLI_ASSOC)) {<br> $result[] = $row['client_id'];<br>}<br>$res->free();<br>?>

with group_concat:

<!--?php $res=$mysqli--->query("SELECT id,GROUP_CONCAT(client_id) as clients FROM services WHERE id = 3 GROUP BY id");<br>$row = $res-&gt;fetch_array(MYSQLI_ASSOC);<br>$result = explode(',', $row['clients']); // $row['clients'] contains string 5,6,7<br>$res-&gt;free();<br>?&gt;

1	<!--?php $res=$mysqli--->query("SELECT id,GROUP_CONCAT(client_id) as clients FROM services WHERE id = 3 GROUP BY id");<br>$row = $res->fetch_array(MYSQLI_ASSOC);<br>$result = explode(',', $row['clients']); // $row['clients'] contains string 5,6,7<br>$res->free();<br>?>

This should work faster, as we remove loop from PHP to MySQL server side.

Also it can be handy to use result concatenated string as part of IN statement:

<!--?php $res=$mysqli--->query("SELECT id,GROUP_CONCAT(client_id) as clients FROM services WHERE id = 3 GROUP BY id");<br>$row = $res-&gt;fetch_array(MYSQLI_ASSOC);<br>$result = $row['clients']; // $row['clients'] contains string 5,6,7<br>$res-&gt;free();<br><br>$resclients=$mysqli-&gt;query("SELECT id,client_name FROM clients WHERE id = IN ($result)");<br>// handle $resclients<br><br>?&gt;

query("SELECT id,GROUP_CONCAT(client_id) as clients FROM services WHERE id = 3 GROUP BY id"); $row = $res->fetch_array(MYSQLI_ASSOC); $result = $row['clients']; // $row['clients'] contains string 5,6,7 $res->free(); $resclients=$mysqli->query("SELECT id,client_name FROM clients WHERE id = IN ($result)"); // handle $resclients ?>

Sure, last example can be handled with one query with joins, but sometimes we need the temporary ids in clients code, for example to execute query on another server.

One more thing: you may want to add ORDER BY NULL statement after GROUP_BY to avoid
unnecessary sorting with filesort