[SOLVED] Selecting record dates before a particular date.

[poleposters]

Hi,

 

I'm writing a script which displays links to the  previous and next blog entries of the current entry.

 

The dates are stored in DATE format.

 

This is the code I've written.

$query="SELECT * FROM blog WHERE blog_date < $blogdate LIMIT 2";
$result=mysql_query($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());
$num=mysql_num_rows($result);
	if($num>0)
		{
		while($blog=mysql_fetch_array($result))
			{
			$link=$blog['blog_title'];
			print "<a href=\"http://localhost/blog/title/$link/\" class=\"right\">$link</a>";

			}

		}
$limit=4-$num;		
$query2="SELECT * FROM blog WHERE blog_date > $blogdate LIMIT $limit ";
$result2=mysql_query($query2) or trigger_error("Query: $query2\n<br />MySQL Error: " . mysql_error());
	if(mysql_num_rows($result2)>0)
		{
		while($blog2=mysql_fetch_array($result2))
			{
			$link2=$blog2['blog_title'];
			print "<a href=\"http://localhost/blog/title/$link2/\" class=\"right\">$link2</a>";

			}

		}





		;

 

The next entries display fine. But the previous ones not at all. Even though they exist in the database.

 

I think i've isolated the problem. If I change the lessthan symbol to a morethan symbol. It works.

 

I've never worked with dates before.Am I doing something wrong?

 

[PFMaBiSmAd]

What exactly does $blogdate have in it and what exactly does blog_date have in it for the row(s) you are expecting the query to match?

[poleposters]

I should of mentioned that.

 

$blogdate contains the date of the current blog, retrieved from the database without modification.

 

An example is 2009-07-25

 

 

[PFMaBiSmAd]

So, if you have blogs on the same date as $blogdate, you are going to ignore them?

 

I asked you to show exactly what the values were for the row(s) that you expected to be matched by the query for a reason.

[poleposters]

Sorry I misunderstood.

 

$blogdate contains 2009-07-25.

 

blog_date from the matching records are 2009-07-24 , 2009-07-23 for previous dates

 

and 2009-07-26, 2009-07-27 for the next dates.

 

I'll be ignoring records from dates that equal $blogdate

[PFMaBiSmAd]

I just tried your code and it works as expected, two links before and two links after a $blogdate of "2009-07-25".

 

Are you sure there is a blog_title for each entry and have you done a "view source" of the page in your bowser to see what it is actually outputting?

 

Edit: Actually it does not work as expected when there are more (earlier) values in the table. It returns the expected number of rows, just not the ones you expect.

[PFMaBiSmAd]

And here is why the existing code was not working. DATE values are strings and need to be enclosed in single-quotes so that they are not math expressions.

 

 

$query="SELECT * FROM blog WHERE blog_date < '$blogdate' LIMIT 2";

 

$query2="SELECT * FROM blog WHERE blog_date > '$blogdate' LIMIT $limit ";

[poleposters]

Thank you.

 

I made the changes. But its still not behaving correctly.

 

It seems that the less than operator retrieves records before the current blog entry, however starting at the beginning of the database.

 

ie. If the current date is 2009-07-24 it will return the first 2 records in the database which are dated 2009-07-18 and 2009-07-19. Instead I'd like to return 2009-07-22, 2009-07-23.

 

I attempted to address this by adding ORDER BY blog_date DESC to the query. This returns the correct records. But in the wrong order. ie 2009-07-23, 2009-07-22.

 

Any ideas?

 

 

[PFMaBiSmAd]

Yes, I see. After a bit of research and testing, the following does what you want -

 

$query = "SELECT * FROM (SELECT blog_title,blog_date FROM blog WHERE blog_date < '$blogdate' ORDER BY blog_date DESC LIMIT 2) AS n ORDER BY blog_date";

[poleposters]

Wonderful. It works great.

 

Thank you very much.