how to group result in subgroups in php

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

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

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

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

Check this out !

<?php
include ("config.php");

function render($price3) {
  $output = "<td align='right'>".number_format($price3->book_price, 0, ',', '.')."</td>";
  $output .= "</tr>";
  return $output;
}
echo "<table border='1'>";
echo "<tr>
    <th>books</th>
    <th>price</th>
  </tr>";
$result = mysql_query("select id, country, books, book_price from test") or die(mysql_error());
$set = array();
while ($record = mysql_fetch_object($result)) {
  $set[$record->country][$record->books][] = $record;
}
$sum_country = 0; 
foreach ($set as $country => $price1) {
    echo "<tr>
        <td align='center'>{$country}</font></td>
        <td></td>
        </tr>";

  foreach ($price1 as $books => $price2 ) {
    echo "<tr>
        <td>{$books}</td>";  

      foreach ($price2 as $price3) {
        echo render($price3);   
      }

         $sum_books = 0;
  foreach($price2 as $grbooks){
      $sum_books += $grbooks->book_price;
  }   

    echo "<tr><td>Total {$books}</td>
    <td align='right'>".number_format($sum_books, 0, ',', '.')."</td>
    </tr>";
    }

      $sum_country += $sum_books;

    echo "<tr<td>Total {$country}</td>
    <td align='right'>".number_format($sum_country, 0, ',', '.')."</td>
    </tr>";
    }
  echo "</table>";