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
it gets lot harder (and easier in application) if dont know possible column (location) values when writing query.
Comments
Post a Comment