Solved

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

Posted on 2014-10-07
9
218 Views
Last Modified: 2014-10-28
I have two mysql tables (course & student)  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.
0
Comment
Question by:davidgm
  • 3
  • 3
  • 3
9 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

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.
0
 
LVL 11

Accepted Solution

by:
tel2 earned 300 total points
Comment Utility
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):
SELECT
  student.courseID,
  student.email,
  student.reg_date,
  course.course_name
FROM
  course,
  student
WHERE
  course.courseID = student.courseID AND
  student.reg_date BETWEEN FROM_DAYS(TO_DAYS(NOW())+3) AND FROM_DAYS(TO_DAYS(NOW())+4)

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
else
  $msg = $msg2
end if
Send $msg to $email address

Open in new window

0
 

Author Comment

by:davidgm
Comment Utility
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).
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
Comment Utility
This and more about handling DATETIME values can be found in the article here.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

Personally, I like to use PHP for things like this.
<?php

// THE course_date COLUMN IS DEFINED AS 'DATE'

// GET THE ISO-8601 DATE FOR THREE DAYS HENCE
$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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 11

Expert Comment

by:tel2
Comment Utility
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?
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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.
0
 

Author Comment

by:davidgm
Comment Utility
tel2;
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.
0
 
LVL 11

Expert Comment

by:tel2
Comment Utility
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:
    WHERE
      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.
0
 

Author Comment

by:davidgm
Comment Utility
Still working on this. Will close it soon.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now