Email reminder using php and mysql

PHP
1

Hello everyone,

I have searched various forums for a solution to my problem, but haven’t found the one that suits my needs.

Here is my situation: I need to send email reminders to two volunteer-referees of a sportsclub. These reminders have to be sent 7 days and 1 day in advance of the day they have to referee on (so in total 4 reminders have to be sent to two people, each referee receiving an email 7 days and one 1 day in advance).

The date, name of volunteers and the volunteers e-mail are all in the same table in the mysql-database.

DB table ‘referee_schedule’ is the table and consists of the following information:
Column 1: Event_date
Column 2: Event_name (the match they have to referee)
Column 3: Referee_1
Column 4: Referee_2
Column 5: Email_referee_1
Column 6: Email_referee_2

What is the best and easiest way to write a php script that checks if it is 7 or 1 day(s) in advance of event date, and if this is true, send a simple html email reminder to both referees.

If I am correct I need to use a ‘select’ line that looks something like this: (?)

SELECT * FROM referee_schedule WHERE 
event_date CURDATE()+INTERVAL 7 DAY 
OR CURDATE()+INTERVAL 1 DAY

I believe this topic (Send automatic email by date HELP! - #10 by chris331) comes pretty close to what I need, but I have no idea how to adapt it to my situation.

Btw, if my DB-table and script will be more effective by storing the actual events (event table with only event_name, event_date and referee) and the email of referees (referee and email_referee) in separate tables; this is possible, as the tables are not filled yet.

My knowledge of php is ‘basic’; I know what separate lines do, but I’m definitely not able to write a full script.

Thanks you in advance! Help would be really appreciated!

(PS sorry for my English. It’s not my first language)

2

I think it would make more sense to have separate tables for Referees and Events, then I think a Look-up table to link them together.
We see there is more than one referee per event. If any one referee is assigned to more than one event you have a “many to many” relationship, so splitting the tables will remove a lot of data duplication.

I’m not sure about that query with CURDATE I’ve not used the curdate function, but I don’t think that’s right. Maybe try something more like the query I posted in the other topic.

3

Thank you for your quick reply!

What you said about splitting the tables indeed makes sense, then I will not have to insert emailadresses for the same referee again and again.
However, I have not worked with a look-up table before. Is there an easy way of grabbing information from two different tables in a single php code? (Sorry my knowledge of PHP and SQL is very basic :pensive:)


The ‘select’ code I posted actually seems to work in my database. At least, I did a test run, and it returned 7 different rows (and I manually checked the table and there were 7 rows within a 7 or 1 day period.

SELECT * FROM 'referee_schedule' WHERE 'Event_date'=CURRENT_DATE+ INTERVAL 7 DAY OR 'Event_date'=CURRENT_DATE+ INTERVAL 1 DAY
4

This will require joins.
Maybe something like this (can’t test without the databases):-

SELECT referees.name, referees.email, events.date, events.name FROM referees
JOIN events_lut ON referees.id = events_lut.ref_id
JOIN events ON events_lut.evt_id = events.id
WHERE 'events.date'=CURRENT_DATE+ INTERVAL 7 DAY
OR 'events.date'=CURRENT_DATE+ INTERVAL 1 DAY
5

Oke, this is a bit confusing for me.

I now have two tables, one for events and one for referee information.

In “events” table I have following information:
Column 1: Date
Column 2: Event_name
Column 3: Name (1st referee)
Column 4: Name (2nd referee)

In “referee” table i have this information:
Column 1: Name
Column 2: Email

Does my query then have to be

SELECT referees.email, events.date, events.event_name FROM events JOIN referees ON referees.name=events.name WHERE events.date=CURRENT_DATE+ INTERVAL 7 DAY OR events.date=CURRENT_DATE+ INTERVAL 1 DAY

I am not sure what these two lines do. Where does events_lut and referees.id come from?

JOIN events_lut ON referees.id = events_lut.ref_id
JOIN events ON events_lut.evt_id = events.id

Again, thank you so much for your help!! Although it is still hard to understand, it is getting better :slight_smile:

6

This is a third LUT (Look-Up Table).
It’s always best to deal with unique IDs in your database rather than things like people’s names. Every table should have a unique id column.

The events table may be like this:-

id | date | name

The referees table:-

id | name | email

And the events_lut table:-

id | ref_id | evt_id

Where ref_id (Referee ID) and evt_id (Event ID) are foreign keys relating to the ID columns in the other respective tables.

1 Like
7

Ah I understand now, thanks! Because if you for example make a spelling mistake in the name in one of the tables, my query will not return a result, but using ID spelling errors will not have any effect (this is what you mean, right?)

I got my query with only the two databases to work, so will now make a third database that will be the LUT and alter my query accordingly!

Now i just have to figure out if it is possible to send two emails to two different referees using a single php-mail line

8

That is one reason, the point is any table must have at least one column which is guaranteed to be totally unique for every row which can unambiguously identify it. Standard practice it to have an ID column as an integer with auto-increment on for this purpose.
The referees table may be like:-

1 | John | john@hotmail.com
2 | Paul | paul@hotmail.com
3 | George | george@hotmail.com
4 | Ringo | ringo@hotmail.com
5 | Rod | rod@gmail.com
6 | Jane | jane@gmail.com
7 | Freddy | freddy@gmail.com
8 | Zippy | zippy@gmail.com
9 | George | george@gmail.com

You have two Georges there, but not an issue because one is identified as number 3 and the other as number 9 in the table. So you negate any problem with duplicate names, if for example there are two people with the same name, or the same person gets their details put into the database by accident.

Then events may have:-

1 | 2017-09-23 | Semi Final A
2 | 2017-09-24 | Semi Final B
3 | 2017-09-30 | Grand Final
4 | 2017-10-01 | Runners Up
5 | 2018-09-22 | Semi Final A
6 | 2018-09-23 | Semi Final B
7 | 2018-09-29 | Grand Final
8 | 2018-09-30 | Runners Up

Again, duplicated names not a problem.

Then the LUT matches referees with events.

1 | 1 | 1
2 | 5 | 1
3 | 3 | 2
4 | 2 | 2
5 | 4 | 3
6 | 6 | 3

Here are the 2 refs assigned to the first 3 events.
So this year’s Semi Final A has “John” and “Rod” assigned to it, Etc…

Note the ID column on the LUT is not used, but it’s there “just because”.

1 Like
9

SamA74, thank you so much for your help!

I did what you advised me to do, giving every referee and event an individual ID, and the LUT put everything together!

Now I just have to adjust my excel workbook to give each referee their own ID :slight_smile:

Thanks a lot!

10

I’m not sure how you have Excel involved, but in mysql tables the addition of IDs should be totally automated by using “Auto Increment”.
There are libraries for working between PHP and Excel if that helps.

11

Well, someone else makes the referee schedule in excel. I usually import those to the MySQL database to show them on the site.

12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.