search by month and year

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
qumar
Forum Commoner
Posts: 29
Joined: Wed Nov 01, 2006 8:20 am

search by month and year

Post by qumar »

hi friends,
i want search by month and year. how can i give, i am facing problem with this. please give some idea.
in database table, date foramte is like this 2007-12-06. i want search with 12-2007. i want display the records under each month. please help to me.
thanks,
qumar.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Choose the Right Board

Post by s.dot »

I would advise you to alter your database table, adding fields month, day, and year. This way you can search for what you want with a simple select query.

The way it is now, you'd have to select all dates, loop through the result set, explode() on the '-' character, and then look for the month you want.

Moved to databases:
[url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url] Section 1.1 wrote:1. Select the correct board for your query. Take some time to read the guidelines in the sticky topic.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Re: Choose the Right Board

Post by Zoxive »

scottayy wrote:I would advise you to alter your database table, adding fields month, day, and year. This way you can search for what you want with a simple select query.

The way it is now, you'd have to select all dates, loop through the result set, explode() on the '-' character, and then look for the month you want.
Not Necessary. Why not Just..

Code: Select all

Select * from `dates` where `date` >= '2007-12-01' AND `date` <= '2007-12-31'
Even yet..

Code: Select all

Select * from `dates` where MONTH(date) = 12 AND YEAR(date) = 2007
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Seriously, use the date functions in the database. They are a plenty and they are really useful. Even without changing your structure.

FYI, My date time formats are always 0000-00-00 00:00:00. Then I do the date math/manipulation on the DB server as needed.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

I was thinking along those lines.. almost posted something about doing date conversion/comparing in the query, but I couldn't say it with enough confidence.. so I didn't say it. :(
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

As an added bonus, any operation that you can push onto the database will speed up your app :-)
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

+1 K-Hug. I should have mentioned that as well.
Post Reply