php list into a table

Question

I asked a question yesterday and the answer I got has answered the first part of my problem - I now have a query that generates a list similar to this:

fname, sname, uname, assignment, task, grade
joe, blogs, joe.blogs, 1, 1, 52
joe, blogs, joe.blogs, 1, 2, 58
jim, blogs, jim.blogs, 1, 1, 95
jim, blogs, jim.blogs, 1, 2, 86
amy, blogs, amy.blogs, 1, 1, NULL
amy, blogs, amy.blogs, 1, 2, 76

an so on. It takes the most recent grade from the list and still works where a student hasn't received a grade for task.

What I want next is to produce a table that looks similar to this:

      | Assignment 1    | Assignment 2    |
 Name |-----------------|-----------------|
      | Task 1 | Task 2 | Task 1 | Task 2 |
===========================================
Joe.. |  52    |  58    |  ..    |  ..    |
Jim.. |  95    |  86    |  ..    |  ..    |
Amy.. |  U     |  76    |  ..    |  ..    |
...

again, I've done similar things before, but I know they were horribly inefficient - Ideally I'd like to be able to produce this table with a single pass of the array.

Thanks.

Answer 1

What you're gonna need to do is add columns while you're iterating through the rows. It might be efficient to execute a small query first, selecting the columns:

SELECT
  *
FROM assignment_tasks
ORDER BY
  Assignment_ID ASC,
  Task_ID ASC;

Then adapt the query loading the array you described to order by user, assignment, task. This way, the values in the array are in the same order as in the table (row by row, left to right). If a value is missing (or NULL, doesn't matter), print U.

Of course, this goes with the assumption that the number of assignments and tasks is small compared to the number of entries in the array.

Answer 2

Take a look at the group by clause. I personally don't use it because I find that I still have to end up foreaching over the results set to create a nested array like I want - but it might be what you're looking for. Here's a link that uses sums so it's not ideal, but it shows you a little more information on the SQL.