如何在php中将结果分组 [英] how to group result in subgroups in php

问题描述

大家好.当我尝试学习一些PHP和mySQL时，遇到了一个我很难解决的问题.我需要一个PHP脚本来查询mySQL数据库中的国家，书籍，book_price.这是我的dbtable:

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 |
+----+------+-----------+------------------------+

我要根据国家/地区，书籍，总书籍，总国家/地区和TOTAL GEN(这是所有国家/地区的总和)来组织结果，结果显示如下:

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  |
+----------------------------------------------------+

谢谢

推荐答案

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

这篇关于如何在php中将结果分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！