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
Here, hope this helps you.
Just do some updates to implement.
Good luck. -
$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_assoc($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($countryData['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>{$totalBooks}</td>
-
</tr>
-
</table>
-
";
-
-
echo "<h2>Cars</h2><table>";
-
foreach($countryData['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>Total {$country} {$totalCountry}</strong>";
-
}
-
7 2747
Here, hope this helps you.
Just do some updates to implement.
Good luck. -
$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_assoc($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($countryData['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>{$totalBooks}</td>
-
</tr>
-
</table>
-
";
-
-
echo "<h2>Cars</h2><table>";
-
foreach($countryData['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>Total {$country} {$totalCountry}</strong>";
-
}
-
Thank you. This script is exactly what i needed. It works.
If i had a hat i'd take it off...
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
It's the same logics, just remove the cars.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: X |
last post by:
I have a varchar field which contains a comma delimeted set of data. I
am trying to us the ELT function to extract the first part of the group.
Problem is, ELT does not seem to work when the data...
|
by: Chad Richardson |
last post by:
I've always been mistified why you can't use a column alias in the group by
clause (i.e. you have to re-iterate the entire expression in the group by
clause after having already done it once in the...
|
by: Simon |
last post by:
Hi,
is there any news group for asp.net developers?
I can't find it.
Thank you,
Simon
|
by: Margaret |
last post by:
I have a group of data that is hierarchical. Level N reports to
level N-1 reports to.....Level 2 reports to level 1. I need to show
this data on my Access report in such a manner:
Level 1 --...
|
by: Pea |
last post by:
Hello,
I have a report with groups, subgroups and counts. I'm trying to give
a total for each group and used the RunningSum property by group. But
I find that it's running throughout the report....
|
by: Wire |
last post by:
Hello experts,
I have a little group by problem, hope you can help me.
I have a lot off records which I want to group by "HOUR(timefield)"
example:
01-02-2005 13:28:00 25.0
01-02-2005 ...
|
by: wheel |
last post by:
I'm not sure if this is the right venue for Plone questions, if not,
could someone ref me to a better one? The discussion groups on the plone
site are mailing lists and I'd rather not subscribe...
|
by: jaypman |
last post by:
Hi, I'm trying to use C# to query Active Directory for the first time. Been thinking about this problems for awhile now but can't figure it out.
How would I get a list or datatable of members of...
|
by: daanoz |
last post by:
hey
I'm trying to make a query which selects all rows that are not linked to the a certain number, i already managed to do this with a subquery, but this slows down the application, mainly caused...
|
by: newVBNETuser |
last post by:
I want to get the follow vbScript to work in VB.NET
Set objRun = CreateObject("wscript.Shell")
strUserName = objRun.ExpandEnvironmentStrings("%USERNAME%")
Dim strComputer, member, strUsername,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |