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. They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and use PDO. — Jay Blanchard
– Jay Blanchard, Commented Apr 21, 2015 at 13:24
How do you read "3 has 6 bookings" when only 1 row is connected to 3? — Loko
– Loko, Commented Apr 21, 2015 at 13:28

Alex · Accepted Answer · 2015-04-21 13:42:17Z

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, 2015 at 13:42

answered Apr 21, 2015 at 13:29

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tmt Over a year ago

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.

nada · Accepted Answer · 2015-04-21 13:55:28Z

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, 2015 at 13:55

nada

9825 silver badges23 bronze badges

2 Comments

s_b Over a year ago

Great! Thanks nada. It's not for large data. I'll test it with larger data to check out which is lighter.

nada Over a year ago

remember both ORDER_BY are optional

Collectives™ on Stack Overflow

PHP stack multiple values into one value

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related