PHP MySQL count events per location per user

Question

I'm trying to build a query using data from 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 that I can't seem to write should get me to display a table like this :

+------------------------------+
+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 I got to work is displaying how many booking have been made per user but not per user AND per location using this query:

$query='
SELECT
bookings.person_id,
COUNT(bookings.person_id) AS total,
bookings.event_id,
users.display_name

FROM bookings

INNER JOIN users ON bookings.person_id=users.id
WHERE users.id=bookings.person_id

GROUP BY 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 and displays :

 +-----------------------------------+
 | ID    |  NAME   | Total Bookings  |
 +-----------------------------------+
 | 7     |  Bob    | 3               |
 | 5     |  Jane   | 2               |
 | 3     |  Joe    | 1               |
 +-----------------------------------+

Could you please help me getting there. Thanks.

Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Apr 22 '15 at 15:05
you need `group by user, location`. you're grouping on user only. — Marc B, Apr 22 '15 at 15:07
Looks like you'll need to do a pivot table to get the results you need — Kisaragi, Apr 22 '15 at 15:10
@Jay all excellent points. Could `mysqli` be used as an alternative to `PDO`? It has been a while but I remember it being pretty straightforward to port my application from `mysql` to `mysqli`. — steve klein, Apr 22 '15 at 15:20
Yes @steveklein you can use `mysqli` as an alternative to `PDO`. No matter which you choose you need to use parametrized queries. — Jay Blanchard, Apr 22 '15 at 15:22
Thank you for your comments. I'll learn about prepared statements and PDO. — s_b, Apr 22 '15 at 18:18

score 0 · Accepted Answer · answered Apr 22 '15 at 16:04

You are looking for a pivot table style query. Here's one way you can do it.

select u.name, 
       count(case when l.name = 'Lombard' then 1 end) as lombard,
       count(case when l.name = 'NYC' then 1 end) as nyc, 
       count(case when l.name = 'LA' then 1 end) la, 
       count(u.name) total
  from 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 by u.name
  with rollup

fiddle here

It gets a lot harder (and is generally easier to do in the application) if you dont know the possible column (location) values when you are writing the query.

thank you very much pala_. The pivot table query works great. — s_b, Apr 22 '15 at 18:14

score 0 · Answer 2 · answered Apr 22 '15 at 16:32

0

http://sqlfiddle.com/#!9/92d50

  SELECT u.name, 
       SUM(l.name = 'Lombard') lombard,
       SUM(l.name = 'NYC') nyc, 
       SUM(l.name = 'LA') la, 
       COUNT(*) total
  FROM bookings b
  LEFT JOIN `events` e
    ON b.event_id = e.event_id
  LEFT JOIN locations l
    ON e.location_id = l.location_id
  LEFT JOIN users u
    ON u.user_id = b.person_id
  GROUP BY u.name
  WITH ROLLUP

answered Apr 22 '15 at 16:32

Alex

16,739
1
28
51

thank you very much Alex. Your pivot table query works great too. – s_b Apr 22 '15 at 18:27

PHP MySQL count events per location per user

2 Answers2