?
Solved

PHP Code

Posted on 2013-11-21
2
Medium Priority
?
536 Views
Last Modified: 2013-11-22
The following code queries my MySQL database & sends an email based off of the SQL & then updates the email_sent field to a 0. I just need it to mark the email_sent field to a 0 where the employee is employee2. At this time, when it is ran, it marks ALL entries in the database email_sent field to a 0. How can I edit this code to only mark the email_sent field to a 0 where the employee is employee2? I have a unique id field named aid that is different on each entry....thanks for any help!

<?php 
error_reporting(E_ALL);

// BRING IN OUR COMMON INITIALIZATION SCRIPT
require_once('connection_test.php');

// WHO WILL WE SEND THE EMAIL TO?
$email = "my_email@yahoo.com";

// THIS WILL CONTAIN THE TEXT FOR THE EMAIL MESSAGE
$reminder_details = "";

$sql = "SELECT aid, o_name, additional_notes, clerk FROM assignments WHERE active = '1' AND email_sent = '1' AND employee = 'employee2' ";

// RUN THE QUERY AND SHOW DIAGNOSTICS IF THE QUERY FAILS
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, SHOW THE ERROR
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}

// GET THE NUMBER OF ROWS
$nr = mysql_num_rows($res);

// SHOW THE NUMBER OF ROWS
echo "<br/>QUERY FOUND $nr ROWS";

// IF THERE ARE ANY ROWS
if ($nr)
{
    // USE AN ITERATOR TO ACCESS EACH ROW OF THE RESULTS SET
    while ($row = mysql_fetch_assoc($res))
    {
        // ADD NAMES AND DATE TO THE EMAIL TEXT
        $reminder_details .= "O Name: " . $row["o_name"] . " \n";
        $reminder_details .= "Assigned Clerk: " . $row["clerk"] . " \n";
		$reminder_details .= "Instructions/Notes: " . $row["additional_notes"] . "\n \n";
    }

    // CREATE THE ADDITIONAL HEADERS FOR THE EMAIL
    $mailheader = "From: The System <$email>\nX-Mailer: Reminder\nContent-Type:text/plain\r\n";

    // SEND THE MAIL OR SHOW A DIAGNOSTIC MESSAGE
    if (!mail("$email", "New Assignment(s)", "$reminder_details", "$mailheader")) {
       echo "<br/>MAIL FAIL";
    } else {
       //mail sent so lets update the database
       $sql = "UPDATE assignments SET email_sent = '0' WHERE active = '1' AND email_sent = '1'";
       mysql_query($sql);
    }
}
else
{
    echo "NO EMAILS SENT";
}

Open in new window

0
Comment
Question by:wantabe2
2 Comments
 
LVL 84

Accepted Solution

by:
Dave Baldwin earned 2000 total points
ID: 39667396
In your UPDATE statement, the WHERE needs to include something like...

AND employee = 'employee2'

$sql = "UPDATE assignments SET email_sent = '0' WHERE active = '1' AND email_sent = '1' AND employee = 'employee2'";

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39667690
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Originally, this post was published on Monitis Blog, you can check it here . It goes without saying that technology has transformed society and the very nature of how we live, work, and communicate in ways that would’ve been incomprehensible 5 ye…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses
Course of the Month17 days, 2 hours left to enroll

864 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