Right, I've got a bit of a tricky one here (for me anyway🙂).
I'm wanting to send out an email to my users on their birthday with a gift voucher in it. I also want to keep a log of all of these which have gone out and their corresponding voucher code. For this I plan to use two tables: users & birthday_log. The relevant fields in each are listed below.
[b]users[/b]
[u]user_nr[/u]
name
email_address
domain
birth_year
birth_month
birth_day
[b]birthday_log[/b]
[u]unique_id[/u] - not really relevant here, just put in for completeness
user_nr
last_email
voucher_code
Pretty simple so far isn't it. Well, here's where it gets a bit messy, this is the first draft of the query for pulling out the users which have to be emailed today. This is with MySQL by the way.
SELECT users.user_nr, name, email_address, domain, DATE_FORMAT(DATE(), '%Y')-birth_year as age
FROM users
LEFT JOIN birthday_log ON (birthday_log.user_nr=users.user_nr)
WHERE birth_day=DATE_FORMAT(DATE(), '%e') && birth_month=DATE_FORMAT(DATE(), '%c')
&& (birthday_log.last_email IS NULL || birthday_log.last_email<=DATE_SUB(DATE(), INTERVAL 1 YEAR))
The second line of the where clause is designed to prevent users changing their birthday to get more emails the problem is that because I'm going to be storing the details of all emails ever sent to them after the first year this safety feature will break down. Basically I need a way of restricting the join to that it only returns the MAX last_email for each user. I'm guessing something with a subselect but to be honest this is a little out of my league. I can solve it quite easily by dumping it out to PHP and sorting it out there but I really want to be able to do as much processing in the database (and maybe one day construct complex queries myself).
If any of this doesn't make sense let me know and I'll give it another go.
