MySQL column data returned as comma delimited list

Question

I currently have a MySQL table like:

id | friend
1  |   2
1  |   5
1  |   10
3  |   6
15 |   19
21 |   4

I'm trying to grab all the friend id's of one particular user and arrange them into a comma-delimited list. For example, grabbed user1's friends, it would return as

$friend_list = 2,5,10

Currently, I have:

$sql = "SELECT friend FROM table__friends WHERE id = ".$user_id;

This only grabs one row though..please help!

Thanks

I am pretty strongly against `GROUP_CONCAT` - it feels like misuse of a database engine to me. — We Are All Monica, Jun 11 '12 at 20:47
@jnylen - Not sure why, no cautionary notes of it by the MySQL folks, as well, the question was tagged MySQL, not PHP. -To each his own friend, just saying :) — GDP, Jun 11 '12 at 21:56
@GregP I guess it's because database operations should manipulate and return tables of data - they shouldn't deal with "fake" data types like a comma-separated string, so it's a bad "code smell" to me. Any time you have a comma delimited list, you should probably just leave it in rows or an array instead. Here's a good example of the problems you can face: http://stackoverflow.com/questions/6643656/mysql-comma-delimited-list-possible-to-add-and-remove-values?rq=1 — We Are All Monica, Jun 12 '12 at 14:42
Fair response, though I respectfully disagree. Asking the database engine to return a format I prefer has little to do with the structure or normalization. — GDP, Jun 12 '12 at 15:26

GDP · Accepted Answer · 2012-06-11T21:57:38.333

13

You want to use GROUP_CONCAT :

$sql = "SELECT GROUP_CONCAT(friend) FROM table__friends GROUP BY id HAVING id = ".$user_id;

Adjusted for correctness per the better answer.

edited Jun 11 '12 at 21:57

answered Jun 11 '12 at 20:37

GDP

8,109
6
45
82

1

mentionable: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_group_concat_max_len – goat Jun 11 '12 at 20:39
1

And you can of course specify the delimiter in GROUP_CONCAT :) – Olivier Coilland Jun 11 '12 at 20:41
Thanks! You can't imagine how glad I was that this function exists in MySQL!! – zur4ik Dec 16 '13 at 16:20
If results are big it doesnt work as limit is 1024 and on some shared hosting it become problem. Cant set global rule. – WhiteHorse Jun 15 '18 at 16:36

score 4 · Answer 2 · answered Jun 11 '12 at 20:39

4

$sql = "SELECT GROUP_CONCAT (DISTINCT friend SEPARATOR ',') 
      FROM table_friends GROUP BY id 
       HAVING id =".$user_id;

answered Jun 11 '12 at 20:39

WojtekT

4,735
25
37

We Are All Monica · Answer 3 · 2012-06-11T20:51:18.823

Your query will return more than one row - it sounds like you're only reading the first row. You want something like this instead:

$sql = "SELECT friend FROM table__friends WHERE id = "
  . mysql_real_escape_string($user_id);

$result = mysql_query($sql);
if (!$result) {
  die("Something bad happened");
}

$friend_arr = array();
while ($row = mysql_fetch_array($result)) {
  $friend_arr[] = $row[0];
}

$friend_list = implode(',', $friend_arr);

Post the PHP code you're using to run the query and we'll be able to help more.

A couple of notes:

I added the mysql_real_escape_string function. Sanitizing your user inputs in this way is crucial to avoid SQL injection attacks.
Depending on what you're actually doing - there's a good chance that storing a comma-separated list of strings isn't a good way to do it.

score 0 · Answer 4 · answered Jun 11 '12 at 20:39

0

You are probably looking for GROUP_CONCAT(column) function. Examples here and here.

answered Jun 11 '12 at 20:39

Andrius Naruševičius

8,348
7
49
78

score 0 · Answer 5 · answered Jun 11 '12 at 20:43

I used a Variable called @a, to store the ids, at the end you have the values in the variable or try with a limit, group order by, like this:

mysql> show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


mysql> set @a:=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @a:=concat(@a,",",actor_id) as actor_id from actor where actor_id>195 order by (actor_id) desc limit 1;
+----------------------------+
| actor_id                   |
+----------------------------+
| 0,196,197,198,199,200,205, |
+----------------------------+
1 row in set (0.00 sec)

In your case change the "WHERE condition"

or you can also after the select:

mysql> select @a;
+-------------------------------+
| @a                            |
+-------------------------------+
| 0,196,197,198,199,200,205,206 |
+-------------------------------+
1 row in set (0.00 sec)

MySQL column data returned as comma delimited list

5 Answers5

Linked