PHP MySQL count events per location per user -

i'm trying build query using data 4 tables: bookings, users, events, locations

bookings :

+---------------------------------+ |book_id   | event_id | person_id | +---------------------------------+ |1         | 1        | 2         | |2         | 2        | 1         | |3         | 2        | 2         | |4         | 1        | 3         | |5         | 3        | 1         | |6         | 3        | 2         | +---------------------------------+ 

users :

+----------------------+ | user_id  | name      | +----------------------+ | 1        | joe       | | 2        | jack      | | 3        | jane      | +----------------------+ 

events :

+------------------------+ | event_id | location_id | +------------------------+ | 1        | 1           | | 2        | 3           | | 3        | 1           | +------------------------+ 

locations :

+---------------------------+ | location_id | name        | +---------------------------+ | 1           | lombard     | | 2           | nyc         | | 3           | la          | +---------------------------+ 

the query can't seem write should me display table :

+------------------------------+ +name     |lombard|nyc|la|total| +------------------------------+ +joe      |1      |0  |1 |2    | +jack     |2      |0  |1 |3    | +jane     |1      |0  |0 |1    | +------------------------------+ +totals   |4      |0  |2 |6    | +------------------------------+ 

what got work displaying how many booking have been made per user not per user , per location using query:

$query=' select bookings.person_id, count(bookings.person_id) total, bookings.event_id, users.display_name  bookings  inner join users on bookings.person_id=users.id users.id=bookings.person_id  group bookings.person_id'; $result = mysql_query($query); if($result) { while($row = mysql_fetch_array($result)) {  /* total bookings per user */  $value = $row['total'];  $sum += $value;   /* displaying results */   echo "<tr width='500'>";     echo "<td>".$row['person_id']."</td>";     echo "<td>".$row['display_name']."</td>";     echo "<td>".$row['total']."</td>";   echo "</tr>"; } 

this works okay , displays :

 +-----------------------------------+  | id    |  name   | total bookings  |  +-----------------------------------+  | 7     |  bob    | 3               |  | 5     |  jane   | 2               |  | 3     |  joe    | 1               |  +-----------------------------------+ 

could please me getting there. thanks.

you looking pivot table style query. here's 1 way can it.

select u.name,         count(case when l.name = 'lombard' 1 end) lombard,        count(case when l.name = 'nyc' 1 end) nyc,         count(case when l.name = 'la' 1 end) la,         count(u.name) total   bookings b     inner join events e       on b.event_id = e.event_id     inner join locations l       on e.location_id = l.location_id     inner join users u       on u.user_id = b.person_id   group u.name   rollup 

fiddle here

it gets lot harder (and easier in application) if dont know possible column (location) values when writing query.