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

Try:

 SELECT country, books, SUM(price)
 FROM sales
 GROUP BY country, books WITH ROLLUP;

Rows where books is null will return total for each country Row where Country and Books are null return total for whole query

Answer 2

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

Answer 3

$conn = mysql_connect("localhost", "mysql_user", "mysql_password");
mysql_select_db("mydbname");

$sql    = 'SELECT * FROM table_name';
$result = mysql_query($sql);

$data = array();
while ($row = mysql_fetch_assoc($result)) {
    if ( empty($data[ $row['Country'] ]) ) {
        $data[ $row['Country'] ] = array();
    }

    if ( empty( $data[ $row['Country'] ][ $row['Books'] ] ) ) {
        $data[ $row['Country'] ][ $row['Books'] ] = array();
    }

    $data[ $row['Country'] ][ $row['Books'] ][] = $row['Book_price'];
}


$totalSum = 0;
foreach ( $data as $country => $books ) {

    echo '<b>' . $country . '</b><br/>';

    $totalCountry = 0;
    foreach ( $books as $book => $prices ) {
        $sum = array_sum( $prices );

        echo '<u>' . $book . '</u><br/>';

        echo implode(',', $prices) . '<br/>;

        echo 'Total ' . $book . ':' . $sum . '<br/>';

        $totalCountry += $sum;
    }


    echo 'Total ' . $country . ':' . $totalCountry . '<br/>';

    echo '<hr/>';

    $totalSum += $totalCountry;

}

echo 'TOTAL GEN: ' . $totalSum;