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 ..
HOw to search things by different months ?????
Moderator: General Moderators
-
sunilbhatia79
- Forum Newbie
- Posts: 24
- Joined: Sun Nov 11, 2007 9:37 pm
- Location: Mumbai, India
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.
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.
Thanks for your post sir
Thanks sir for your reply ..
But i got the solution by another way ..
here is the code for retrieving data according to desired month ..
here $year and $month are the year & month correspondingly ....
Thanks
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'";Thanks
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
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.
It's perfectly legit, and keeps your page refresh / SQL queries down to one.
2 legit, 2 legit 2 quit. Hammertime.
Code: Select all
<div class="comment" year="2007" month="11" day="15">...</div>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
Hi djdon,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 ..
here $year and $month are the year & month correspondingly ....Code: Select all
$sql = "select * from project_mapping where month(pdate)='$month' and year(pdate)='$year'";
Thanks
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.