mysql query with two date columns

[sw45acp]

I have a table that stores college assignments. In the table there are three columns: assignments, date, and due date, where the "date" is the date that it was assigned or handed out, and the "due date" is the date that the assignment is due. Both the "date" and "due date" columns are formatted with the mysql date format. What I would like to do is retrieve all assignments where today's date is between those two date columns.

 

This doesn't work:

$date = date("Y-m-d");
$q = mysql_query("SELECT * FROM `table_college` WHERE `date` >= '$date' AND `due_date` <= '$date'");

 

Any help would be appreciated, thank you.

[Jax2]

You need to use time() instead of date.

 

It will assign a time when you create the record, and then you can use time() again for the "due date"...

 

Make sense?

 

I.e., if you want it due 24 hours past when you assigned it, it would be

 

start time + 86000 (24 hours).

 

Then when you do a search, it's as simple as

 

Find all records where time() > start time and time() < end time ...

 

[sw45acp]

time() spits out something like "1269007275". Should I format the date columns to be "TIME" instead? When I insert something from time() it says data type fractured (in phpMyAdmin). So should I format the "date" and "due_date" columns to simply be an integer with 10 as a length?

[Jax2]

You can just leave the tables set up as varchar (255) ... that will work fine ... won't work with date at all.

 

[PFMaBiSmAd]

Good God. Don't use the time() function.

 

You already have the data in the optimum format for storing, comparing, and retrieving dates. Just fix the logic in the query -

 

$q = mysql_query("SELECT * FROM table_college WHERE '$date' BETWEEN date AND due_date");

 

[sw45acp]

Ok this appears to work great. Thank you both for your help.