PHP Code Issues

The attached code pulls data from a mysql database & emails the requested data based off of the number on line 4 which is 366. Supervisor "John Doe" has 7 people who he supervises. When I run the script I only get the first 4 people in the database. I should be getting all 7 if I have 366 in on line 4. Why is this happening?
<?php
include('connection.php');

$number_of_days_before = 366;
$email = "myemail@yahoo.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, eval_due_date FROM hr_info WHERE supervisor = 'john 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\n";
}

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

Open in new window

LVL 15
wantabe2Asked:
Who is Participating?
 
Ray PaseurCommented:
This assumes that your data base table has defined the date columns as DATE or DATETIME.  Hopefully the comments and code are helpful.

You might also want to learn about this thing.  I find it very useful.
http://en.wikipedia.org/wiki/PhpMyAdmin

This is UNTESTED CODE -- I do not have your data base so I cannot test it for you, but you should be able to do that fairly easily.  Best of luck with the project, ~Ray
<?php // RAY_temp_wantabe.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.php');

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

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

// CONSTRUCT THE DATE BOUNDARIES FOR USE IN THE QUERY
$todays_date  = date('c');
$trigger_date = date('c', strtotime($todays_date . ' + 366 days'));

// CONSTRUCT THE QUERY IN A SEPARATE VARIABLE STRING
$sql = "SELECT f_name, l_name, eval_due_date FROM hr_info WHERE supervisor = 'john doe' AND eval_due_date BETWEEN '$todays_date' AND '$trigger_date' ";

// 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["f_name"] . " " . $row["l_name"] . "\n";
    $reminder_details .= "Evaluation Due Date: " . $row["eval_due_date"] . "\n\n";
}

// CREATE THE ADDITIONAL HEADERS FOR THE EMAIL
$mailheader = "From: Evaluation Reminder System <$email>\nX-Mailer: Reminder\nContent-Type:text/plain\r\n";

// SEND THE MAIL OR SHOW A DIAGNOSTIC MESSAGE
if (!mail("$email", "Evaluation(s) due within 366 days", "$reminder_details", "$mailheader")) echo "<br/>MAIL FAIL";

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

Open in new window

0
 
Ray PaseurCommented:
@wantabe: You might want to read this article that shows some of the things about how to handle DATETIME information in PHP and MySQL.  I'll take a look at your code in a moment.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
 
Ray PaseurCommented:
Put quotes around the date values and see if that helps.

$result = mysql_query( "SELECT f_name, l_name, eval_due_date FROM hr_info WHERE supervisor = 'john doe' AND eval_due_date BETWEEN '$todays_date' AND '$trigger_date' " );
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Ray PaseurCommented:
Next, buy this book.  It will show you some good design patterns and strategies to avoid the kind of errors that you have in this code!
http://www.sitepoint.com/books/phpmysql4/

This needs a complete rewrite.  I'll show you how and why in a few minutes.  Best, ~Ray
0
 
wantabe2Author Commented:
Thanks for the quick reply. I'll test & post back this evening. Thanks again!
0
 
wantabe2Author Commented:
It's working but it is not displaying the all the results...I can change the SQL:

$sql = "SELECT f_name, l_name, eval_due_date FROM hr_info WHERE supervisor = 'john doe' AND eval_due_date BETWEEN '$todays_date' AND '$trigger_date'";

to

$sql = "SELECT f_name, l_name, eval_due_date FROM hr_info WHERE supervisor = 'john doe' AND eval_due_date ";

just as a test & it does return all the people assigned to John Doe but when I add the BETWEEN '$todays_date' AND '$trigger_date'"; it will onl return 3 of the 7 that it should return. I tested by going into phpmyadmin & running both with the BETWEEN '$todays_date' AND '$trigger_date'"; & without & it returned the same as if I run it from the script...Any idea as to why  this is happening? It has something to do with the $trigger_date & $todays_date variable I think.


0
 
wantabe2Author Commented:
Let me rephrase that, I can run

SELECT f_name, l_name, eval_due_date FROM hr_info WHERE supervisor = 'john doe' AND eval_due_date BETWEEN '2011-10-12' AND '2012-10-11'

from phpmyadmin & get the same results...not returning all 7, it just returns 3..
0
 
wantabe2Author Commented:
Crap! I found the problem. The date that were not showing up were for the year 2010....dah!!...Thanks again for your help.
0
 
Ray PaseurCommented:
Thanks for the points.  Carefully crafted test data is a really big help.  You can see what I am talking about if you read this little article about the thought processes that go into Test-Driven Development.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_7830-A-Quick-Tour-of-Test-Driven-Development.html

Best regards, ~Ray
0
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.

All Courses

From novice to tech pro — start learning today.