Solved

PHP Code

Posted on 2013-11-21
2
464 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 82

Accepted Solution

by:
Dave Baldwin earned 500 total points
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This article discusses four methods for overlaying images in a container on a web page
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ā€¦
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.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now