[RESOLVED] Date range best option

rulian

Hello class

I'm trying to wrap my head around a good, or better way of acheiving what I need to do.

Basically I'm making a serve up coupon book
The client will go in, specify what ever offer they are doing and select a "date range" of lets say 2 weeks, 3 months so fourth.

The idea is for when a user searches for a date, based on the date and the current date it will serve up a specific coupon.

For example, if today is March 25th, and you pick May 25th, you qualify for the 3 month interval coupon bu tnot the 2 week one.

So I need a way to store and compare times in mysql in such a way that I can pull qualifying coupons based on the current and user inputed time.

My idea of far is to store a int in the timeframe specified in seconds , 2 weeks = 60 60 24 * 14 = 1209600
And subtract curdate - user date and compare the results to the stored field in the database.

So I ask if anyone has a better idea of how to work this in mysql, i am still building and can pretty much change any structure if need be.

NogDog

I'm not sure I understand the exact scenario, but it doesn't seem like you should need to count seconds, just days. Amongst the various MySQL date functions such as DATEDIFF(), DATE_ADD(), DATE_SUB(), and CURDATE(), it would seem to me you could just do "date arithmetic" within the query to get your desired results, using DATE type columns for any dates and INT columns for any durations in days.

rulian

Days would do just fine, i am simply not sure of how to store and compare that information.

Basically, the client will create different coupons and assing them different days, weeks or month intervals.

So the client says "give this coupon to everyone who's chosen a date that is within 3 months from today"

So the database must compare today against a table which contains various intervals.

Since those intervals are always based on a time frame based on the current day, i need to apply those intervals dynamically.

I though about using add_date and interval but I need to concat the information on the database which would be stored as "14 DAY" , or "3 MONTH" so that I could combine it with a INTERVAL call in the database. but I cannot combine it wihtout some for of eval() function

NogDog

So, assume there is a coupon_start (DATE) and coupon_days (INT) column. The user specifies a date which you have as a yyyy-mm-dd string in variabel $date:

$sql = "SELECT * FROM `tablename` WHERE '$date' BETWEEN `coupon_start` "
     . "AND DATE_ADD(`coupon_start`, INTERVAL `coupon_days` DAYS)";

Alternatively, you could do the math on the PHP side (using [man]strtotime/man, e.g.) when storing the coupon data, and instead have a coupon_start (DATE) field and a coupon_end (DATE) field. Then you would just check for the user's requested date being "BETWEEN coupon_start AND coupon_end".

rulian

Thanks NogDog, your suggestion worked perfectly, I store the days in a int format,and run the following:

SELECT * FROM `table` WHERE '$date (yyyy-mm-dd)' BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL `range` DAY)