Combine multiple columns and rows into a single entry

Question

I have a relational database with one main table linked to other tables with foreign keys. I am trying to write a reporting tool in PHP to grab all the data out but have gotten stck with one part of it.

here is my query so far:

SELECT * 
FROM student s 
LEFT OUTER JOIN ep e ON s.sID = e.sID 
LEFT OUTER JOIN ntc n ON s.sID = n.sID 
LEFT OUTER JOIN pk p ON s.sID = p.sID 
LEFT OUTER JOIN roa r ON s.sID = r.sID 
WHERE s.sID = '$id'

ep, ntc and pk are all 1:1 with student so there are no problems. But the roa table holds multiple records (spanning multiple rows) for each student.

roa table structure:

+----------+--------------+------+-----+-------------------+----------------+
| Field    | Type         | Null | Key | Default           | Extra          |
+----------+--------------+------+-----+-------------------+----------------+
| roaID    | int(11)      | NO   | PRI | NULL              | auto_increment |
| sID      | int(11)      | NO   | MUL | NULL              |                |
| roa      | varchar(255) | NO   |     | NULL              |                |
| roaStaff | varchar(50)  | NO   |     | NULL              |                |
| visible  | tinyint(1)   | NO   |     | 1                 |                |
+----------+--------------+------+-----+-------------------+----------------+

where a select * from roa where sID = 1 returns:

+-------+-----+---------------+----------+---------+
| roaID | sID | roa           | roaStaff | visible |
+-------+-----+---------------+----------+---------+
|    41 | 1   | Description 1 | Staff 1  |       1 |
|    60 | 1   | Description 2 | Staff 2  |       1 |
+-------+-----+---------------+----------+---------+

what I am after my original query to achieve is a result something like:

<-student, etc data
..-------+-----+-----------------------------------------------+---------+
.. roaID | sID | roa                                           | visible |
..-------+-----+-----------------------------------------------+---------+
..    41 | 1   | Description 1 Staff 1, Description 2 Staff 2  |       1 |
...
...

so that both the roa.roa and the roa.roaStaff are all in one cell.

I have tried using GROUP_CONCAT but have only been able to get it to work for one column, not group multiple columns together.

any help is greatly appreciated.

regards.

score 1 · Accepted Answer · edited May 23 '17 at 12:07

1

try something like this,

SELECT ......, GROUP_CONCAT(CONCAT(roa, ' ', roaStaff)) roa
FROM ...

so when you plug it in your query,

SELECT * 
FROM student s 
LEFT OUTER JOIN ep e ON s.sID = e.sID 
LEFT OUTER JOIN ntc n ON s.sID = n.sID 
LEFT OUTER JOIN pk p ON s.sID = p.sID 
LEFT OUTER JOIN 
(
    SELECT sID, GROUP_CONCAT(CONCAT(roa, ' ', roaStaff)) roa
    FROM roa 
    GROUP BY sID
) r ON s.sID = r.sID 
WHERE s.sID = '$id'

follow-up question, how about the visible field on table roa? How would you want it to be shown

SIDENOTE

your query is vulnerable with SQL INJECTION, please read the article below to learn how to protect from it

How can I prevent SQL injection in PHP?

edited May 23 '17 at 12:07

Community

1
1

answered Dec 12 '12 at 03:44

John Woo

258,903
69
498
492

Hi, thanks that worked. the visible does not actually get used. it is more for an alternative to deleting, so that we can still go back and see what has been "deleted". all data is parsed through `mysqli_real_escape_string` before being added to the query. Is that not enough to prevent injection? – Jake Dec 12 '12 at 04:13

Combine multiple columns and rows into a single entry

1 Answers1