Solved

PHP & MySQL Help Needed

Posted on 2013-11-07
6
501 Views
Last Modified: 2013-11-08
The code below works perfect. The only thing I need edited is, after the code runs, is there a way for me to update the email_sent field to a 0

The database name is district_db & the table is named assignments & the field name is email_sent

Thanks

<?php 

// ALWAYS SHOW ALL OF THE ERROR MESSAGES WHEN WE ARE DEVELOPING OUR CODE
error_reporting(E_ALL);

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

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

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

$sql = "SELECT name, additional_notes FROM assignments WHERE active = '1' AND email_sent = '1'";

// 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);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS

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

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

// 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 .= "Name: " . $row["name"] . " \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", "You have a new assignment", "$reminder_details", "$mailheader")) echo "<br/>MAIL FAIL";

// SHOW WHAT WE SENT
echo "<pre>";
var_dump($reminder_details);

Open in new window

0
Comment
Question by:wantabe2
  • 3
  • 2
6 Comments
 
LVL 42

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 250 total points
ID: 39631776
Something like this to replace your line 48:

if (!mail("$email", "You have a new assignment", "$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);
}

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39633269
Sooner or later you're going to have a problem with this script, because PHP is doing away with MySQL support, so you will need a different data base extension.  This article explains why and what you must do to keep the script 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

As written, it appears that the script will only send one email message.  Is that right?  Or do you expect more than one row in the results set from the query on line 18?  If you get several rows, we need to reorganize the script a little bit so each of the rows is updated (I think).  Let us know, thanks. ~Ray
0
 
LVL 15

Author Comment

by:wantabe2
ID: 39633503
That's great & it works just like I need it to thanks! The only thing now is, & I don't know why, if the email_sent field is a 0 it will send a blank email. If the email_sent field is a 1 it will send that data in the body of the email like I need it to. Here's the final code:

<?php 

// ALWAYS SHOW ALL OF THE ERROR MESSAGES WHEN WE ARE DEVELOPING OUR CODE
error_reporting(E_ALL);

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

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

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

$sql = "SELECT name, additional_notes, clerk FROM assignments WHERE active = '1' AND email_sent = '1'";

// 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";

// 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 .= "Name: " . $row["name"] . " \n";
    $reminder_details .= "Instructions/Notes: " . $row["additional_notes"] . "\n";
	$reminder_details .= "Assigned Clerk: " . $row["clerk"] . " \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", "You have a new assignment", "$reminder_details", "$mailheader")) echo "<br/>MAIL FAIL";

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);
}

// SHOW WHAT WE SENT
echo "<pre>";
var_dump($reminder_details);

Open in new window

0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 108

Accepted Solution

by:
Ray Paseur earned 250 total points
ID: 39633558
if the email_sent field is a 0 it will send a blank email.
That's probably because the script doesn't make any test for the number of rows -- it just assumes there will be some rows, and if there are not, it still sends the email.

Find this part, and test for zero rows.  If zero rows are found, skip sending the email.
// GET THE NUMBER OF ROWS
$nr = mysql_num_rows($res);

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

Open in new window

HTH, ~Ray
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39633569
Warning: Untested code.
<?php 
error_reporting(E_ALL);

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

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

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

$sql = "SELECT name, additional_notes, clerk FROM assignments WHERE active = '1' AND email_sent = '1'";

// 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 .= "Name: " . $row["name"] . " \n";
        $reminder_details .= "Instructions/Notes: " . $row["additional_notes"] . "\n";
        $reminder_details .= "Assigned Clerk: " . $row["clerk"] . " \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
 
LVL 15

Author Closing Comment

by:wantabe2
ID: 39633600
THANKS!!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This article discusses how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

757 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

17 Experts available now in Live!

Get 1:1 Help Now