Deleting using date

[TheViper]

I am building a classifieds page and want to use a radio button for people to select how many days their ad will be displayed. If their ad is to be displayed for 7 days I want it to be deleted after 7 days. How can this be done?

 

[derren]

Hi

Do you wish the add to be automatically deleted from your database or simply hidden when it has expired?

If you hide the add you can use a simple query on your data to show just the records which are "live". This is highly effective, and if the user wishes to place the add again they can login and view all their old adds and "renew" them without having to re enter their details (which is nice)

For deleting I suggest two possible methods

1) create an admin page for you so that you can choose to delete all expired orders from the press of a button

2) create an application variable and fill it with today's date. At the start of some page(s) check that the value of the application variable is the same as today's date. If it isn't, delete all expired records and set the variable to today's date. This will "clean" your database automatically

Good luck

Derren [The only person in the world to like Word]

 

[mlawson]

Hi Guys,

Tom Hughes has a similar thread a couple of weeks back. If you want to create an expirable (or is that to do with breathing..?) field (ie a record within a database) then create the query to be used WITHIN Access and you can then use UD to lift it into the recordset you create. This has a number of benefits, you don't need to create any SQL or filters within the UD window and any queries you set up in Access will be lifted with the recordset as it's executed.

If your page has a radio button set for expiring date after 7, 14 or 21 days for example, use this numerical values in your Access field (ie When you create a new record when the 7 day radio button is clicked, the value of "7" is dumped in a field called "AD_Ex_Date". In Access create a query and drag all your ad table fields across. (You may want to open your Access help file here!) Now you'll have to create a query using the query builder and effectively add the code;

>=Date()AND<=(Date()+[ab_table_name]![AD_Ex_date])

This should pull the value for any advert within the database and display it between the date today and x days hence (where x is 7,14 or 21).

Sorry to go on, but if you spend a little time in Access playing with queries you'll find that they are much more powerful than trying to handcode SQL correctly.

Any probs, call my wife!

Have fun

M

 

[TheViper]

I would like to hide the add. That sounds like a better idea.

 

[derren]

All of the examples are based around the same principle - that the end browser only gets to see the adds which are active. The access solution is a great one because you have a much better query builder with it, whereas Dreamweaver can be very counter-intuitive, especially if you are a database programmer used to writing your own SQL. I would rate both at the same level of difficulty or ease - dependant upon how you look at it!

Try this as an extra idea for the dates:

When the person placing the add decides on the timescale and submits their add, put today's date in a startdate field and populate an enddate field with

now()+ (add the value chosen by the user for the number of days)

Then the filtering on the database is more simple:

WHERE now()>startdate AND now()<enddate

I must stress that this is just another way of doing the same thing which may seem a little bit more straight forward.

Derren [The only person in the world to like Word]