i have a page that shows the user submitted car ads records from database. it works fine without any conditions applied.
when a user submit his car, it asks time period to display the ad and on the ad save page i calculate and set the expiry date of ad to store n database. suppose if user want to display his ad for 1 week and submitting his ad on 2010-02-10, i calculate the add on date with the display time period and store the expiry date with the addition of 7days in date like 2017-02-10.
everything works fine but now the problem is that on ad display page i want to show only those ads whose not expired.
those ads whose expiry date greater than current date.
Select records according to date
Moderator: General Moderators
Re: Select records according to date
You can have a code which happens each time the page is run that deletes all records you need to:
if you dont store the dates as a timestamp you can format the date and then compare it:
If you dont want to delete just use a select query instead
I used to use a code like this and this is untested but you get the idea
lewis
Code: Select all
$time=time()
mysql_query("delete from ads where expiry_date<$time")Code: Select all
$time=time();
$date=date("Y-m-d");
mysql_query("delete from ads where expiry_date<$date");I used to use a code like this and this is untested but you get the idea
lewis
Re: Select records according to date
thanks for reply
i have another field in the database table named "status". the default value of status is 1.
i don't want to delete the entries from my database so that's why i use status field.
i used your mentioned method to update record on every page load like that
i have 2 ads which expireon dates are less than 2010-02-10.
i stored dates in this format in my database 2010-02-09
the expireon dates of these ads are 2010-02-09 and 2010-02-05.
i used update query but it doesn't take any effect.
it's not updating the ad status value.
is there any missing or something wrong in the query
i have another field in the database table named "status". the default value of status is 1.
i don't want to delete the entries from my database so that's why i use status field.
i used your mentioned method to update record on every page load like that
Code: Select all
UPDATE usedcars SET status = 0 WHERE expireon < 2010-02-10i stored dates in this format in my database 2010-02-09
the expireon dates of these ads are 2010-02-09 and 2010-02-05.
i used update query but it doesn't take any effect.
it's not updating the ad status value.
is there any missing or something wrong in the query
Code: Select all
UPDATE usedcars SET status = 0 WHERE expireon < 2010-02-10Re: Select records according to date
sorry sorry sorry
it works fine.
thanks a lot.

it works fine.
thanks a lot.