PHP: Concatenating database results by email

Question

I have a list of tasks assigned to multiple users. I'd like to set up a cron job and send out email notifications once an hour to each user. Here's my query:

SELECT t.*, u.name, u.email
FROM tasks t, users u
WHERE t.date_created > DATE_SUB(NOW(), INTERVAL 1 HOUR) AND t.user_id = u.id
ORDER BY t.date_created ASC

Here's the result (I trimmed a few columns to fit better):

+----+-------+---------------------+---------+--------+------+--------------+
| id | title |     date_created    | user_id | status | name |     email    |
+----+-------+---------------------+---------+--------+------+--------------+
| 9  | task1 | 2013-09-01 17:56:10 |    2    | active | John | [email protected] |
| 10 | task2 | 2013-09-01 17:57:20 |    1    | active | Tim  | [email protected] |
| 11 | task3 | 2013-09-01 17:58:30 |    2    | active | John | [email protected] |
| 12 | task4 | 2013-09-01 17:59:40 |    1    | active | Tim  | [email protected] |
+----+-------+---------------------+---------+--------+------+--------------+

The problem is that I can't figure out how to concatenate the tasks belonging to a certain user_id and then send them out to the assigned email. So for the above example there would be two emails sent out:

task1 and task 3 (to [email protected])
task2 and task 4 (to [email protected])

I'm not sure what you are asking for but try GROUP BY email or GROUP BY user_id? — putvande
– putvande, Commented Sep 1, 2013 at 17:05

darma · Accepted Answer · 2013-09-01 17:14:56Z

3

Try with the GROUP_CONCAT aggregate command :

SELECT GROUP_CONCAT(t.title), u.name, u.email
FROM tasks t, users u
WHERE t.date_created > DATE_SUB(NOW(), INTERVAL 1 HOUR) AND t.user_id = u.id
GROUP BY u.id

This will output something like :

task1,task3 | John | [email protected]
task2,task4 | Tim  | [email protected]

Default is a comma delimited string, but you can specify another separator like this : GROUP_CONCAT(t.title,' and ')

edited Sep 1, 2013 at 17:14

answered Sep 1, 2013 at 17:09

darma

4,7571 gold badge27 silver badges26 bronze badges

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

1 Comment

Norbert Over a year ago

Thanks! You can also use SELECT GROUP_CONCAT(t.title) as title to change the column heading.

Collectives™ on Stack Overflow

PHP: Concatenating database results by email

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related