how to group result in subgroups

[dannyp2010]

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, cars - then organize the results based upon the country, books, cars and total.
Here is my dbtable:
Id Country Books Book_price Cars Car_price
1 Germany Zorro 5 Bmw 300
2 Germany Tarzan 10 Mercedes 500
3 Italy Zorro part 2 15 Alfa 200
4 Italy Zorro part 3 55 Fiat 100
And I want the result to show like this in a table:
-------------------------------
Germany
-------------------------------
Books:
- Zorro 50
- Tarzan 10
-------------------------------
Total books 60
-------------------------------
Cars :
- Bmw 300
- Mercedes 500
-------------------------------
Total cars: 800
-------------------------------
Total Germany: 860
-------------------------------

Italy
-------------------------------
Books:
- Zorro part2 15
- Zorro part3 25
-------------------------------
Total books 40
-------------------------------
Cars :
- Alfa 200
- Fiat 100
-------------------------------
Total cars: 300
-------------------------------
Total Italy: 340
-------------------------------

Total countries 1200
-------------------------------
Thank you

[Canabeez]

Here, hope this helps you.

Just do some updates to implement.

Good luck.

[code="php"]
$link = mysql_connect( ... );
mysql_select_db ( ... );

$result = mysql_query("
SELECT `Id`, `Country`, `Books`, `Book_price`, `Cars`, `Car_price` FROM 'tableName';
", $link);

$data = array();

while($row = mysql_fetch_ass oc($result)){
if(!$data[$row['Country']]){
$data[$row['Country']] = array();
$data[$row['Country']]['cars'] = array();
$data[$row['Country']]['books'] = array();
}

$data[$row['Country']]['cars'][] = array("name" => $row['Cars'], "price" => $row['Car_price']);
$data[$row['Country']]['books'][] = array("name" => $row['Books'], "price" => $row['Book_price']);
}

echo "<!-- " . var_dump($data) . " -->";

foreach($data as $country => $countryData){
$totalBooks = 0;
$totalCars = 0;

echo "<h1>{$country} </h1>";

echo "<h2>Books</h2><table>";
foreach($countr yData['books'] as $book){
echo "
<tr>
<td>{$book['name']}</td>
<td>{$book['price']}</td>
</tr>
";
$totalBooks = $totalBooks + (int)$book['price'];
}
echo "
<tr>
<td>Total Books</td>
<td>{$totalBook s}</td>
</tr>
</table>
";

echo "<h2>Cars</h2><table>";
foreach($countr yData['cars'] as $car){
echo "
<tr>
<td>{$car['name']}</td>
<td>{$car['price']}</td>
</tr>
";
$totalCars = $totalCars + (int)$car['price'];
}
echo "
<tr>
<td>Total Cars</td>
<td>{$totalCars }</td>
</tr>
</table>
";

$totalCountry = $totalBooks + $totalCars;

echo "<strong>To tal {$country} {$totalCountry} </strong>";
}
[/code]

[dannyp2010]

Thank you. This script is exactly what i needed. It works.
If i had a hat i'd take it off...

[Canabeez]

No problem.

[dannyp2010]

Now I have another problem with subgroups...
My dbtable looks like this:
+----+----------+-------+---------------+
| 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 for your help

[Canabeez]

It's the same logics, just remove the cars.

[dannyp2010]

Thank you very much.

[Canabeez]

No problem.