Styjun

I want to be able to display data from a SQL database (using PDO) and present it in an HTML table, however, I am having trouble understanding how to include a subquery in the results of my original query. I have gotten close to my desired result by using the GROUP_CONCAT function to group the data as comma separated values.

Using Twig to populate the HTML table with the desired values, but I can't figure out how to group certain values (course names) underneath the student.

`PHP
SELECT classes.ID, classes.PIDM, classes.fName, classes.lName, classes.advisorOneFirst, classes.advisorOneLast, classes.advisorOneEmail, 
GROUP_CONCAT(classes.courseTitle) AS courses
FROM classes
WHERE term = :cterm AND (classes.MGrade = "F" OR classes.MGrade = "F~" OR classes.MGrade = "D" OR classes.MGrade = "D~")
GROUP BY classes.ID
ORDER BY lName ASC

`HTML
% for student in classes %
<tr>
<td>student.fName</td>
<td>student.courses</td>
</tr>
% endfor %

To illustrate my webpage looks like this:

student1 course1,course2
student2 course3,course4

And I want it to look like this:

student1 course1
 course2
student2 course3
 course4

Also, using

GROUP_CONCAT(classes.courseTitle SEPARATOR "<br>")

or any other substitution results in:

student1 course1<br>course2
student2 course3<br>course4

Twig automatically escapes your variables to protect against XSS attacks, which is why using the <br> tags wasn't working. Using the GROUP_CONCAT(classes.courseTitle SEPARATOR "<br>"), update your twig to use the raw filter:

% for student in classes %
<tr>
<td>student.fName</td>
<td>student.courses</td>
</tr>
% endfor %

Update

Using the raw filter could produce other issues depending on the characters in courses. A better approach would be to use a character you can split on as the separator in group_concat, then split on that character and loop each course in twig. For example, if you used a semicolon as the character separator,

Your group concat would look like this:

GROUP_CONCAT(classes.courseTitle SEPARATOR ';')

And your twig would like this:

% for student in classes %
<tr>
 <td> student.fName </td>
 <td>
 split(';') %
 course <br>
 % endfor %
 </td>
</tr>
% endfor %

given a generalized table:

 myTable
=========
foo | bar
----+----
foo1|bar1
foo2|bar2
foo1|bar3
foo2|bar4

SQL

SELECT foo,
GROUP_CONCAT(bar SEPARATOR "@@@") AS bars
FROM myTable
GROUP BY foo

HTML

% for foobar in myTable %
<tr>
 <td>foobar.foo</td>
 <td>
 % for bar in foobar.bars
 bar<br>
 % endfor %
 </td>
</tr>
% endfor %

webpage presentation

foo1 bar1
 bar3
foo2 bar2
 bar4