[SOLVED] Date & Time - Archiving Results?

[Solarpitch]

Hey,

 

I have the below script that is supposed to archive articles that are older than 80 days and display them on a different page, but it doesnt seem to work. I have this script that will calculate an archive date and return results before and after that date depending on whether the user is viewing the archives.

 

I should mention my date format in the DB is ..eg:  "April 7, 2008"

 

<?php 

	   $days = 80; 

                   // Get todays date and minus 80 days from it to set the archive date
	   $archive = date("F j, Y",strtotime("-{$days} days"));
	  

                  // Check to see if the archive page is being viewed, if yes then get results that are older than the archive date we set above
	  if($_GET['archive'] == 1)
	  {
	  	$sql = "article_timestamp <= '".$archive ."'";

	  }
	  else
	  {
	    $sql = "article_timestamp >= '".$archive ."'";
	  }

                 // Now ad the corresponding sql insert to the query

$sql = "select * from article where $sql AND article_cat = 'Latest News' ORDER BY article_id desc";


?>

[PFMaBiSmAd]

That date format cannot be directly sorted or compared using any greater-than/less-than operators, because the fields it is composed of it are not ordered, left to right, most-significant field to least-significant field.

 

In a date, the most significant field is the year (largest number), then month, then the least significant field is the day  (smallest number.) A sql DATE or DATETIME data type is formated yyyy-mm-dd for a reason. The fields it is made of are ordered left to right, most-significant field to least-significant field. This allows sorting and any kind of comparisons to work.

 

You could use the mysql STR_TO_DATE() function in your query to get a standard yyyy-mm-dd value from your existing date that you can than compare with a yyyy-mm-dd value of your choice, but this will make your query run slow as every value in every row will be passed through the STR_TO_DATE() function in order to do the comparison.

 

It would be better for your site's performance if you change your existing date column to a standard mysql DATE type. To retrieve a DATE type in any format you want, simply use the mysql DATE_FORMAT() function in your query.

[Solarpitch]

Ah, I understand. Thats what I was thinking... I had a feeling the system was unable to sort using that date format but wasnt sure. I'll just change the DATE type in the MySQL table to a more standard one that will allow for me to arrange them < >.

 

Thanks for that!