Generate emails based on MySQL query

The endstate of my efforts is to create a way to identify users who have not completed a training item, then send them an email notifying them of the requirement to complete the training.

For my script, there are two tables.

users
user_id
email

user_training_items
user_id
item_id
date_completed

I need to query the user_training_items for user_id's with NULL date_completed entries. Then, using the user_id's, get the associated email addresses from the users table.  For each of the resultant email addresses, send an email.

Here is my script thus far:
<?php

require_once 'scripts/app_config.php';
require_once 'scripts/database_connection.php';

$query = "SELECT email FROM users t1 JOIN user_training_items t2 ON t1.user_id=t2.user_id WHERE t2.date_completed IS NULL";

$result = mysql_query($query) or die(mysql_error());

   while ($row = mysql_fetch_array($result)) {
       $recipients[] = $row['email'];
}

//These are the variables for the email 

$sendto = $row['email']; // this is the email address collected form the form 
$subject = "Training Notification"; // Subject of the email
$message = "Hello!  You have training items requiring your attention.  To complete the items, please visit:\n\n"; 
$message .= "http://www.mysite.com";
$header = "From: service@mysite.com\r\n"; 
$header .= "Reply-to: service@mysitem\r\n"; 

// This is the function to send the email 
mail($sendto, $subject, $message, $header);
?>

Open in new window

chaddcarrAsked:
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.

GaryCommented:
And the question is?
Wouldn't it be easier to put your email script in the WHILE since you have nothing in your code to loop the array you are creating
No point creating an array to just then loop through it on the next line

And remember as I said before you need to get off mysql_query and no better time than when you are creating the code.
0

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
chaddcarrAuthor Commented:
Good point! Fixed that.  Is there a way to limit the query so it only produces unique user_id's (email addresses)?  A user_id may have many entries in the user_training_items table.
0
GaryCommented:
Add to the end of your sql the below, this will ensure just the single row for each user_id

GROUP BY t1.user_id
0
Ray PaseurCommented:
Maybe something like this.  I cannot test it because I do not have your database of test data, but it should be right or fairly close.

<?php
error_reporting(E_ALL);

require_once 'scripts/app_config.php';
require_once 'scripts/database_connection.php';

$query = "SELECT DISTINCT email FROM users t1 JOIN user_training_items t2 ON t1.user_id=t2.user_id WHERE t2.date_completed IS NULL";

$result = mysql_query($query) or trigger_error(mysql_error());

// SET UP THE VARIABLES FOR THE EMAIL
$subject = "Training Notification"; // Subject of the email
$message = "Hello!  You have training items requiring your attention.  To complete the items, please visit:\n\n"; 
$message .= "http://www.mysite.com";
$header = "From: service@mysite.com\r\n"; 
$header .= "Reply-to: service@mysite.com\r\n"; 

while ($row = mysql_fetch_object($result)) 
{
    mail($row->email, $subject, $message, $header);
}

Open in new window

And you want to get off the MySQL extension as soon as you can, forever. This article explains why and what you must do to keep your scripts running in the future.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
chaddcarrAuthor Commented:
Thanks!
0
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
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.