SitePoint Sponsor |
|
User Tag List
Results 1 to 8 of 8
Thread: [mySQL] Query by date/month
-
Nov 5, 2000, 14:12 #1
- Join Date
- Jul 2000
- Location
- Nowhere
- Posts
- 278
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
Nov 5, 2000, 14:46 #2
- Join Date
- May 2000
- Location
- London
- Posts
- 283
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
In your mySQL select statement, simply do
SELECT * from table ORDER BY whatever-your-column-with-the-date DESC
-j
-
Nov 5, 2000, 14:56 #3
- Join Date
- Jul 2000
- Location
- Nowhere
- Posts
- 278
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Nov 5, 2000, 15:39 #4
- Join Date
- Aug 2000
- Location
- San Diego, CA
- Posts
- 5,460
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
/*
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";
Please don't PM me with questions.
Use the forums, that is what they are here for.
-
Nov 5, 2000, 17:53 #5
- Join Date
- Jul 2000
- Location
- Nowhere
- Posts
- 278
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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!
-
Nov 6, 2000, 09:36 #6
- Join Date
- Oct 2000
- Posts
- 89
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
why dont you use mysql's MONTH function to solve this
SELECT whateveryouwanthere WHERE MONTH(datefield)=$month;
but be carefull new year is so closeGreat Dane
Gokhan ARLI
-
Nov 6, 2000, 11:11 #7
- Join Date
- Jul 2000
- Location
- Nowhere
- Posts
- 278
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Hey, that's much easier! In this forum, I really can learn alot... much faster than reading a book
-
Nov 6, 2000, 11:36 #8
- Join Date
- Aug 2000
- Location
- San Diego, CA
- Posts
- 5,460
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Wow, I love this stuff, I learn something new everyday. Thanks
Please don't PM me with questions.
Use the forums, that is what they are here for.
Bookmarks