PHP Code Help Needed

The following code looks at my MySQL database at the due_owner field. This is a date field & if the due_owner date is within (greater than) 6 days of the current date, I need it to send an email to the $email variable.  Also, is there ANY way for me to adjust this code so it will stop sending emails after the date listed in the due_owner field?

At this time, the email address listed in the $email variable still gets an email after the date in the due_owner field. If this is not possible, is it possible to adjust the code so it will only send the email one time? I have this code as part of a script that runs each night at midnight...Thanks
<?php
include('connection.php');
$number_of_days_before = 6;
$email = "employee1@email.org";
$reminder_details = "";
$todays_date = date( "Ymd" );
echo $todays_date;
$year = substr($todays_date, 0, 4);
$month = substr($todays_date, 4, 2);
$date = substr($todays_date, 6, 2);
$trigger_date = date("Ymd", mktime(0,0,0,$month, $date-$number_of_days_before,$year));
echo $trigger_date;
$result = mysql_query( "SELECT due_owner, employee, pacts, dock, name, lname FROM psrinfo WHERE employee='Jane Doe' AND due_owner <= $trigger_date" );
$nr = mysql_num_rows( $result );
while( $row = mysql_fetch_array( $result ) )
{
$reminder_details .= "Name: ".$row["fname"]." ".$row["lname"]."\n";
$reminder_details .= "PACTS Number: ".$row["pacts"]."\n";
$reminder_details .= "Dock: ".$row["dock"]."\n";
$reminder_details .= "Due to Owner: ".$row["due_owner"]."\n\n\n";
}
mysql_free_result( $result );
if( !empty( $nr ) )
{
$mailheader = "From: The Reminder System <$email>\nX-Mailer: Reminder\nContent-Type:text/plain";
mail("$email", "Due to Within 6 Days", "$reminder_details", "$mailheader");
}
?>

Open in new window

LVL 15
wantabe2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tom BeckCommented:
>>is it possible to adjust the code so it will only send the email one time?

What if you add a column to the database that takes a boolean value that indicates whether a reminder email has been sent already or not. Check for the value before sending. Write 'True' in that column after sending.
0
wantabe2Author Commented:
I can add a column to the database named "mail_sent" no problem....I'm unsure how to edit the code to write a true value & check for that value before it sends the email though.

0
Tom BeckCommented:
I'm not in a position to write code, as I'm heading out the door for my morning commute, but to check the value of mail_sent you would include that column in your select statement then enclose your email generation code in a conditional IF block that asks "If value of mail_sent is 0, send the email".

Adding true to the column after sending the email would look something like this:

$insertQuery = "UPDATE psrinfo SET mail_sent=1 WHERE employee='Jane Doe'";
mysql_query($insertQuery) or die ('Error updating database');
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.