MYSQL and PHP group results by field data

I am trying to create reports based on data from a log in the database that looks like:

id |    student    |    type     |    marks

1       23494         CAT1          50

2       23495         CAT1          20

3       23494         CAT2          35

4       23495         MIDTERM   40

My select statement so far looks like this:

$res = @mysqli_query ($dbc, "SELECT id, student, type, GROUP_CONCAT(marks) AS mark, GROUP_CONCAT(type) AS types FROM log WHERE class = '1' AND term = '2' GROUP BY student DESC");

// Fetch and print all the records....<br>
while ($row = mysqli_fetch_array($res, MYSQLI_ASSOC)) {

        echo '<tr>
            <td align="left">'. $row['student'] .'</td>';

        //$exams = split(",", $row['exams']); // 4,3,1,2
        $marks = split(",", $row['mark']); // 10,20,40,50

        foreach( $marks as $mark ) {
             echo '
                   <td align="left">' . $mark . '</td>
                ';
        }

        echo '</tr>';

} //End LOOP

//Then i end table

So far the data displays like so:

STUDENT | CAT1 | CAT2 | MIDTERM

23494          50          35

23495          2开发者_C百科0          40

The problem is that the code is not arranging 'marks' according to 'type' (look at MIDTERM output for id 4 and corresponding display).

Question:

How do i display the results by student, followed by marks in the appropriate cell/group like so:?

STUDENT | CAT1 | CAT2 | MIDTERM

23494          50          35

23495          20                       40

Thanks in Advance Guys.

First, try to keep logic away from layout. It's generally good practice to first gather the data you need, and then display it.

Using GROUP_CONCAT can make things more complicated, since you do not know how many results you will get for each student, nor will you be able to easily identify which marks are of what type.

With that in mind I've created a solution. You'll still need to extend the query of course.

$query = 'SELECT student, type, marks FROM log';
$res = mysqli_query($query);
$studentMarks = array();

while ($row = mysqli_fetch_array($res, MYSQLI_ASSOC))
{
    $studentMarks[$row['student']][$row['type']] = $row['marks'];
}

// Now $studentMarks should look like:
// $studentMarks = array(
//    23494 => array('CAT1' => 50, 'CAT2' => 35)
//  , 23495 => array('CAT1' => 20, 'MIDTERM' => 40)
// );    

echo '<table><thead><tr>';
echo '<td>Student</td><td>CAT1</td><td>CAT2</td><td>MIDTERM</td>';
echo '</tr></thead><tbody>';

foreach($studentMarks as $studentId => $marks)
{
    echo '<tr>';
    echo '<td>', $studentId, '</td>';
    echo '<td>', (isset($marks['CAT1']) ? $marks['CAT1'] : '&nbsp;'), '</td>';
    echo '<td>', (isset($marks['CAT2']) ? $marks['CAT2'] : '&nbsp;'), '</td>';
    echo '<td>', (isset($marks['MIDTERM']) ? $marks['MIDTERM'] : '&nbsp;'), '</td>';
    echo '</tr>';
}
echo '</tbody></table>';