?
Solved

Generate emails based on MySQL query

Posted on 2014-08-02
5
Medium Priority
?
737 Views
Last Modified: 2014-08-02
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

0
Comment
Question by:chaddcarr
  • 2
  • 2
5 Comments
 
LVL 58

Accepted Solution

by:
Gary earned 600 total points
ID: 40236732
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
 

Author Comment

by:chaddcarr
ID: 40236742
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
 
LVL 58

Assisted Solution

by:Gary
Gary earned 600 total points
ID: 40236744
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
 
LVL 111

Expert Comment

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

Author Closing Comment

by:chaddcarr
ID: 40236808
Thanks!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

850 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