This code works, but are there alternate, more efficient ways of doing it. A count of subscribers, by classification, has to be provided, where the classification definitions are in one table (class) with this structure:
nClassKey - unique, numeric, autoincrement Classification - text ,name of the classification ClassDesc - text, description of the classification
and a nClassKey value is stored in each subscriber record.
As the version of MySQL used by my ISP lacks UNION capability, I'm fetching the necessary data with two SELECTS. The first retrieves nClassKey and Classification from the class table, and the results are put in an array, $arClass, where the $key is nClassKey and $value is Classification. Pretty straightforward, resulting in [1]=>Development, [2]=>Retailing ... [13]=>Agriculture.
For the count of classifications I used the following SELECT:
select nClassKey, count(nClassKey) as classcount from subscriber group by nClassKey
and stick it in the array $arCount with this code:
while ($myrow = mysql_fetch_array($result)) {
$arCount[$myrow["nClassKey"]] = $myrow["classcount"];
}
All of this works. Note that $arCount can be a sparse array; there may be instances of nClassKey missing from the subscriber table simply because no one happens to fall in the "aerospace" category, for example.
To generate a listing for display, I am using the following code to traverse the $arClass array, searching in the $arCount for a matching key, leaving out all the HTML stuff:
foreach($arClass as $key => $value){ print( $value . ": "); if (array_key_exists($key, $arCount)){ print( $arCount[$key] ); } else { print( 0 ); } } // foreach
My question is if there is a faster, cleaner way to do this? I did try array_search, but as it looks for any matching value anywhere in the target array the results it returned were not reliable.
If I'm understanding you correctly, I think this'll work:
SELECT c.nClassKey, c.Classification, COUNT(s.nClassKey) FROM classifications c LEFT JOIN subscribers s ON c.nClassKey = s.nClassKey GROUP BY c.nClassKey
-- ---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/
php|architect: The Magazine for PHP Professionals – www.phparch.com
-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
