Solved

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

Posted on 2014-10-07
9
225 Views
Last Modified: 2014-10-28
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.
0
Comment
Question by:davidgm
  • 3
  • 3
  • 3
9 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 40367296
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 12

Accepted Solution

by:
tel2 earned 300 total points
ID: 40367567
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
ID: 40368754
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
ID: 40368775
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
 
LVL 12

Expert Comment

by:tel2
ID: 40369541
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 109

Expert Comment

by:Ray Paseur
ID: 40370611
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
ID: 40371329
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 12

Expert Comment

by:tel2
ID: 40371865
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
ID: 40397785
Still working on this. Will close it soon.
0

Featured Post

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
This article will show, step by step, how to integrate R code into a R Sweave document
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

861 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