Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PHP Code - Where is my mistake?

Posted on 2011-09-30
15
Medium Priority
?
297 Views
Last Modified: 2012-05-12
The attached code is supposed to look at the eval_due_date in a mysql database & if the eval_due_date is within 60 days of todays date, it is supposed to send an email to myemail@gmail.org

When I run it it acts like it works & I get the attachment in the browser but I get no email??? Thanks for any help provided.
<?php
include('connection.php'); 

$number_of_days_before = 60;
$email = "myemail@gmail.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 f_name, l_name FROM hr_info WHERE supervisor = 'john doe' AND eval_due_date BETWEEN $todays_date AND $trigger_date" );
$nr = mysql_num_rows( $result );

 if (mysql_errno()) {
  echo "MySQL error ".mysql_errno().": ".mysql_error()."\n<br>";

$reminder_details .= "Name: ".$row["f_name"]." ".$row["l_name"]."\n";
$reminder_details .= "Evaluation Due Date: ".$row["eval_due_date"]."\n\n\n";
}

if( !empty( $nr ) )
{

$mailheader = "From: Evaluation Reminder System <$email>\nX-Mailer: Reminder\nContent-Type:text/plain";
mail("$email", "Evaluation(s) due within 60 days", "$reminder_details", "$mailheader");

}
?>

Open in new window

attatch.JPG
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
  • 6
  • 4
  • 2
  • +2
15 Comments
 
LVL 13

Expert Comment

by:Hugh McCurdy
ID: 36892539
I don't see an obvious problem.  A good next step is to print debugging information.

if( !empty( $nr ) )
{

echo "attempting to send message<br />\n";

$mailheader = "From: Evaluation Reminder System <$email>\nX-Mailer: Reminder\nContent-Type:text/plain";
$sent = mail("$email", "Evaluation(s) due within 60 days", "$reminder_details", "$mailheader");

var_dump ( $sent ); echo "<br />\n";
var_dump ( $email ); echo "<br />\n";
var_dump ( $reminder_details); echo "<br />\n";
var_dump ( $mailheader); echo "<br />\n";

}

Open in new window


Caveat: I didn't test my additions.

If the problem becomes obvious after that, then fix it.  If not, copy/paste the relevant output here.
0
 
LVL 13

Expert Comment

by:Hugh McCurdy
ID: 36892549
You also might want to check your error_log file (if you have access to it) for recent error messages.
0
 
LVL 10

Expert Comment

by:stu215
ID: 36892580
Have you tried sending a basic mail to see if you can send mail at all from the server?
<?php
// The message
$message = "Line 1\nLine 2\nLine 3";

// In case any of our lines are larger than 70 characters, we should use wordwrap()
$message = wordwrap($message, 70);

// Send
mail('yourEmail@example.com', 'My Subject', $message);
?> 

Open in new window


Did you try checking your mailError.log file to see if maybe the issue is not with the script?
http://help.yahoo.com/l/us/yahoo/smallbusiness/webhosting/php/php-03.html

You'll find your error logs for PHP and Perl mail in your tmp subdirectory, in a log file called mailError.log. Messages will be saved to this file automatically whenever an error occurs.
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 31

Expert Comment

by:Marco Gasi
ID: 36892622
try without double quotes (they are unneeded)

mail($email, "Evaluation(s) due within 60 days", $reminder_details, $mailheader);
0
 
LVL 15

Author Comment

by:wantabe2
ID: 36892728
okay....it has started emailing me now when I run the script. There must have been something wrong with the mail server (backlogged or something) I got numerous emails. okay, now that I'm getting the emails I get the subject line I have in the code but no results int eh body of the email....
0
 
LVL 31

Accepted Solution

by:
Marco Gasi earned 1000 total points
ID: 36892790
$result = mysql_query( "SELECT f_name, l_name FROM hr_info WHERE supervisor = 'john doe' AND eval_due_date BETWEEN $todays_date AND $trigger_date" );
$nr = mysql_num_rows( $result );

 if (mysql_errno()) {
  echo "MySQL error ".mysql_errno().": ".mysql_error()."\n<br>";

while ($row = mysql_fecth_array($result)){
  $reminder_details .= "Name: ".$row["f_name"]." ".$row["l_name"]."\n";
  $reminder_details .= "Evaluation Due Date: ".$row["eval_due_date"]."\n\n\n";
}
}

Cheers
0
 
LVL 15

Author Comment

by:wantabe2
ID: 36893125
marqusG
still the same thing..I get an email but with no results. There is data within the date range that should be sent via the email but it is not getting put inthe body of the message.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36893245
To learn the correct ways of handling DATETIME values in PHP and MySQL you might want to read over this article.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36893297
Try this and see if it makes sense to you.  I am assuming that the column named eval_due_date is of DATETIME data type, right?  Sorry I cannot test this because I do not have your data base, but hopefully it will work or nearly work.  At least you will be able to see the query string, and know whether the query worked or not!
<?php
include('connection.php'); 

$number_of_days_before = 60;
$email                 = "myemail@gmail.org";
$reminder_details      = NULL;
$todays_date           = date("c");
$trigger_date          = date("c", strtotime($todays_date . ' + ' .$number_of_days_before . ' DAYS'));

$query  = "SELECT f_name, l_name FROM hr_info WHERE supervisor = 'john doe' AND eval_due_date BETWEEN $todays_date AND $trigger_date LIMIT 1";
$result = mysql_query($query);
if (!$result) die("QUERY FAILED: $query<br/>" . mysql_error() );
$row = mysql_fetch_assoc($result);
if ($row)
{
    $reminder_details .= "Name: ".$row["f_name"]." ".$row["l_name"]."\n";
    $reminder_details .= "Evaluation Due Date: ".$row["eval_due_date"]."\n\n\n";
}
else
{
    die("NOTHING FOUND BY QUERY $query");
}
echo "SUCCESS: $query";
$mailheader = "From: Evaluation Reminder System <$email>\nX-Mailer: Reminder\nContent-Type:text/plain\r\n";
mail("$email", "Evaluation(s) due within 60 days", "$reminder_details", "$mailheader");

Open in new window

0
 
LVL 15

Author Comment

by:wantabe2
ID: 36893448
still nothing:

QUERY FAILED: SELECT f_name, l_name FROM hr_info WHERE supervisor = 'john doe' AND eval_due_date BETWEEN 2011-09-30T18:14:03+00:00 AND 2012-09-28T18:14:03+00:00 LIMIT 1
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':14:03+00:00 AND 2012-09-28T18:14:03+00:00 LIMIT 1' at line 1

There is data in the DB & the eval_due_date is the DATE data type. I change dit to DATETIME & ran the above code with no luck either...
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36893491
Use single quotes around the datetime strings.
0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 1000 total points
ID: 36893504
But I am wondering... Did you run exactly the script I posted, or did you make some other changes?  Here is the script with the quotes in the right places in the query.  Sorry I cannot test any of this because I do not have your data base, so you will have to do the testing for us, and post the results back here.
<?php
include('connection.php'); 

$number_of_days_before = 60;
$email                 = "myemail@gmail.org";
$reminder_details      = NULL;
$todays_date           = date("c");
$trigger_date          = date("c", strtotime($todays_date . ' + ' . $number_of_days_before . ' DAYS'));

$query  = "SELECT f_name, l_name FROM hr_info WHERE supervisor = 'john doe' AND eval_due_date BETWEEN '$todays_date' AND '$trigger_date' LIMIT 1";
$result = mysql_query($query);
if (!$result) die("QUERY FAILED: $query<br/>" . mysql_error() );
$row = mysql_fetch_assoc($result);
if ($row)
{
    $reminder_details .= "Name: ".$row["f_name"]." ".$row["l_name"]."\n";
    $reminder_details .= "Evaluation Due Date: ".$row["eval_due_date"]."\n\n\n";
}
else
{
    die("NOTHING FOUND BY QUERY $query");
}
echo "SUCCESS: $query";
$mailheader = "From: Evaluation Reminder System <$email>\nX-Mailer: Reminder\nContent-Type:text/plain\r\n";
mail("$email", "Evaluation(s) due within 60 days", "$reminder_details", "$mailheader");

Open in new window

0
 
LVL 15

Author Comment

by:wantabe2
ID: 36893798
Ray,
Thanks for your help. Between my code & what you posted I was able to get it working 90%. The code below is only returning i result in the email I recieve....I know I probably don't have a ; or () somewhere but just can't seem to find it. Any suggestions before I award the points? Thanks again for your assistance.
<?php
include('connection.php'); 

$number_of_days_before = 364;
$email = "myemail@yahoo.com";
$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 hrid, f_name, l_name, eval_due_date FROM hr_info WHERE supervisor = 'john doe' AND eval_due_date BETWEEN $todays_date AND $trigger_date" );

$row = mysql_fetch_assoc($result);
if ($row)
{
    $reminder_details .= "Name: ".$row["f_name"]." ".$row["l_name"]."\n";
    $reminder_details .= "Evaluation Due Date: ".$row["eval_due_date"]."\n";
}

$mailheader = "From: Evaluation Reminder System <$email>\nX-Mailer: Reminder\nContent-Type:text/plain\r\n";
mail("$email", "Evaluation(s) due within 60 days", "$reminder_details", "$mailheader");

Open in new window

0
 
LVL 15

Author Comment

by:wantabe2
ID: 36893803
oh, one more thing to add...the one result I get back in the email is not the one you'd think it would be. It skips October & returns an entry for December... hth
0
 
LVL 15

Author Comment

by:wantabe2
ID: 36894148
I was able to get this working by using the code posted by ray & by adding the while( $row = mysql_fetch_array( $result ) ) to the code.
<?php
include('connection.php'); // DB connection
// Constants
$number_of_days_before = 60;
$email = "email@yahoo.com";
$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 f_name, l_name, eval_due_date FROM hr_info WHERE supervisor = 'Jane Doe' AND eval_due_date BETWEEN $todays_date AND $trigger_date" );
$nr = mysql_num_rows( $result );

$row = mysql_fetch_assoc($result);
while( $row = mysql_fetch_array( $result ) )
if ($row)
{
    $reminder_details .= "Name: ".$row["f_name"]." ".$row["l_name"]."\n";
    $reminder_details .= "Evaluation Due Date: ".$row["eval_due_date"]."\n";

}
$mailheader = "From: Evaluation Reminder System <$email>\nX-Mailer: Reminder\nContent-Type:text/plain\r\n";
mail("$email", "Evaluation(s) due within 60 days", "$reminder_details", "$mailheader");

Open in new window

0

Featured Post

Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

Question has a verified solution.

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

Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
When the s#!t hits the fan, you don’t have time to look up who’s on call, draft emails, call collaborators, or send text messages. An instant chat window is definitely the way to go, especially one like HipChat. HipChat is a true business app. An…
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

688 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