MYSQL and PHP group results by field data

Question

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 20 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.

Arjan · Accepted Answer · 2011-08-08 16:50:17Z

1

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>';

edited Aug 8, 2011 at 16:50

answered Aug 7, 2011 at 21:26

Arjan

9,8941 gold badge33 silver badges43 bronze badges

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

4 Comments

Brian Over a year ago

Thank you for your reply @Arjan, I am currently testing your logic. Though my first test using your code returns only $studentId. Let me explore a bit more and I will get back to you.

Brian Over a year ago

I still can't get your logic to display the marks as associated to type. Any other ideas, please?

Arjan Over a year ago

I'm not sure why the code doesn't seem to work. Just to be on the safe side, I've added a few comment lines in the code to show what $studentMarks should look like (based on the question). When it looks like that, the code will work. If you don't understand parts of the code, please explain which parts need clarification and I'll provide that.

Brian Over a year ago

Thanks so much. I was able to get your code working. I was initially lost at the foreach output but it was just an error in my part while integrating my database. I can now safely say problem solved. Cheers.

Collectives™ on Stack Overflow

MYSQL and PHP group results by field data

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related