Simple comparison of dates

[joesaddigh]

I am trying to simply compare todays date with a date from the database and am having some problems.

 

The code below outlines my problem, the startdate variable contains the startdate from the db but even if it is greater than todays date the figure that the strtodate outputs is considerably lower.

 

//Get the current date based on the system date
$currentdate = date("d-m-y"); 

//In the loop need to compare the course dates for each course that the student is enrolled on
while($rowstudentcourse = mysql_fetch_array($resultstudentcourse)) 
{
	//Get the course details
	$courseid = $rowstudentcourse['CourseID'];
	$startdate = $rowstudentcourse['StartDate'];
	$enddate = $rowstudentcourse['EndDate'];


	echo 'Startdate'. $startdate2 = strtotime($startdate);
	echo '<br />';
	echo 'Todays date'. $today = strtotime($currentdate);
	echo '<br />';

	//Check to see if the startdate of the course has already expired
	if ($startdate2 > $today) 
	{ 
		echo $valid = "The startdate is in the future"; 
		echo '<br />';
	} 
	else 
	{ 
		echo $valid = "The startdate is in the past"; 
		echo '<br />';
	} 

 

 

The output from this code is as follows

 

Startdate1241478000

Todays date1773014400

The startdate is in the past

Startdate1233532800

Todays date1773014400

The startdate is in the past

Startdate1238112000

Todays date1773014400

The startdate is in the past

 

The startdates that are coming from the database are

05-05-2009

02-02-2009

27-03-2009

 

 

If anybody knows what i am missing then i would really appreciate your advice

 

Thanks,

 

 

[mrfitz]

Hi, i didn't spend much time on this, but if the date string from the database is formatted as a date field the date is stored in the database in format yyyy-mm-dd you would have to use :

$r = mysql_query("select  DATE_FORMAT(ad_date,'%m-%d-%Y') from " . $db_prefix . "gcadds WHERE ad_date >= '" . $todayis . "' order by ad_date DESC");

 

The key here is the DATE_FORMAT() sql function.

mrfitz

[joesaddigh]

Hi,

 

Thanks for your suggestion!

 

Just to clarify my date that is stored in the database is a string value..

 

I have done what you have suggested and now when i try to echo the startdate to the screen It does not display. Do i need to do something other than just call it from the while loop as an array value. When i print it to the screen it is telling me that:

 

 

Notice: Undefined index: StartDate in C:\wamp\www\CIS School\Admin\deletestudentsubmit.php on line 37

 

Notice: Undefined index: EndDate in C:\wamp\www\CIS School\Admin\deletestudentsubmit.php on line 38

Startdate

Todays date1804550400

The startdate is in the past

 

 

 

My edited code is below:

 

 

$querystudentcourse = "SELECT DATE_FORMAT('StartDate','%d-%m-%Y'), DATE_FORMAT('EndDate','%d-%m-%Y'), CourseID FROM studentcourse WHERE StudentID =".$studentid;

$resultstudentcourse=mysql_query($querystudentcourse) 
	or die("Error getting details of the courses that a student is enrolled on");


//Get the current date based on the system date
$currentdate = date("d-m-y"); 

//In the loop need to compare the course dates for each course that the student is enrolled on
while($rowstudentcourse = mysql_fetch_array($resultstudentcourse)) 
{
	//Get the course details
	$courseid = $rowstudentcourse['CourseID'];
	echo $startdate = $rowstudentcourse['StartDate'];
	echo $enddate = $rowstudentcourse['EndDate'];


	echo 'Startdate'. $startdate2 = strtotime($startdate) . $startdate ;
	echo '<br />';
	echo 'Todays date'. $today = strtotime($currentdate);
	echo '<br />';
       }

 

Thanks

 

 

[kenrbnsn]

Change the way you store the date in the database. Alway store dates as DATE, not VARCHAR.

 

Also, to get the current date's UNIX time number, use time

 

Ken

[Yesideez]

I agree - PHP does a lot of work so why not take some of the load away and give it to MySQL when it comes to dates?

 

The formatting of dates in the MySQL database (as already pointed out) is YYYY-MM-DD so sorting and ordering as it is can be done without having to translate - it's only when you output to the browser you might want to change the date format.

[joesaddigh]

Ok thanks for your help. I was trying to avoid changing the database structure in this instance as i have very little time and just wanted a working prototype. Changing the database will mean that I will have to change lots of other areas of my php script.

 

Thanks for your help

 

:-)

[mrfitz]

I agree - PHP does a lot of work so why not take some of the load away and give it to MySQL when it comes to dates?

 

The formatting of dates in the MySQL database (as already pointed out) is YYYY-MM-DD so sorting and ordering as it is can be done without having to translate - it's only when you output to the browser you might want to change the date format.

 

Yep, I concur this needs to be stored as a date value in you mysql table.

Mrfitz