Use GROUP_CONCAT syntax

Use GROUP_CONCAT 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. SQL code Java code 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); www.2cto.com to find the course selected by student ID 2, use the following SQL: java code 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) there are three records in the output result of www.2cto.com, indicating that students with the student ID of 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 code Java code foreach ($ pdo-> query ("SELECT student_id, courses_id FROM student_courses WHERE student_id = 2 ") as $ row) {$ result [] = $ row ['courses _ id'];} www.2cto.com, if GROUP_CONCAT () functions and group by statements are very simple, AS shown below: SQL code Java code 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) php code: Java code $ 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']); separators can also be customized, the default Delimiter is ",". To change it to "|", use SEPARATOR to specify it. For example, SELECT student_id in SQL code Java code, GROUP_CONCAT (courses_id SEPARATOR '|') AS courses FROM student_courses WHERE student_id = 2 group by student_id; www.2cto.com, you can also sort the values of this group and connect them to strings. For example, sort the values BY courses_id in descending ORDER: SQL code Java code 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;