By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,706 Members | 1,105 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,706 IT Pros & Developers. It's quick & easy.

how to group result in subgroups

P: 4
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
Sep 9 '10 #1

✓ answered by Canabeez

Here, hope this helps you.

Just do some updates to implement.

Good luck.

Expand|Select|Wrap|Line Numbers
  1.         $link = mysql_connect( ... );
  2.     mysql_select_db( ... );
  3.  
  4.     $result = mysql_query("
  5.         SELECT `Id`, `Country`, `Books`, `Book_price`, `Cars`, `Car_price` FROM 'tableName';
  6.     ", $link);
  7.  
  8.     $data = array();
  9.  
  10.     while($row = mysql_fetch_assoc($result)){
  11.         if(!$data[$row['Country']]){
  12.             $data[$row['Country']] = array();
  13.             $data[$row['Country']]['cars'] = array();
  14.             $data[$row['Country']]['books'] = array();
  15.         }
  16.  
  17.         $data[$row['Country']]['cars'][] = array("name" => $row['Cars'], "price" => $row['Car_price']);
  18.         $data[$row['Country']]['books'][] = array("name" => $row['Books'], "price" => $row['Book_price']);
  19.     }
  20.  
  21.     echo "<!-- " . var_dump($data) . " -->";
  22.  
  23.     foreach($data as $country => $countryData){
  24.         $totalBooks = 0;
  25.         $totalCars = 0;
  26.  
  27.         echo "<h1>{$country}</h1>";
  28.  
  29.         echo "<h2>Books</h2><table>";
  30.         foreach($countryData['books'] as $book){
  31.             echo "
  32.                 <tr>
  33.                     <td>{$book['name']}</td>
  34.                     <td>{$book['price']}</td>
  35.                 </tr>
  36.             ";
  37.             $totalBooks = $totalBooks + (int)$book['price'];
  38.         }
  39.         echo "
  40.                 <tr>
  41.                     <td>Total Books</td>
  42.                     <td>{$totalBooks}</td>
  43.                 </tr>
  44.             </table>
  45.         ";
  46.  
  47.         echo "<h2>Cars</h2><table>";
  48.         foreach($countryData['cars'] as $car){
  49.             echo "
  50.                 <tr>
  51.                     <td>{$car['name']}</td>
  52.                     <td>{$car['price']}</td>
  53.                 </tr>
  54.             ";
  55.             $totalCars = $totalCars + (int)$car['price'];
  56.         }
  57.         echo "
  58.                 <tr>
  59.                     <td>Total Cars</td>
  60.                     <td>{$totalCars}</td>
  61.                 </tr>
  62.             </table>
  63.         ";
  64.  
  65.         $totalCountry = $totalBooks + $totalCars;
  66.  
  67.         echo "<strong>Total {$country} {$totalCountry}</strong>";
  68.     }
  69.  

Share this Question
Share on Google+
7 Replies


Canabeez
100+
P: 126
Here, hope this helps you.

Just do some updates to implement.

Good luck.

Expand|Select|Wrap|Line Numbers
  1.         $link = mysql_connect( ... );
  2.     mysql_select_db( ... );
  3.  
  4.     $result = mysql_query("
  5.         SELECT `Id`, `Country`, `Books`, `Book_price`, `Cars`, `Car_price` FROM 'tableName';
  6.     ", $link);
  7.  
  8.     $data = array();
  9.  
  10.     while($row = mysql_fetch_assoc($result)){
  11.         if(!$data[$row['Country']]){
  12.             $data[$row['Country']] = array();
  13.             $data[$row['Country']]['cars'] = array();
  14.             $data[$row['Country']]['books'] = array();
  15.         }
  16.  
  17.         $data[$row['Country']]['cars'][] = array("name" => $row['Cars'], "price" => $row['Car_price']);
  18.         $data[$row['Country']]['books'][] = array("name" => $row['Books'], "price" => $row['Book_price']);
  19.     }
  20.  
  21.     echo "<!-- " . var_dump($data) . " -->";
  22.  
  23.     foreach($data as $country => $countryData){
  24.         $totalBooks = 0;
  25.         $totalCars = 0;
  26.  
  27.         echo "<h1>{$country}</h1>";
  28.  
  29.         echo "<h2>Books</h2><table>";
  30.         foreach($countryData['books'] as $book){
  31.             echo "
  32.                 <tr>
  33.                     <td>{$book['name']}</td>
  34.                     <td>{$book['price']}</td>
  35.                 </tr>
  36.             ";
  37.             $totalBooks = $totalBooks + (int)$book['price'];
  38.         }
  39.         echo "
  40.                 <tr>
  41.                     <td>Total Books</td>
  42.                     <td>{$totalBooks}</td>
  43.                 </tr>
  44.             </table>
  45.         ";
  46.  
  47.         echo "<h2>Cars</h2><table>";
  48.         foreach($countryData['cars'] as $car){
  49.             echo "
  50.                 <tr>
  51.                     <td>{$car['name']}</td>
  52.                     <td>{$car['price']}</td>
  53.                 </tr>
  54.             ";
  55.             $totalCars = $totalCars + (int)$car['price'];
  56.         }
  57.         echo "
  58.                 <tr>
  59.                     <td>Total Cars</td>
  60.                     <td>{$totalCars}</td>
  61.                 </tr>
  62.             </table>
  63.         ";
  64.  
  65.         $totalCountry = $totalBooks + $totalCars;
  66.  
  67.         echo "<strong>Total {$country} {$totalCountry}</strong>";
  68.     }
  69.  
Sep 9 '10 #2

P: 4
Thank you. This script is exactly what i needed. It works.
If i had a hat i'd take it off...
Sep 10 '10 #3

Canabeez
100+
P: 126
No problem.
Sep 10 '10 #4

P: 4
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
Sep 13 '10 #5

Canabeez
100+
P: 126
It's the same logics, just remove the cars.
Sep 13 '10 #6

P: 4
Thank you very much.
Sep 14 '10 #7

Canabeez
100+
P: 126
No problem.
Sep 15 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.