Geek asks and answers

MySQL column data returned as comma delimited list

I currently have a MySQL table, for example:

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

I am trying to grab all the ids of a friend of one particular user and sort them into a comma-separated list. For example, captured users user1, they will return as

$friend_list = 2,5,10

I currently have:

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

It only captures one line, though ... help!

thank

+5
source share
5 answers

Do you want to use GROUP_CONCAT:

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

Corrected for correctness for a better answer.

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

, GROUP_CONCAT(column). .

0

, @a, , try , , :

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)

"WHERE"

:

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

- , . - :

$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);

PHP-, , .

:

  • mysql_real_escape_string. , SQL injection.
  • Depending on what you are actually doing, there is a good chance that keeping a list of strings separated by commas is not a good way to do this.
0
source

More articles:


All Articles