Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Generate emails based on MySQL query

Avatar of Chadd C
Chadd C asked on
PHPMySQL Server
5 Comments1 Solution1011 ViewsLast Modified:
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);
?>