?
Solved

PHP Code

Posted on 2013-11-21
2
Medium Priority
?
513 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 83

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

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…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

771 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