Show results from database

[flyboeing]

Hello all,

 

I am making a database with all the Boeing deliveries sorted by month. I made a index with all the months and when you click on a month you will get a full list of the type and quantity of aircraft that has been deliverd.

 

I have two problems and I can't figure it out.

The first problem I have, is that I want to show the visitors which airline has bougt which type of aircraft and how many.

The second problem I have is a want to show them the total amount of deliveries, not just per type of aircraft, but a total number.

 

Here are some shots of my problem and what I have:

This is how my MYSQL-database lookes like. The columns I have are: id, day, month, year, airline, aircraft, amount (just a simple translation of the Dutch words ).

As you can see, there are some airlines that received one type of aircraft two times (TUI Travel PLC has received 2x Boeing 737-800). Keep this in mind, I will get back to this.

 

This is the table on my website. The first column shows the type of aircraft. The secon column shows us how many of that type has been deliverd. In the last column I want to have all the airlines that has received that type of aircraft. As I mentioned above, TUI Travel PLC has received 2x B737-800, so in the row of B737-800 I want to have TUI Travel PLC (2x). If the airline received more then 1 aircraft, I would like to have (.....x) (and the amount on the dots --> TUI example: 2 aircraft, so 2x). So you will get TUI Travel PLC (2x).

 

This is the code I have so far (sorry I am a real noobie in PHP/MYSQL so my programming ain't that great )

 

<?php
mysql_connect(".....", ".....", ".....") or die(mysql_error());
mysql_select_db(".....") or die(mysql_error());

$maand = $_GET['maand'];
$jaar = $_GET['jaar'];


$query = "SELECT aircraft, jaar, maatschappij, COUNT(maatschappij) FROM deliveries_orders_boeing WHERE maand = '$maand' AND jaar = '$jaar' GROUP BY aircraft"; 

$result = mysql_query($query) or die(mysql_error());

// Print out result
echo "<br /><h3>Boeing deliveries ".$maand." ".$jaar."</h3><br />
<table border='0' width='700'>";
while($row = mysql_fetch_array($result)){

echo "<tr><td id='aircraft'>". $row['aircraft']. "</td><td id='aantal'>". $row['COUNT(maatschappij)'] ."</td><td id='maatschappijen'>different companies</td></tr>";	
}
echo "<tr><td id='totaal'>Totaal</td><td id='totaal_aantal'>....</td><td></td></tr></table>";

?>
</td>
</tr>
</table>

 

Maybe a little confusing, but I hope someone can help me with this.

 

Kind regards,

Flyboeing

[litebearer]

http://www.tizag.com/mysqlTutorial/mysqlsum.php

[flyboeing]

Thanks for the link.

That solved just one of my problems, now the other one

[Anon-e-mouse]

Thanks for the link.

That solved just one of my problems, now the other one

 

Explain clearly what you secondly want.. is it just to have the number of deliveries put before the "x" to make something like "2x"?

[flyboeing]

I will try to explain.

 

In the last column I want to show all the airlines that ordered the aircraft that is in the first cell. If you look at row 1 of secon image, you see in the first cell B737-900ER. In the column right of it, you see the amount (3 aircraft). In the last column I want to show all the airline names that ordered that type of aircraft. For example, Delta 1 aircraft and US Airways 2 aircrafts of that type, I would like to show it as "Delta, US Airways (2x)" (without the "")

 

Here is a image:

 

As you can see, I have a table with the delivery date (day, month, year), a column containing the airline name, an other column with the type of aicraft and another with the amount.

The table below the red arrow is how I would like it to have. Showing the the type and the amount is working, but the last column, the airlines, I don't know how. As you can see, if an airline ordered more then 1 aircraft, it will gets the ..x (a number on the dots) behind the airline name (look at US Airways and KLM).

 

I hope this will help.

[flyboeing]

I tried several things, but I can't get it to work

 

This is the code I use:

<?php
mysql_connect("xxxx", "xxxx", "xxxx") or die(mysql_error());
mysql_select_db("xxxx") or die(mysql_error());

$maand = $_GET['maand'];
$jaar = $_GET['jaar'];

$query = "SELECT aircraft, jaar, maatschappij,aantal, COUNT(maatschappij), COUNT(aantal) FROM deliveries_orders_boeing WHERE maand = '$maand' AND jaar = '$jaar' GROUP BY aircraft"; 

$result = mysql_query($query) or die(mysql_error());

// Print out result
echo "<br /> <h3>Boeing deliveries ".$maand." ".$jaar."</h3><br />
<table border='0' width='700'><tr><td colspan='3'><img src='images/boeing_deliveries/".$maand."_$jaar.jpg'></td></tr><tr><td id='copyright'>© paineairport.com</td></tr>
";
while($row = mysql_fetch_array($result)){

echo "<tr><td id='aircraft'>". $row['aircraft']. "</td><td id='aantal'>". $row['COUNT(maatschappij)'] ."
</td>
<td id='maatschappijen'><?php echo 'test'; ?>". $row['maatschappij']. "</td>
</tr>";	
}
echo "<tr><td id='totaal'>Totaal</td><td id='totaal_aantal'>
aantal ". $row['COUNT(aantal)'] ." 
</td><td></td></tr></table>";

?>
</td>
</tr>
</table>

 

Here you can see the result:

http://www.pro-aviation.nl/index.php?option=com_chronocontact&chronoformname=deliveries_orders_boeing_maand&maand=06&jaar=2011

 

As you can see, I only get the first airline with that aircraft. For example, On the first row you have the 737-900ER. There are 3 records in the mysql database with this aircraft and 2 airlines are using it. Only one is shown.

 

How can I put every airline that is using the aircraft in the last column?

[cpd]

You need to use either a Stored Procedure or multiple queries with the logic in your PHP. You can not accomplish this through a simple query. I did, after playing around, create this for you but it's only half of what you want. The other half isn't possible in a simple query.

 

SELECT `aircraft`,COUNT(`aircraft`) AS `Amount` FROM `deliveries_orders_boeing` GROUP BY `aircraft`;