[mySQL] Query by date/month

[dekuro]

Hello everyone,

on my site there's a tip of the day for which I want to create an archive ordered by month. So the first page in the archive is displaying all the available months with their links to the tip's overview. So far, that's not the problem... here it comes:

Every single tip is stored in a mySQL table with its date. With PHP I want to query the DB for all tips of that selected month. However the date is in the format "yyyy-mm-dd" and I'm sure that mySQL (or PHP?) understands what there's written as its saved as a date cell.

Can you help?

[James Smith]

In your mySQL select statement, simply do

SELECT * from table ORDER BY whatever-your-column-with-the-date DESC

-j

[dekuro]

Yes, I knew that

Actually I meant displaying only one entire month on one page and for the next month one has to click for another page... If possible, I want pass the month's digit, so the script knows which month to display.

[freddydoesphp]

/*
Use this to generate your query and just pass the month as a digit ie January = 1; So pass 1 for January.
*/
//If month variable isn't set make it current month
if(!isset($month)) $month = date("m");
//Get the first and la;st days of the month
$firstday = date("Y")."-".$month."-1";
$lastday = date("Y")."-".$month."-".DATE("d", mktime(0,0,0,$month + 1, 0, date("Y")));
//Create query
$query = "select * from tablename WHERE datefield >= $firstday AND <= $lastday";

[dekuro]

Wow thanks!! It works perfectly... there was only one thing to change: in the query I had to put the $firstday and $lastday into ' '

thanks again!

[gokhana]

why dont you use mysql's MONTH function to solve this
SELECT whateveryouwanthere WHERE MONTH(datefield)=$month;
but be carefull new year is so close

[dekuro]

Hey, that's much easier! In this forum, I really can learn alot... much faster than reading a book

[freddydoesphp]

Wow, I love this stuff, I learn something new everyday. Thanks