1

i am contacting an API in PHP that returns a loop of items with this information:

[domain_name] => domain.co.uk
[expiry_date] => 2016-06-18T02:27:04

i then have a table that contains columns for domain, reminder1, reminder2, reminder3

i am going to poll this URL throughout the day, every day and i want to send 'renewal reminders' for 30, 7 and 1 days before the expiry_date - each reminder will be stored in my database (reminder1, reminder2 and reminder3)

What is the best way to check if the reminders have already been sent?

charlie
  • 415
  • 4
  • 35
  • 83

1 Answers1

1

I suggest you to change the database structure a bit to fit with this solution. So for example you have the following tables:

Table domains:

+----+----------------------+
| id |      domain_name     |
+----+----------------------+
|  1 | domain1.com          |
+----+----------------------+
|  2 | domain2.com          |
+----+----------------------+
|  3 | domain3.com          |
+----+----------------------+

Table domain_expiry

+----+--------------+----------------------+
| id |  domain_id   |     expiry_date      |
+----+--------------+----------------------+
|  1 | 1            | 2016-07-01 11:22:24  |
+----+--------------+----------------------+
|  2 | 1            | 2017-07-01 11:22:24  |
+----+--------------+----------------------+
|  3 | 2            | 2016-08-01 11:22:24  |
+----+--------------+----------------------+

Table reminders:

+----+-----------+---------------+------------+--------+
| id | expiry_id | reminder_type | date_sent  | status |
+----+-----------+---------------+------------+--------+
|  1 | 1         | 1             | 2016-06-01 | 1      |
+----+-----------+---------------+---------------------+
|  2 | 1         | 2             | 2016-06-23 | 1      |
+----+-----------+---------------+---------------------+
|  3 | 2         | 1             | 2016-08-01 | 0      |
+----+-----------+---------------+---------------------+

Now, you should have a cron which runs daily/nightly ( say at 12:00 AM ), this cron shall do:

1- Pulls the domains and their latest expiry_date

2- Checks if the expiry_date of the domain matches the record in domain_expiry table, if matches do nothing, if doesn't match add a new records with domain_id and new expiry_date

3- Sends reminder according to domain latest expiry_date

4- Inserts the result of reminder sending process into reminders table

expiry_id,

reminder_type (1, 2, or 3),

the date when that reminder was sent, date_sent

and status (1 = success and 0 = failure)

Update

I updated my answer to allow the solution to keep history logs.

Ahmed Rashad
  • 507
  • 3
  • 7
  • okay, so if it is renewed, obviously that will change the expiry date to + X years. what do we do then? – charlie Jun 08 '16 at 22:49
  • in case of renewal i.e. changing the `expiry_date`, then you are supposed to reset the `reminder` table records corresponding to the `domain_id` of changed `expiry_date`, just after updating that record in `domains` table – Ahmed Rashad Jun 08 '16 at 22:52
  • reset records = delete records – Ahmed Rashad Jun 08 '16 at 22:54
  • Or, do you like to keep those reminder table records for matter of logging? – Ahmed Rashad Jun 08 '16 at 22:56
  • yes i would prefer to keep them as a backlog, what about having the `reminder_type` column in the domains table then having the other table as a history / log of reminders sent – charlie Jun 08 '16 at 22:57
  • I'll make another update to the answer, but I wouldn't recommend to keep reminder_type in domain table – Ahmed Rashad Jun 08 '16 at 22:58
  • what do you think? – charlie Jun 08 '16 at 23:06
  • im not worried about logs for expiry dates - just for when reminders are sent out. i have a table of domains and i call an API of domains that are near to expiring. inside this API call (loop) i will run the functions/processes for checking reminders etc and sending new emails – charlie Jun 08 '16 at 23:11
  • so, could i have - the domains table that contains the `domain_name` and `reminder_type` then a `domains_expiry` table that contains `domain_id`, `reminder_type` and `date_sent` – charlie Jun 08 '16 at 23:12
  • according to what you asked ( history logs ) then you are actually linking the reminder_type to both domain and expiry_date, and if you will have many expiry_dates per domain, then you need to normalize the table of domains into 2 tables. My suggestion is to keep reminder_type in separate table, is to have full information about that reminder ( for example sent status, and the date ), but keeping them in domain or expiry tables may prevent that. – Ahmed Rashad Jun 08 '16 at 23:14
  • i dont store the `expiry_date` myself - that comes from the API i am calling – charlie Jun 08 '16 at 23:15
  • and if i am checking for dates, the time until the expiry_date in my example is `-9.0901851851852` in days - how can i check if its the 30, 7 or 1 day reminder based on the number of days returned? – charlie Jun 08 '16 at 23:17
  • `expiry_date` is important for matter to differentiate between reminders sent for the same domain, say if you sent `reminder1` for `domain1.com` for two times, don't you care why did you send it? you need to know that the first `reminder1` was because of `expiry_date1` and the second `reminder1` was because of `expiry_date2` – Ahmed Rashad Jun 08 '16 at 23:21
  • You can use PHP `DateTime` class to get the date from `expiry_date` string and then get the difference between it and today's date. Then use this answer http://stackoverflow.com/questions/1940338/date-difference-in-php-on-days – Ahmed Rashad Jun 08 '16 at 23:29