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.

Can it be assumed that your list/array will be sorted by name, assignment, task, and that all values that don't exist will be returned with NULL, ala Amy's Assignment 1 - Task 1 assignment? — tschaible
– tschaible, Commented Jun 16, 2009 at 10:41
sorry, my data above doesn't make that obvious! the data comes sorted by surname, forename, assignment and then task, so everything is grouped together. — MalphasWats
– MalphasWats, Commented Jun 16, 2009 at 11:49

Martijn · Accepted Answer · 2009-06-16 11:23:25Z

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.

answered Jun 16, 2009 at 11:23

Martijn

5,6915 gold badges41 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andrew Dunkman · Accepted Answer · 2009-06-23 23:06:48Z

1

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.

answered Jun 23, 2009 at 23:06

Andrew Dunkman

1,1312 gold badges8 silver badges18 bronze badges

Collectives™ on Stack Overflow

php list into a table

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related