asked on

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

ASKER CERTIFIED SOLUTION

Gary

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chadd C

ASKER

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.

SOLUTION

Gary

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray Paseur

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.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Chadd C

ASKER

Thanks!