[RESOLVED] total hours fields for each a/c?

homer09001

ok here is the situation im writing a management script for a virtual airline and i need the fleet page to get all the aircraft info from the aircraft database:

| ID | Type | Reg | Hours |

|001| 747 | G-BSPK| 10.23 |

|002| 747 | G-BSPL| 59.00 |

|003| 737 | G-BSPM| 19.03 |

|004| 737 | G-BSPN| 00.00 |

|005| 747 | G-BSFK| 210.23|

|006| 767 | G-BSGK| 10.23 |

I need a script to count the number of a certain type of aircraft and total the hours for that type of aircraft.

so it is outputed on a table like so:

|Aircraft Type | Total Hours | No. In Fleet |

| 737 | 19.03 | 2 |

| 747 | 279.46 | 3 |

| 767 | 10.23 | 1 |

i know how to count the number of records im just not sure how to total all of the hours for each aircraft type?

devinemke

SELECT SUM(Hours) AS sum, COUNT(*) AS count FROM planes GROUP BY Type

homer09001

then how would i print that into the table?

devinemke

homer09001 wrote:
then how would i print that into the table?

...that would depend entirely on which RDBMS you are using. if you are using mySQL you could use the following functions:
[man]mysql_query[/man]
[man]mysql_fetch_assoc[/man]

homer09001

i understand how to send the query to mysql but once its done the query how do i put the Count of the hours and the Count of aircraft type into separate variables.

Sorry im having a slow day

and in addition to that when i execure the query i get the following:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY Type' at line 1

homer09001

no problem sorted it

my code:

<h4>Sunward Aviation Fleet</h4>

<table cellspacing="1" bordercolor=#000000 bgcolor=#e5e5e5 cellpadding="2" border="2">
<tr><th width="48">Aircraft Type</th><th>Total Flight Hours</th><th>No. In Fleet</th></tr>
<?php
include "config.php3";
include "dbconnect.php3";

$query = "SELECT SUM(Hours) AS sum, COUNT(*) AS count, Type AS type FROM sa_aircraft GROUP BY Type";
$result = mysql_query($query)or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
    $type = $row["type"];
    $hours = $row["sum"];
    $numb = $row["count"];
echo "<tr><td>$type</td><td>$hours</td><td>$numb</td></tr>";

}
?>
</table>