PDA

View Full Version : mysql query to count


2bz2p
10-20-2008, 06:07 PM
Hi,

I want to count how how many times a user has used one perticular plane, I also do not know which planes they have used or which ones they have a choice on.

So I know that SQL will do this (i think) but I do not know how to write the query.


Example

database:
Jayhawk
v22
C-130
Jayhawk
v22

out put
jayhawk used 2 times
v22 used 2 times
c-130 used 1 time.


Thank you
2b

edbr
10-21-2008, 01:59 AM
how is your database constructed?

2bz2p
10-21-2008, 02:09 AM
right now thats in a table that is for filed pilot reports so there is like

columns
route
aircraft
pilot
dep time
ariv time
dur time

what not everything dealing with user enter info about their flight.

thanks again
2b

davidj
10-21-2008, 05:43 AM
SELECT COUNT(aircraft) AS aircraftcount

WHERE pilot = $var and aircraft = $varAircraft

GROUP BY aircraft



this can be anything... >> aircraftcount as its an alias and you would use this to reference your value

2bz2p
10-21-2008, 12:39 PM
Your the man,

Thanks
2b

2bz2p
10-21-2008, 07:42 PM
I got it to work!! Its a little sloppy but I will clean that up later.

Thanks DJ
2b


function fav_plane($callsign){
$sql = "SELECT aircraft, COUNT(aircraft) AS times_used FROM filed_pireps
WHERE pilot = '$callsign' GROUP BY aircraft ORDER BY times_used DESC LIMIT 1";
new core();
core::query($sql);

echo'<table>';
do {
echo'
<tr>
<td>Most Popular Aircraft:</td>
<td>';
echo $data['aircraft'];
echo '</td>
</tr>
<tr>
<td>Times_used:</td>
<td>';
echo $data['times_used'];
echo '</td>
</tr>';

} while ($data = mysql_fetch_assoc($this->result));
echo' </table>';


return;
}

davidj
10-22-2008, 05:48 AM
assign your html to a var


$html .= "<table>";
$html .= "<tr>";
$html .= "<td>Most Popular Aircraft:</td>";
$html .= "<td>".$data['aircraft']."</td>";


then return the $html var

2bz2p
10-22-2008, 01:15 PM
$html .= "<table>";
$html .= "<tr>";
$html .= "<td>Most Popular Aircraft:</td>";
$html .= "<td>".$data['aircraft']."</td>";


How is it able to be the same variable with different values is that because of the . ?

Ahh its so you can return the $html and it will run the entire html code, saw it in one of your examples.

Thanks
2b

davidj
10-22-2008, 01:19 PM
as you know the dot is used to concatinate so using it as above just adds a string to the back of the current contents

2bz2p
10-22-2008, 01:50 PM
Thats what I thought I just want to verify that.

Thanks
2b

davidj
10-22-2008, 02:40 PM
NOTE: when using this method


$html .= "<table>\n";
$html .= "<tr>\n";
$html .= "<td>Most Popular Aircraft:</td>\n";
$html .= "<td>".$data['aircraft']."</td>\n";


remember to add a \n to the back of each line or the table html will work but will be one long string in the source. Use the \n control char to add a new line so the code looks neat and easy to read when debugging

2bz2p
10-22-2008, 03:15 PM
Though I dont know if I want to go that way because now I have multiple pages with different info and if I start adding the html to the functions/methods then I have to include two classes and now its in a conflic because both classes are runing the core.class.php as in include in them selves. or just eleminate the core class from the others and include it seperately on each page?

Hope that make sence
2b

davidj
10-22-2008, 03:41 PM
take the path of least resistance