Solved

PHP & MySQL Help Needed

Posted on 2013-11-07
6
504 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 43

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

911 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

19 Experts available now in Live!

Get 1:1 Help Now