SQL Count queries

[Mundane]

Hi all,

 

I've built a few amateur sites in the past but am keen to utilize php combined with mysql for my current project.  However, I have little knowledge of php and am just picking it up as I go (same way I've done for all my computer knowledge)

 

What I'm looking for may sound simple, but I really don't know where to start and I'm hoping there are some kind souls here that will lend a hand.  I've got some fantastic help here before and am hoping I'll get lucky again.

 

SQL Database

meetingid venue

001 new york

002 chicago

003 new york

004 new york

005 new york

006 texas

007 texas

008 chicago

009 new york

010 new york

 

Question

If I hard code everything around it, I'm just looking for the code I would need to have it display as follows: (hard coded text in blue)

 

"New York has been used * times." 

(Where * would count the number of times "New York" appears in the venue column of the database.)

"The most consecutive times New York has been used is % ." 

(Where % would count the maximum number of consecutive times "New York" appears in the venue column of the database.)

 

Giving the output:

New York has been used 6 times.

The most consecutive times New York has been used is 3.

[Muddy_Funster]

For the first part you are simply looking for a COUNT(SELECT venue FROM <table> WHERE venue = 'new york').  The second part isn't something that I know a database can do (not without having some control fields included in the table).  Maybe it can, but I think you're going to be looking ar some fairly complex array work in php to get the output that you are looking for.  Regardless, I'm sorry to say I can't help any more than that.

[ManiacDan]

Your first one is more accurately:

SELECT COUNT(venue) as total FROM theTable WHERE venue = 'New York';

or

SELECT venue, COUNT(venue) as total FROM theTable GROUP BY venue;

 

The second one will produce all your counts at once, for every venue.

 

The second one might be handled by this DevShed thread I wrote a couple years ago.  It's very difficult to generate a "Streak" value straight from a database table.  I get the feeling there's a way, but the SQL for it is beyond me.

 

-Dan

[btherl]

An alternative for part 1 is:

 

SELECT venue, count(*) FROM <table> GROUP BY venue

 

That will give you counts for all venues.

 

For part 2, I would do it in PHP.  Any method I can think of to do it in SQL is ugly.  Here's one approach that pops into my head:

 

$max_consecutive_venues = array();
$last_venue = null;
while ($row = mysql_fetch_row($result)) {
  $venue = $row['venue'];

  if ($venue != $last_venue) {
    if ($last_venue !== null) {
      $max_consecutive_venues[$venue] = max($max_consecutive_venues[$venue], $current_count);
    }
    $last_venue = $venue; # Start counting next venue
    $current_count = 1; # And reset count
  } else {
    # This is the same venue as the previous one.  Increase the count.
    $current_count += 1;
  }
}
# And record the final venue in the list, which won't get recorded by the while loop.
if ($last_venue !== null) {
  $max_consecutive_venues[$venue] = max($max_consecutive_venues[$venue], $current_count);
}

 

This will (assuming it works, it's untested) count all venues and how many times they appear consecutively.  It's assuming the data it got from mysql is ordered by meetingid.