NYCPHP Meetup

[Jon Baer]

ok ...

just as a follow up to my own question as i figured out how to do it with
hardly no PHP.

echo "select ssid, count(*) as points from aps group by ssid order by points
desc \G" | mysql warbike > ssid_report.txt

- jon (feeling dumb)

pgp key: http://www.jonbaer.net/jonbaer.asc
fingerprint: F438 A47E C45E 8B27 F68C 1F9B 41DB DB8B 9A0C AF47

----- Original Message ----- 
From: "Jon Baer" <jonbaer at jonbaer.net>
To: <talk at lists.nyphp.org>
Sent: Monday, July 21, 2003 5:48 PM
Subject: sorting multi-dimesional arrays ...


> ok, not the greatest code in the world but it did what i needed :-) ...
>
> basically i have around 3500+ access points logged and wanted to do some
> stats, but could not think of a better way to sort via a 2d array on the
2nd
> element, im just looking to shorten up the code (by just using an array
> method of some kind - array_multisort?)...
>
> example output:
>
> Indexing 3388 access points ...
> SSID: "linksys" : 620 (18.30%)
> SSID: "default" : 192 (5.67%)
> SSID: "Wireless" : 167 (4.93%)
> SSID: "wireless" : 58 (1.71%)
> SSID: "WLAN" : 44 (1.30%)
> SSID: "Verizon Wi-Fi" : 43 (1.27%)
>
> - jon
>
> <?php
>
>  $con = mysql_connect("localhost","ap_user","ap_pass");
>  mysql_select_db("ap_db");
>
>  $sql = "select * from aps";
>  $result = mysql_query($sql);
>
>  $total_ap = mysql_num_rows($result);
>
>  echo ("Indexing " . $total_ap . " access points ...\n\n");
>
>
>
////////////////////////////////////////////////////////////////////////////
> //////////////
>
>  $ssidArray = array();
>  $ssidNameArray = array();
>
>  while($row = mysql_fetch_assoc($result)) {
>
>   // ssid stats
>   $ssid_name = $row['ssid'];
>   $ssidNameArray[] = $ssid_name;
>   if (array_key_exists($ssid_name, $ssidArray)) {
>    $ssidArray[$ssid_name]['count'] = $ssidArray[$ssid_name]['count'] + 1;
>   } else {
>    $ssidArray[$ssid_name]['count'] = 1;
>   }
>
>  }
>
>  $ssidUnique = array_unique($ssidNameArray);
>  sort($ssidUnique);
>
>  for ($x = 0; $x < count($ssidUnique); $x++) {
>   if ($ssidUnique[$x] == "") next;
>   $countArray[$x] = array($ssidUnique[$x],
> $ssidArray[$ssidUnique[$x]]['count']);
>  }
>
>  array_multisort($countArray[1]);
>
>  mysql_query("delete from ssid_count");
>
>  foreach($countArray as $ap) {
>   $insert = "insert into ssid_count values ('".$ap[0]."',".$ap[1].")";
>   mysql_query($insert);
>   // echo $ap[0].$ap[1]."\n";
>  }
>
>  $query = "select * from ssid_count order by count desc limit 25";
>  $result = mysql_query($query);
>  while ($row = mysql_fetch_assoc($result)) {
>   $percent = sprintf("%01.2f", $row['count']/$total_ap * 100);
>   echo "SSID: \"". $row['name'] . "\" : " . $row['count']."
> (".$percent."%)\n";
>  }
>
>  mysql_close($con);
>
> ?>
>
> pgp key: http://www.jonbaer.net/jonbaer.asc
> fingerprint: F438 A47E C45E 8B27 F68C 1F9B 41DB DB8B 9A0C AF47
>
>