Join two mysql tables and loop to send a reminder message based on values obtained

I have two mysql tables (coursestudent)  with courseID in both of them. In table course there is another (text) field called course_name that identifies whether the course is firsttime or renewal. That is, its value is either "firsttime" or "renewal".  In table student I have a second field called email and a third field called reg_date (with a date in it).

I need to query table student to get the emails with a date - 3 days from today - and send them a reminder message using send mail. However, the message (msg1 or msg2) to be sent depends on whether it is first_time or renewal course, hence, necessitating the joining of the tables. Joining the tables would enable finding which course it firsttime and which one renewal to send the message accordingly. How would I do the join and loop using these table names and fields?

Your input is deeply appreciated.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
This isn't really a question with an answer, so much as a requirement for application development, and for something like that it might make sense to hire a professional developer.  It would not cost you much money and it would get you good results quickly.

If you're new to PHP and MySQL, and you want to learn, this article can help you find your way to good learning resources.

If you can please post the CREATE TABLE statements for these tables and a bit of test data to load them, we can probably give you a tested and working code sample.
Hi David,

As a general rule, I agree with Ray that it would be good if you supply the CREATE TABLE statements, so we can be more sure we are heading down the right path, but you may have provided enough info for me to do part of this.  I'll probably leave the rest to PHP experts, if you can't work it out yourself.

Please answer the following questions, David:
Q1. Can one student do only one course at a time?  (Looks as if each student record is associated with only 1 courseID, though I guess multiple student records could exist for each student, but that would result in duplication of records with the same email address, which is not ideal).
Q2. Are you sure your 'course_name' field contains 'firsttime' or 'renewal' rather than the name of the course?  If so, it sounds a bit (or even a byte) misnamed, and it would have been better to have something like a boolean 'firsttime' field which is true or false.
Q3. What field is the name of the course stored in?
Q4. Is this program going to run once a day?
I would recommend that you add a flag to the student table to indicate that the message has been sent, because if it fails to run one day, this could then be used to detect that a message is pending, but the code below would need to change a bit to cater for this.  And if it ever runs twice in a day, or fails part way through a batch, then the flag would help avoid sending duplicate messages.  But I will not try to cater for any of this here.

I think the SQL (MySQL) could be done something like this (implicit join on courseID):
  course.courseID = student.courseID AND

Open in new window

You might want to change the '+3' to '+2' and the '+4' to '+3' depending on what time the program runs and how you define 3 days before.

Execute the above, and within your loop do something like this (this is pseudo-code, not PHP):
if $course_name = 'firsttime'
  $msg = $msg1
  $msg = $msg2
end if
Send $msg to $email address

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
davidgmAuthor Commented:
The date would be three days from the current date. Therefore, if today's date is Oct. 8, 2014, a reminder would be sent only to those whose course starts Oct. 11 (i.e., 8+3).
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Ray PaseurCommented:
This and more about handling DATETIME values can be found in the article here.

Personally, I like to use PHP for things like this.


$due = date('Y-m-d', strtotime('Today + 3 days'));
$sql = "SELECT... WHERE course_date = '$due'";

Open in new window

But that aside, what if the notice is to go out on a Friday, but the following Monday is a holiday?  You may want to consider business days and not just calendar days in your application design.
Hi David,

My date test was not quite right.  I was thinking of datetime fields, (and my code still may have been slightly wrong), but since you're dealing with a date field, you could change my code from this:
       student.reg_date BETWEEN FROM_DAYS(TO_DAYS(NOW())+3) AND FROM_DAYS(TO_DAYS(NOW())+4)
to this:
       student.reg_date = FROM_DAYS(TO_DAYS(NOW())+3)
Ray has given an alternative way of doing this test, which uses PHP to do some of the work that SQL is doing in my code.  It looks as if mine is more concise, but there may be advantages with Ray's method.  Ray, can you comment on the differences?

What were the answers to my questions Q1-Q4 in my last post?
Ray PaseurCommented:
On the use of PHP vs MYSQL to compute the date of a day three days from now...

It probably does not make any difference if you define the problem narrowly.  Three days in the future is just that, and either engine can make the computation.  

There will not be a performance advantage either way, since this computation will occur in something like 0.000001 second.  You can't optimize something that small and get any measurable value for your effort.

The column definition is in play, hence the request for the CREATE TABLE statement.  You may achieve different results from a DATE column and a DATETIME column.  For more on this issue, see the very good article "Beware of Between."

Where might PHP may have an advantage?  If you think about the question of "business days," the definition of "3 days into the future" changes somewhat.  I have an example of the issue and ways to handle it in the Date/Time article.  See "Practical Application #9" in the article.
davidgmAuthor Commented:
Q1. There is no need to keep student records. Once course is over, records will be wiped out (yes, sounds strange!)
Q2. Astute observation on your part. I tried to simplify the question. It is the name of the course from which I get the relevant info to know whether first time or repeat.
Q3. See Q2 above.
Q4. Yes.

Again good observation on your part. I do have a field to indicate a message has been sent.

I am in the process of setting up the cron job to test.
Thanks Ray.

Thanks for your answers, David.
Since you do have a field to indicate that the message has been sent, I would suggest you change the WHERE condition as to this:
      course.courseID = student.courseID AND
      student.reg_date <= FROM_DAYS(TO_DAYS(NOW())+3) AND
      student.msg_status <> 'Sent'     -- This line depends on your column name, type and values.

Open in new window

The reasons for this are given below my Q4 in my 1st post above.  You would then need to add an UPDATE statement to change the msg_status, of course.

If you change the msg_status field (or whatever you call it) to be a datetime that the msg was sent, that could be good for trouble-shooting/audit purposes.  Otherwise, ENUM might be a good type for this field, as it allows you to store user-friendly stati like 'Sent' in a single byte.
davidgmAuthor Commented:
Still working on this. Will close it soon.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.