Counting each instance of characters

[flynryan692]

Hello, I am working on an aviation website and on a part of my site I want it to display information regarding a pilot log book. Currently I just query all the flights for a member and use substr to get the first three digits (typically an ICAO code) then run it past a few IF's so that if there is a private registration (like N612ER, G-PCH, A6-EDG etc) I can get the right character (N, G, or A6) and then I use the character to display an image. It counts how many times each three character group is used. So lets say a member has 15 Air Canada flights (ACA), 5 Southwest flight (SWA), 3 British Airways (BAW), and 2 flights under N622ER (N62) it will display the logo for each airline, or a "private registration" image for each country after is runs pasts the IF's and turns "N62" into "N". What I would like to do is expand the capability of this so that it will count how many times the characters were used. Right now if they have three different American private registrations it displays them separately because they are actually N62 or N54, I want to group it so that all American registrations are together, British together etc. I also want to be able to support military call signs, so something like EAGLE13 (I want the word EAGLE) which my IF's that check the characters will do.

 

So, tl;dr if I change my query to get all the members callsigns how would I then count up each instance of an airline, private registration, or military call sign and display the top 5, highest to lowest as I do now?

 

Here is what the code I want to change currently looks like...

<?php 
			$sql = "SELECT COUNT(*) AS Nr, SUBSTR(Callsign, 1, 3) as Airline FROM {$dbprefix}Reports WHERE PilotID=$userid GROUP BY Airline ORDER BY Nr DESC LIMIT 5";
			$result = mysql_query($sql);
			while ($row = mysql_fetch_array($result))
			{
    			$nr = $row["Nr"];
    			$airline = $row["Airline"];
    			if ($airline[2] == '-'){//is the 3rd char a hyphen? 
    			$icao = substr($airline,0,2); 
			}elseif ($airline[1] == '-'){//is the 2nd char a hyphen?
    			$icao = substr($airline,0,1);
			}elseif (is_numeric($airline[1])){//is the 2nd char a number? 
    			$icao = substr($airline,0,1); 
			}else{
  			$re1='((?:[a-z][a-z]+))';    //search term for a word?
  			if ($c=preg_match_all ("/".$re1."/is", $airline, $matches)){
      			$icao=$matches[1][0]; //1st occurance
  		 	}
			}
			$pathtofile = ($_SERVER['DOCUMENT_ROOT'] . "/cms/images/airlines/$icao.jpg");
			$exists = file_exists($pathtofile);
    			if (file_exists($pathtofile)) 
    				echo "<tr><th class=\"profile3\"><img src=\"http://www.website.org/cms/images/airlines/$icao.jpg\" /></th><td class=\"profile2\">$nr</td></tr>";
    			else
    				echo "<tr><th class=\"profile3\">$airline</th><td class=\"profile2\">$nr</td></tr>";
    			 }
    			?>

[kicken]

Use an array where the key of the array is the call sign and the value is the number of times it has been seen. Eg:

$flightCount = array();
foreach ($flights as $f){
   //Assume $f['callSign'] is your ACA, SWA, N, etc
   if (isset($flightCount[$f['callSign']])) $flightCount[$f['callSign']]++;
   else $flightCount[$f['callSign']] = 1;
}

After the loop $flightCount would contain something like:

array(
   [ACA] => 5
   [N] => 3
   [SWA] => 1
)

indicating 5 Air Canada, 3 Privates, and 1 Southwest.

[flynryan692]

Thank you (sorry for the late reply, I got busy with some other stuff). 

 

If I am understanding this correctly I should query all callsigns, which could be thousands in certain cases, and use it as the key of the array and the number the callsign was used as the value. I'm a bit confused as to how this works. Currently, the query is using a substr on each callsign so it only has the first three characters, but I no longer want it to function that way. Maybe I need to make a better example..

 

So lets say in a database I have the following flights: SWA1545, DAL1755, TANKR11, SWA388, N5439K, G-CHRE, N334R, DAL355, SWA2115

 

Now what I'd like to do is determine what airline each flight it is and count up how many times it was used. Each airline flight, such as the SWA ones, should only give me the characters SWA, the TANKR11 callsign should return TANKR, and the N5439K should return only N (I've sort of got this bit written already in my OP where it takes the variable $Airline and determines the code to use). Now I want to take the code and use it in a link to an image that is inside an HTML table, which is easy.

 

Next I want to count of each instance of a callsign, which will go to the right of the image. So, how many times was SWA used? How many times was N used? TANKR? etc and echo this inside an HTML table. The table rows are included in my OP to show how it is being used currently. 

 

Thank you for the help!

[rwhite35]

This is where PDO really excels.  You can make multiple queries on the database with little performance cost.  PDO is a prepared query statement that you apply variable data to with each query.

 

determine what airline each flight it is and count up how many times it was used

So you have a table of airline codes (SWA, DAL, TAN),  Your query would look something like this:

$sql = "SELECT COUNT(*) AS Nr, SUBSTR(Callsign, 1, 3) as Airline FROM {$dbprefix}Reports WHERE PilotID=? AND CallSign=? GROUP BY Airline ORDER BY Nr DESC LIMIT 5";

Each time through your would bind the $userid to PilotID and the $airlineCode to CallSign column(not sure of you tbl structure). The results set then would be only the records with matching $airlineCodes.  The results set can then be assigned to multi-dim array as Kicken suggest.  The array would look something like:

array (
  [0] => (
     [SWA] => (
            [0] => swa123
            [1] => swa345
            [2] => swa1524
     )
  )
 [1]=> (
     [DAL] => (
            [0] => dal321
            [1] => dal543
     )
  )
)

Where the outer array [0] would be each result set. 

Edited June 14, 2013 by rwhite35

[rwhite35]

This is where PDO really excels.  You can make multiple queries on the database with little performance cost.  PDO is a prepared query statement that you apply variable data to with each query.

So you have a table of airline codes (SWA, DAL, TAN),  Your query would look something like this:

$sql = "SELECT COUNT(*) AS Nr, SUBSTR(Callsign, 1, 3) as Airline FROM {$dbprefix}Reports WHERE PilotID=? AND CallSign=? GROUP BY Airline ORDER BY Nr DESC LIMIT 5";

Each time through your would bind the $userid to PilotID and the $airlineCode to CallSign column(not sure of you tbl structure). The results set then would be only the records with matching $airlineCodes.  The results set can then be assigned to multi-dim array as Kicken suggest.  The array would look something like:

array (
  [0] => (
     [SWA] => (
            [0] => swa123
            [1] => swa345
            [2] => swa1524
     )
  )
 [1]=> (
     [DAL] => (
            [0] => dal321
            [1] => dal543
     )
  )
)

Where the outer array [0] would be each result set. 

Have a look at this post, where I've given a simplified PDO example.

[flynryan692]

I will give this a shot and see what happens. Thank you!