HOw to search things by different months ?????

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
djdon11
Forum Commoner
Posts: 90
Joined: Wed Jun 20, 2007 5:03 pm

HOw to search things by different months ?????

Post by djdon11 »

Hi Friends


I want a solution for searching
actually i have a page which shows the comments post by my users . now i want to add a functionality to search the comments according to the months like January , February and so on till December .....
this all months are listed on the page and when user click over the January suppose than he get all the comments of January ....

now i want to do this with only one query and i don't want to execute different queries on different month select means if here is any mysql function which select the date according to month and year than please give me any snippet or query coding or any idea ..

please help me

Thanks ..
sunilbhatia79
Forum Newbie
Posts: 24
Joined: Sun Nov 11, 2007 9:37 pm
Location: Mumbai, India

Post by sunilbhatia79 »

Hi,

I am assuming that your field to store date of comments is a Date/Time field... if you use a query that will select records between a particular month range, it will involve in a full table scan... because you will be using a MySQL Date function to extract the month part... for this it will have to evaluate each record and compare it with the range...

I suggest that for this functionality... create another field (numeric) that will hold date like 200701 for Jan 2007 and 200705 for May 2007... This way if you want look for comments posted only in the month of jan 2007 then query it using value 200701 and for jan 2006, you can use the value 200601.

And don't forget to index that new field.... otherwise it will still do a full table scan... index it in the combination of other fields that you might use to search (multi-field indexing)

Let me know if you need more info.

Hope this helps.
djdon11
Forum Commoner
Posts: 90
Joined: Wed Jun 20, 2007 5:03 pm

Thanks for your post sir

Post by djdon11 »

Thanks sir for your reply ..

But i got the solution by another way ..

here is the code for retrieving data according to desired month ..

Code: Select all

$sql = "select * from project_mapping where month(pdate)='$month' and year(pdate)='$year'";
here $year and $month are the year & month correspondingly ....


Thanks
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

I'd populate custom attributes named "month" "year" and "day" in the coment div and use jQuery to hide / show them using an xpath selector.

Code: Select all

<div class="comment" year="2007" month="11" day="15">...</div>
It's perfectly legit, and keeps your page refresh / SQL queries down to one.

2 legit, 2 legit 2 quit. Hammertime.
sunilbhatia79
Forum Newbie
Posts: 24
Joined: Sun Nov 11, 2007 9:37 pm
Location: Mumbai, India

Re: Thanks for your post sir

Post by sunilbhatia79 »

djdon11 wrote:Thanks sir for your reply ..

But i got the solution by another way ..

here is the code for retrieving data according to desired month ..

Code: Select all

$sql = "select * from project_mapping where month(pdate)='$month' and year(pdate)='$year'";
here $year and $month are the year & month correspondingly ....


Thanks
Hi djdon,

Using this method will work.... but will do a FULL TABLE scan... this means that it will check all rows in your comments table to fetch the records.... on a small system its ok... but as your system increases in size.... your queries will slow down.... I would recommend using my earlier method... and indexing the new field... this will work for a long long time.

Hope this helps.
Post Reply