mysql - how to group result in subgroups in php

Question

Hey everyone. While I'm trying to learn some PHP and mySQL, I ran into a problem that I've had some difficulty in solving. I need a PHP script that queries mySQL database for country, books, book_price. Here is my dbtable:

+----+-------+----------+------------------------+
| id |Country|Books     | Book_price             |
+----+-------+----------+------------------------+ 
|  1 | USA  | Zorro     |   10 |
|  2 | USA  | Zorro     |   20 |
|  3 | USA  | Zorro     |   50 |
|  4 | USA  | Leon      |  200 |
|  5 | USA  | Leon      |  240 |
|  6 | ITALY| Tarzan    |   70 |
|  7 | ITALY| Tarzan    |   30 |
|  8 | ITALY| Tarzan    |  100 |
|  9 | ITALY| Cobra     |  300 |
| 10 | ITALY| Cobra     |  320 |
| 11 | ITALY| Cobra     |  350 |
+----+------+-----------+------------------------+

I want to organize the results based upon the country, books, total book, total country and TOTAL GEN (which is sum of all total country) and the result to show like this:

+----------------------------------------------------+
| USA                                                |
+----------------------------------------------------+ 
|     Zorro    10  |
|              20  |
|              50  |
+----------------------------------------------------+
| Total Zorro:  80   |
+----------------------------------------------------+
|      Leon  200  |
|            240  |
+----------------------------------------------------+
| Total Leon:440   |
+----------------------------------------------------+
|Total USA:       520   |
+----------------------------------------------------+
|ITALY     |
+----------------------------------------------------+ 
|     Tarzan      70  |
|                 30  |
|                100  |
+----------------------------------------------------+
| Total Tarzan:200  |
+----------------------------------------------------+
|         Cobra  300  |
|                320  |
|                350  |
+----------------------------------------------------+
| Total Cobra: 970  |
+----------------------------------------------------+
|Total ITALY:       1170  |
+----------------------------------------------------+
|TOTAL GEN:       1690  |
+----------------------------------------------------+

Thank you

Answer 1

Use additional variables that hold the last country/book and their subtotals:

$last = array('Country' => null, 'Books' => null);
$subtotals = array('Country' => 0, 'Books' => 0);
echo '<table>';
while ($row = mysql_fetch_assoc($result)) {
    if ($row['Books'] !== $last['Books']) {
        if (!is_null($last['Books'])) {
            echo '<tr><td colspan="2">Total '.$last['Books'].': '.$subtotals['Books'].'</td></tr>';
        }
        $last['Books'] = $row['Books'];
        $subtotals['Books'] = $row['Book_price'];
    } else {
        $subtotals['Books'] += $row['Book_price'];
    }
    if ($row['Country'] !== $last['Country'])) {
        if (!is_null($last['Country'])) {
            echo '<tr><td colspan="2">Total '.$last['Country'].': '.$subtotals['Country'].'</td></tr>';
        }
        echo '<tr><th colspan="2">'.$row['Country'].'</th></tr>';
        $last['Country'] = $row['Country'];
        $subtotals['Country'] = $row['Book_price'];
    } else {
        $subtotals['Country'] += $row['Book_price'];
    }
    echo '<tr><td>'.$row['Books'].'</td><td>'.$row['Book_price'].'</td></tr>';
}
echo '</table>';