Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

PHP Code Issues

Posted on 2011-10-11
9
Medium Priority
?
191 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:wantabe2
  • 5
  • 4
9 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36949143
@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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36949157
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36949168
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 36949293
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
 
LVL 15

Author Comment

by:wantabe2
ID: 36949969
Thanks for the quick reply. I'll test & post back this evening. Thanks again!
0
 
LVL 15

Author Comment

by:wantabe2
ID: 36955348
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
 
LVL 15

Author Comment

by:wantabe2
ID: 36955391
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
 
LVL 15

Author Comment

by:wantabe2
ID: 36955527
Crap! I found the problem. The date that were not showing up were for the year 2010....dah!!...Thanks again for your help.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36955627
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this tutorial viewers will learn how to embed Flash content in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: "<!DOCTYPE html>": Use the <object> tag to embed Flash content.: To specify that the object is Flash content, d…
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…
Suggested Courses

569 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