PHP Count without Group By

[bigheadedd]

Hi,

 

I'm wondering if this is possible.

Basically i'm making a query that gets lots of results from different tables.

 

I'll then loop through them, displaying each event. However, each event is assigned a venue, which is assigned an area.

What I want to do is count the amount of events that are in each area, but without doing separate mysql queries (one for the main listing, the other for the count).

 

$result = mysql_query("SELECT event_title, img_filename, area_name, COUNT(area_id) AS counted FROM events 
LEFT JOIN images ON events.event_id=images.owner_id 
LEFT JOIN venues ON events.venue_id=venues.venue_id 
LEFT JOIN area ON venues.v_area=area.area_id 
GROUP BY area.area_id ORDER BY area_name ASC")or die(mysql_error());

 

Now obviously this won't work, as it groups the results by the area_id in order to make the count work, and I can't loop through my events.

When going through the loop i'm using some separate php code to only echo the area name once. I'd do this for the count, however I need the count at the top of the page.

 

if ($areaname!=$row['area_name']){
$areaname = $row['area_name'].$row['count'];
echo "<div class=\"areaheader\"><h2 class=\"gridarea\">".$areaname."</h2></div>";
$count="";
}

 

Any help would be amazing!

 

Thanks,

E

[bigheadedd]

Okay so I found a way around it, though maybe someone can shine a better way?

 

while ($ss=mysql_fetch_array($result))
{
$areacount[$ss['area_id']]++;
}
mysql_data_seek($result,0);

 

Doing a simple count with a separate loop before the main loop. (Also removed all of the COUNT and GROUP BY statements in the main query!).

[Psycho]

When going through the loop i'm using some separate php code to only echo the area name once.

 

A couple things:

 

You don't have to "echo" the content as you are processing it. You can store the content in a variable(s) as you are getting the count and then echo the output.

 

I'm curious why you are grouping by events and area. Why would you have multiple events in the events table? I would also assume that each event is only associated with one area. It's really difficult to provide the proper solution as I do not understand your database schema. If what I think is true about your database, then I would think that GROUP BY clause is not serving a purpose.

 

Another thing is that your query has a specific area ID specified in the WHERE clause. If you are only pulling records for one area then just use mysql_num_rows() on the result to get the number of venues for that area.

 

But, assuming you are pulling records for multiple areas, I would modify the processing logic to get the count of venues by area THEN create the output for that area.

 

Sample code (NOTE: I would change the query to also return the area ID and use that in the logic to determine changes in area. Using a name value is not good practice.

function displayEventsByArea($areaArr)
{
    $areaCount = count($areaArr);
    echo "<div class='areaheader'>";
    echo "<h2 class='gridarea'>{}</h2>{$areaArr['area_name']} ({$areaCount})";
    echo "</div>";
    foreach($areaArr)
    {
        echo "Event: {$areaArr['event_title']}";
        echo "<img src='{$areaArr['img_filename']}'><br>\n";
    }
}

$currentArea = false;
while($row = mysql_fetch_assoc($result))
{
    if($currentArea !== $row['area_name'])
    {
        if($currentArea !== false)
        {
            //Output the previous set of events for the area
            displayEventsByArea($areaArray);
        }
        //Set the current area
        $currentArea = $row['area_name'];
        //Set (or reset) array of event data
        $areaData = array();
    }
    //Populate current record into $areaData array
    $areaData[] = $row;
}

//Output the LAST set of events for the LAST area
displayEventsByArea($areaArray);