Delete DB entry after a given date (php/mysql) help

danny_brazil · #1 (**permalink**) 08-18-09, 10:09

Hello
Im looking for help with this chalenge:
I have a database with "future events" in it.

each even is given a DATE(example: Aug' 25 - film festival)

and a user will see all of the future event ONLY

WHAT i need here id a code the i can run every day (i can run it no problems) and it will DELETE old entries from the DB.

lets say today is Aug' 12 so i want to delete ALL entries with a date OF Aug 11 and back

Any help ?

Danny

gvee · #2 (**permalink**) 08-18-09, 10:24

Are you certain you want to delete?

why not only show future dated entries by limiting your SELECT statement with an appropriate WHERE clause:

Code:

SELECT list
     , of
     , fields
FROM   events
WHERE  event_date > current_timestamp

danny_brazil · #3 (**permalink**) 08-18-09, 10:26

Man , thanks a lot.

One question though , what would be the best "timestamp" to use here ?

can you give me an example ?

thanks a lot !

gvee · #4 (**permalink**) 08-18-09, 10:44

field should be datetime datatype and then you can use the current_timestamp() function to return the datetime of now

illustration:

Code:

SELECT current_timestamp()

danny_brazil · #5 (**permalink**) 08-18-09, 15:37

Another question

lets say i have 20 entries from today on

i want to present JUST the 4 that are the closest to today ?

today is 18/08/2009

i have entries from sep 1 on...( 1..2..3..4..6..8..15...23....29)

but there ARE not order as i wrote here they are mixed

is there any way to SQL by distance from current date ?(lets say 1 week in advanced)

thanks

gvee · #6 (**permalink**) 08-18-09, 17:08

Code:

SELECT TOP 4
       list
     , of
     , fields
FROM   events
WHERE  event_date > current_timestamp
ORDER
    BY event_date ASC

Internet Services

Web Development

Digital Marketing

Consumer Tech