Showing records which are within a set Time Period

[ebanbury]

Hi There.

I have a property website, which already has some extensive search criteria for users to filter which properties they would like to see.

I need to add to the search queries that fact that only properties which are within 21 days from the date they were submitted should show up!

I have the following column on my mysql db: add_date which is datetime type.

I think I need to write something like:

echo date("d/m/y", strtotime(0,0,0,date('m'), date('d')+21, date('y'));

However it is not really working. And I'm not sure where to put it in the sql query?

Any help would be great! :-)
Thanks

[urtrivedi]

I think you do not need php , you can simply do it in mysql query itself

SELECT * from table_name 
where date_add(submit_date ,interval 21 day ) <= current_date

[ebanbury]

Hi Many thanks for getting back to me. I'll test it right away :-)
How does submit_date know that it is the date in the date_add column? Is submit_date an inbuilt function?

Thanks

[urtrivedi]

Submit date is name for your date column which you want to check for 21 days.
(you must change it ). Here function is date_add and not submit_date.

[ebanbury]

Haha yes of course. I was getting confused as my actual column is add_date :-)

[ebanbury]

Hi Well the code is working but not bringing back the expected results. It brought back 2 properties which have a submitted date of: 2011-05-09 00:00:00 and 2011-05-14 21:45:16 respectively. These should not be showing as they are over 21 days from those dates.

However I have the following records which should be showing up:

2011-05-20 00:00:00
2011-05-20 14:04:22
2011-05-29 14:44:14
2011-06-01 15:39:18
2011-06-01 15:45:06
2011-06-01 15:47:08
2011-06-02 11:21:05
2011-06-02 12:03:48

I think I have the code wrong way round right? It should be something like !date_add(); ?

Thanks again for the help

[urtrivedi]

submit your query, and mysql table script with data

[ebanbury]

Hi here is the query:

$query_rs_agentlists = "SELECT property_details.ID, property_details.prop_id, property_details.prop_type, property_details.prop_price, user_registration.ID, user_registration.reg_tel, user_registration.reg_lname,  user_registration.reg_agent, user_registration.reg_email, property_details.add_road, property_details.add_house, property_details.add_area, property_details.screenpath, property_details.image_main, user_registration.reg_companyname, property_details.prop_cat, user_registration.reg_license, property_details.prop_sold, property_details.add_date FROM property_details, user_registration WHERE property_details.ID = user_registration.ID  AND user_registration.reg_agent = 'Yes' AND user_registration.ID = '$_POST[ID]' AND property_details.prop_sold = 'Active' AND date_add(add_date ,interval 21 day ) <= current_date ORDER BY property_details.prop_cat, property_details.prop_price ASC";

Here is the mysql table:

Showing rows 0 - 10 (11 total, Query took 0.0003 sec)
SELECT ID, prop_id, add_date
FROM`property_details` 
WHERE ID ="1"
AND prop_sold ='Active'
LIMIT 0 , 30

Not sure what else to show you?

Many thanks for the help though... it is much appreciated.

[ebanbury]

The results I get back from the mysql query are the list of dates I sent earlier. 11 records in total, but only 2 showed up when executing the full sql query at the top.

[ebanbury]

Hi

I think in this query (with the date_add at the bottom)

SELECT property_details.ID, property_details.prop_id, property_details.prop_type, property_details.prop_price, user_registration.ID, user_registration.reg_tel, user_registration.reg_lname, user_registration.reg_agent, user_registration.reg_email, property_details.add_road, property_details.add_house, property_details.add_area, property_details.screenpath, property_details.image_main, user_registration.reg_companyname, property_details.prop_cat, user_registration.reg_license, property_details.prop_sold, property_details.add_date
FROM property_details, user_registration
WHERE property_details.ID = user_registration.ID
AND user_registration.reg_agent ='Yes'
AND user_registration.ID ="1"
AND property_details.prop_sold ='Active'
AND DATE_ADD(add_date, INTERVAL 21 DAY) <=current_date

What needs to happen is for the query to bring back any results in which the time elapsed is NOT within 21 days from the add_date.
Currently it now only brings back the records which are within or upto 21 days from add_date.

What I'm trying to do on the website is for records which are older than 21 days from the date they were submitted, to be knocked off any results returned by the search query.

Is there any way to negate the

AND DATE_ADD(add_date, INTERVAL 21 DAY) <=current_date

Thank You so much :-)

Liz

[urtrivedi]

and add_date < DATE_ADD(current_date, INTERVAL -21 DAY)

[ebanbury]

yep tried that. It is still bringing back records with dates like:
2011-05-07 00:00:00 - i.e. older than 21 days.

strange...it looks like it should work? Is it possibly because I have datetime on the table structure?

[urtrivedi]

can u post mysql table script with data

[ebanbury]

Hi I'm not quite sure how to put the data into this message? Could you let me know how best to do this?

Table script:

SELECT property_details.ID, property_details.prop_id, property_details.prop_type, property_details.prop_price, user_registration.ID, user_registration.reg_tel, user_registration.reg_lname, user_registration.reg_agent, user_registration.reg_email, property_details.add_road, property_details.add_house, property_details.add_area, property_details.screenpath, property_details.image_main, user_registration.reg_companyname, property_details.prop_cat, user_registration.reg_license, property_details.prop_sold, property_details.add_date
FROM property_details, user_registration
WHERE property_details.ID = user_registration.ID
AND user_registration.reg_agent ='Yes'
AND user_registration.ID ="1"
AND property_details.prop_sold ='Active'
AND DATE_ADD( add_date,INTERVAL-21
DAY)<= current_date
LIMIT 0 , 30

Data/Results of above:

ID prop_id prop_type prop_price ID reg_tel reg_lname reg_agent reg_email add_road add_house add_area screenpath image_main reg_companyname prop_cat reg_license prop_sold add_date
1 4 Hudc Apartments 595000 00000000000000000001 81185703 Banbury Yes [e-mail] Marine Parade Mandarin Gardens Siglap images/propertyimages/ fronthouse.jpg kaiproductionservices Sell EJB1971 Active 2011-05-07 00:00:00
1 32 Private Apartments 234 00000000000000000001 81185703 Banbury Yes [e-mail] sdf 23 images/propertyimages/ kaiproductionservices Sell EJB1971 Active 2011-05-14 21:45:16
1 36 0 00000000000000000001 81185703 Banbury Yes [e-mail] adfa kaiproductionservices EJB1971 Active 2011-05-20 00:00:00
1 39 Private Apartments 340000 00000000000000000001 81185703 Banbury Yes [e-mail] Siglap Road Mandarin Gardens Siglap images/propertyimages/ kaiproductionservices Rent EJB1971 Active 2011-05-20 14:04:22
1 47 Landed Property 2212 00000000000000000001 81185703 Banbury Yes [e-mail] ads asd images/propertyimages/ kitchen1.jpg kaiproductionservices Sell EJB1971 Active 2011-05-29 14:44:14
1 49 Private Apartments 888888 00000000000000000001 81185703 Banbury Yes [e-mail] siglap mandarin gardens siglap images/propertyimages/ fronthouse.jpg kaiproductionservices Rent EJB1971 Active 2011-06-01 15:39:18
1 50 Private Apartments 999999 00000000000000000001 81185703 Banbury Yes [e-mail] siglap mandarin gardens siglap images/propertyimages/ fronthouse.jpg kaiproductionservices Rent EJB1971 Active 2011-06-01 15:45:06
1 51 Private Apartments 999999 00000000000000000001 81185703 Banbury Yes [e-mail] siglap mandarin gardens siglap images/propertyimages/ fronthouse.jpg kaiproductionservices Rent EJB1971 Active 2011-06-01 15:47:08
1 56 Please Select 500999 00000000000000000001 81185703 Banbury Yes [e-mail] siglap road chevalier gardens Ma On Shan images/propertyimages/ fronthouse.jpg kaiproductionservices Sell EJB1971 Active 2011-06-02 11:21:05
1 63 Please Select 2500000 00000000000000000001 81185703 Banbury Yes [e-mail] dfafdaf adfaf images/propertyimages/ fronthouse.jpg kaiproductionservices Sell EJB1971 Active 2011-06-02 12:03:48
1 71 Please Select 45255252 00000000000000000001 81185703 Banbury Yes [e-mail] dfdf trt images/propertyimages/ kaiproductionservices Sell EJB1971 Active 2011-06-07 11:21:44

[urtrivedi]

if you have php myadmin, select your table, click export ->go, it will give you table mysql script, paste it here

I think you have not seen my post properly, its no only -21, I have interchanged the columns
see carefully

and add_date < DATE_ADD(current_date, INTERVAL -21 DAY)

[ebanbury]

Hi...Thank you so much for continuing to help me.
This is just pulling back the same 1st three records though. i.e. the records which are older than 21 days from add_date...

However I think this works.. just change the comparison :

and add_date > DATE_ADD(current_date, INTERVAL -21 DAY)

?? :-)