PHP stack multiple values into one value

Question

This is the bookings table I'm using for my query

+----------------------+
| event_id | person_id |
+----------------------+
| 5        | 7         |
| 4        | 7         |
| 3        | 7         |
| 4        | 5         |
| 3        | 5         |
| 5        | 3         |
+----------------------+

This table shows that person_id 7 has 3 bookings, 5 has 2 bookings and 3 has 6 bookings. Currently, I'm using this query to get the total number of bookings per person.

$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;

 /* events booked per user  */
 $events....

 /* Displaying results */
  echo "<tr width='500'>";
    echo "<td>".$row['person_id']."</td>";
    echo "<td>".$row['display_name']."</td>";
    echo "<td>".$row['total']."</td>";
    echo "<td>".$events."</td>";
  echo "</tr>";
}

This works okay and gives me:

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

I'm seeking help to get this to display the events booked by each person (like the 4th columns below):

+------------------------------------------------+
| ID    |  NAME   | Total Bookings | Event IDs   |
+------------------------------------------------+
| 7     |  Bob    | 3              | 5,4,3       |
| 5     |  Jane   | 2              | 4,3         |
| 3     |  Joe    | 1              | 5           |
+------------------------------------------------+

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 21 '15 at 13:24
How do you read "3 has 6 bookings" when only 1 row is connected to 3? — Loko, Apr 21 '15 at 13:28

Alex · Accepted Answer · 2015-04-21T13:42:17.393

2

GROUP_CONCAT https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

$query='
SELECT
bookings.person_id,
COUNT(bookings.person_id) AS total,
GROUP_CONCAT(bookings.event_id) as event_ids,
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';

edited Apr 21 '15 at 13:42

answered Apr 21 '15 at 13:29

Alex

16,739
1
28
51

1

Please, add some explanation (mentioning your use of `GROUP_CONCAT`) and possibly a link to the manual so that your answer has more of an educational value. – tmt Apr 21 '15 at 13:34

score 0 · Answer 2 · answered Apr 21 '15 at 13:55

0

A bit different query but same result:

SELECT
    bookings.person_id,
    COUNT(
        bookings.person_id
    ) AS total,
    users.display_name,
    GROUP_CONCAT(
        bookings.event_id
        ORDER BY
            bookings.event_id
    ) AS events_list
FROM
    bookings,
    users
WHERE
    bookings.person_id=users.id
GROUP BY
    bookings.person_id
ORDER BY
    bookings.person_id

I don't know if for a large data, the execution time is less, more or equal.

answered Apr 21 '15 at 13:55

nada

972
5
22

Great! Thanks nada. It's not for large data. I'll test it with larger data to check out which is lighter. – s_b Apr 21 '15 at 16:27
remember both ORDER_BY are optional – nada Apr 22 '15 at 07:17

PHP stack multiple values into one value

2 Answers2