How to Use the group_concat () function of MySQL

Syntax:

Group_concat ([distinct] expr [, expr...] [order by {unsigned_integer | col_name | expr} [ASC | DESC] [, col_name...] [separator str_val])

The following shows the function. First, create a student_courses table and fill in some test data.

SQLCode Copy codeThe Code is as follows: Create Table student_courses (
Student_id int unsigned not null,
Courses_id int unsigned not null,
Key (student_id)
);
Insert into student_courses values (1, 1), (1, 2), (2, 3), (2, 4), (2, 5 );

To find the course selected by student ID 2, use the following SQL statement:

SQL codeCopy codeThe Code is as follows: mysql> select student_id, courses_id from student_courses where student_id = 2;
+ ------------ +
| Student_id | courses_id |
+ ------------ +
| 2 | 3 |
| 2 | 4 |
| 2 | 5 |
+ ------------ +
3 rows in SET (0.00 Sec)

The output result contains three records, indicating that students with the student ID 2 have chosen the courses 3, 4, and 5.
Put it in PHP, you must use a loop to get the three records, as shown below:

PHP codeCopy codeThe Code is as follows: foreach ($ PdO-> query ("select student_id, courses_id from student_courses where student_id = 2") as $ row ){
$ Result [] = $ row ['courses _ id'];
}

However, using the group_concat () function and the group by statement is very simple, as shown below:

SQL codeCopy codeThe Code is as follows: mysql> select student_id, group_concat (courses_id) as courses from student_courses where student_id = 2 group by student_id;
+ ------------ + --------- +
| Student_id | courses |
+ ------------ + --------- +
| 2 | 3, 4, 5 |
+ ------------ + --------- +
1 row in SET (0.00 Sec)

In this way, PHP processing is simple:

PHP codeCopy codeThe Code is as follows: $ ROW = $ PdO-> query ("select student_id, group_concat (courses_id) as courses from student_courses where student_id = 2 group by student_id ");
$ Result = explode (',', $ row ['Course']);

The separator can also be customized. The default Delimiter is ",". To change it to "|", use separator to specify it. For example:

SQL codeCopy codeThe Code is as follows: Select student_id, group_concat (courses_id separator '|') as courses from student_courses where student_id = 2 group by student_id;

In addition, you can sort the values of this group and then connect them to strings. For example, sort the values by courses_id in descending order:

SQL codeCopy codeThe Code is as follows: Select student_id, group_concat (courses_id order by courses_id DESC) as courses from student_courses where student_id = 2 group by student_id;