Getting data that was entered in the previous week

I am building an e-commerce site for a client and require a page that will show products that were added to the database in the last week/ two weeks etc.

When a product is added to the database it has three seperate fields storing the date added in(01 - 31 format) month (January-December) and year( xxxx).

Is there anyway that I can run a mysql/php query that will just retrieve those products entered within the last xx weeks?

All help greatefully appreciated.

Kyle :thumbsup:

This is probably more an SQL question than a PHP question. It really would just require writing and SQL statement that takes todays date and then subtracts a week from it and pulling everything based off of that. I'll move this over to the SQL forum...

HI, is it too late to change the database format? reason I ask is its really much better for several reasons to store any date information as a MySQL or UNIX TIMESTAMP

why?
well MySQL can perform data calculations on all valid TIMESTAMPS i.e.

"SELECT * FROM $table WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30"

which would give you the records from the last 30 days etc.


As you are at the moment it is going to be awkward and innefficient to call stuff, this query will be slow unless all 3 fields are indexed (on a medium/large dataset)

$day=15;
$month=6;
$year=2002;

"SELECT * FROM $table WHERE month>='$month' && year>='$year && $day>='$day'"

though it will work, but is there any reason you want 3 fields for the date? & can you change that now ?

I have a full set of fucntions that I use for manipulating form data into MYSQL timestamps for data insertion if you need them.