?
Solved

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

Posted on 2014-10-07
9
Medium Priority
?
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 111

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 1200 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 800 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 111

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

CHALLENGE LAB: Troubleshooting Connectivity Issues

Goal: Fix the connectivity issue in the lab's AWS environment so that you can SSH into the provided EC2 instance.  

Question has a verified solution.

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

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
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 learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

764 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