get max value in php (instead of mysql)

Question

I have two msyql tables, Badges and Events. I use a join to find all the events and return the badge info for that event (title & description) using the following code:

SELECT COUNT(Badges.badge_ID) AS badge_count,title,Badges.description FROM Badges JOIN Events ON Badges.badge_id=Events.badge_id GROUP BY title ASC

In addition to the counts, I need to know the value of the event with the most entries. I thought I'd do this in php with the max() function, but I had trouble getting that to work correctly. So, I decided I could get the same result by modifying the above query by using "ORDER BY badgecount DESC LIMIT 1," which returns an array of a single element, whose value is the highest count total of all the events.

While this solution works well for me, I'm curious if it is taking more resources to make 2 calls to the server (b/c I'm now using two queries) instead of working it out in php. If I did do it in php, how could I get the max value of a particular item in an associative array (it would be nice to be able to return the key and the value, if possible)?

EDIT: OK, it's amazing what a few hours of rest will do for the mind. I opened up my code this morning, and made a simple modification to the code, which worked out for me. I simply created a variable on the count field and, if the new one was greater than the old one, changed it to the new value (see the "if" statement in the following code):

if ( $c > $highestCount ) { $highestCount = $c; }

Answer 1

Check out this very similar question asked less than 15 minutes ago: http://stackoverflow.com/questions/1345307/php-retrieve-minimum-and-maximum-values-in-a-2d-associative-array

Answer 2

you can try use http://php.net/array_multisort

Answer 3

This might again lead to a "religious war", but I would go with the two queries version. To me it is cleaner to have data handling in the database as much as possible. In the long run, query caching, etc.. would even out the overhead caused by the extra query.

Anyway, to get the max in PHP, you simply need to iterate over your $results array:

getMax($results) {
  if (count($results) == 0) {
    return NULL;
  }

  $max = reset($results);
  for($results as $elem) {
    if ($max < $elem) {  // need to do specific comparison here
      $max = $elem;
    }
  }
  return $max;
}