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; }

Check out this very similar question asked less than 15 minutes ago: stackoverflow.com/questions/1345307/… — Amber
– Amber, Commented Aug 28, 2009 at 6:33
If you're worried about the (read) performance have you considered to calculate and store the redundant information when a new entry is made? — VolkerK
– VolkerK, Commented Aug 28, 2009 at 7:31

Zed · Accepted Answer · 2009-08-28 06:36:58Z

4

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;
}

answered Aug 28, 2009 at 6:36

Zed

57.8k9 gold badges79 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Chris Cherry Over a year ago

I agree with the 2 queries method. Database calls are very fast at ordering (as long as the column is indexed) and more efficient memory wise.

Sorin Mocanu Over a year ago

I agree with two queries. Basides the index argument (which may mean that the db engine will not even hit the disk to read all those values), this also saves traffic if your database server is on a different machine. Watch out on micromanaging optimizations :)

staypuffinpc Over a year ago

this is similar to what I ended up doing this morning. Also, thanks for the opinions on the query method. I know this follow up probably belongs in another question (and has likely been answered), but what would be the "best" way to test which method is the least resource intensive?

Zed Over a year ago

implement both versions, and then stress test both of them using apache benchmark, or something similar, mimicking real life usage.

Collectives™ on Stack Overflow

get max value in php (instead of mysql)

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related