How to merge multiple records into a single record in Mysql, PHP

Question

I have 3 tables courses students results there are like 9 courses in the courses' table

when a student takes an exam it stores the result in the results table like:

id student_id, course_id, score
    1  12345    23456       50
    2  12345    12345       20
    -  ----     -----       --

Assuming: course id 23455 is MS Word course id 12345 is PowerPoint

what i'm trying to do is to get all the results of every user in a single row something like: 1. elexis promise 123322 50 MS Word 20 PowerPoint

My code is:

SELECT s.first_name, s.last_name, s.ap_number, r.student_id, r.course_id, r.total_score, c.course_id, c.course_title
                FROM students AS s
                JOIN result AS r
                    ON s.ap_number = r.student_id
                    JOIN courses AS c
                    ON r.course_id = c.course_id

but i get

1. elexis promise 123322 50 MS Word
2. elexis promise 123322 20 PowerPoint
-  ------ ------  -----  -- ---------

in my HTML i have something like :

<thead>
    <tr>
        <th class="ml-5">ID</th>
        <th>Name</th>
        <th>Power Point</th>
        <th>Word</th>
        <th>----</th>
    </tr>
</thead>
<tbody>
<?php $id = 0;
foreach ($results as  $value) {?>
<tr>
    <td><?php echo $id += 1 ?></td>
    <td><?php echo "{$value["first_name"]} {$value["last_name"]}" ?></td>
    <td><?php echo $value["total_score"] ?></td>
    <td><?php echo $value["total_score"] ?></td>
    <td><?php echo ---- ?></td>
</tr>
<?php }
?>

score 0 · Answer 1 · answered Jul 25 '22 at 15:52

0

You could try rewriting your query to make use of GROUP_CONCAT function and GROUP BY clause. Something like this:

SELECT
    s.first_name,
    s.last_name,
    s.ap_number,
    GROUP_CONCAT(r.total_score, c.course_title)
FROM
    students AS s
    JOIN result AS r ON s.ap_number = r.student_id
    JOIN courses AS c ON r.course_id = c.course_id
GROUP BY s.ap_number

answered Jul 25 '22 at 15:52

Karol Oracz

182
6

It gave me 1 row which is okay.. But how can I access the scores for each course title. – Elex Promise Jul 25 '22 at 15:59
Why do you want to access individual scores separately? – Shadow Jul 25 '22 at 16:15
If `r.total_score` column is the score for a single course, you should have now score grouped along with the course title in the last column. – Karol Oracz Jul 25 '22 at 16:16
@Shadow It is because I want to have something like: Id name msword powerpoint (these are the columns headings in the front-end). Then, 1. Elexis Promise 20 50 ( these are for the td/values for the aforementioned headings. In essence, I want to use the c.course_title (from the sql) to know where to place the score( equivalent th, in the HTML) – Elex Promise Jul 25 '22 at 16:55
Then you need dynamic pivoting, not grouping – Shadow Jul 25 '22 at 16:59

How to merge multiple records into a single record in Mysql, PHP

1 Answers1