There is a condition for sampling expiration in this case, VIP privileges. vip_data field

 (`vip_data` - INTERVAL 1 DAY > NOW()) AND (`vip_data` - INTERVAL 2 DAY < NOW())

This is a condition for sending a letter, saying that tomorrow your VIP privileges end. The word tomorrow is far from always true, for everything depends on several components

  • 1 when the email sending script runs
  • 2 when vip privileges end
  • 3 how many times a day the script runs

If the script is launched once a day, we have large errors and even omissions to send alert letters.

How to build a notification scheme in this case?

What is the right way to choose those that end today , with existing entries in the database, regardless of when the script is launched?

  • try curdate () instead of now () it will give the beginning of the day. and it is better to still write vip_data > now()+inteval 1 day that no calculations are performed on the database field, and once was considered a constant - Mike
  • one
    How to build a notification scheme in this case? For example, at 0:00, in one request, scoop everyone who needs to be notified into a separate table. And from 0:30 to 10:00 send letters every half hour sending 5% of random from all selected ones, removing them from the list for sending. At 10:30, control, and in case of problems - manual response. - Akina

1 answer 1

First, users or vip_data statuses (if they are stored in a separate table) need to add a mail column in which the letter is indicated whether the letter was sent or not. 0 - not yet sent 1 - already sent. The datetime field means you can make a request:

 SELECT * FROM `table` WHERE `vip_data` <= CURDATE() + INTERVAL 1 DAY

or

 SELECT * FROM `table` WHERE `vip_data` <= CURDATE() + INTERVAL 86400 SECOND

So you get those who have a day or less before the end of the VIP account. Send them a letter and change the mail column from zero to 1. change the request:

 SELECT * FROM `table` WHERE `vip_data` <= CURDATE() + INTERVAL 1 DAY AND `mail`=0 SELECT * FROM `table` WHERE `vip_data` <= CURDATE() + INTERVAL 86400 SECOND AND `mail`=0

Further, at the moment when it prolongs the VIP status, the mail bell must be reset to 0 again.