Custom MySQL to display Count of Events at each location.

  • Resolved SimonFinch

    (@simonfinch)


    5 years, 10 months ago

    On a separate site page, I want to show a listing of each location and the number of events that have taken place there. I’m using;
    =======
    SELECT
    wp_em_locations.location_name as “Location”, COUNT(*) as “Total”
    FROM wp_em_locations
    INNER JOIN wp_em_events
    ON wp_em_locations.location_id = wp_em_events.location_id
    GROUP BY wp_em_events.location_id
    Order By “Total”
    =======

    This works fine. However it gives me ALL events and I just want events from a single category. The wp_em_category_id field in the db is showing null values, so I expect the categories I have used are in a meta table table somewhere. How would I do this please?

    Thanks Simon

Viewing 1 replies (of 1 total)

  • Hi Simon,
    You are mixing up two things: showing locations and showing events with a category. A location does not have the event category linked, as more events with different categories can take place at that single location, of course.

    To only get the count of events in category X on location Y, the leading “info giver” is the event and not the location. ??

    A SQL query will not allow you to combine all those easily, as the info is ‘scattered’ over several tables. Luckily, EM has a built-in solution! ??

    Using the EM php function get() will allow you to use the same arguments as [events_list] (See: https://wp-events-plugin.com/documentation/event-search-attributes/)

    $args = array(
	'scope' = 'all', 	// or 'past' or 'future'
	'category' = 'X', 	// the Event Category ID.
	'location' = 'Y' 	// the Location ID.
);

$Event_Count = EM_Events::get($args, $count=true); // false returns the list.
echo $Event_Count;
Viewing 1 replies (of 1 total)
  • The topic ‘Custom MySQL to display Count of Events at each location.’ is closed to new replies.