date comparing searchs

sweetstuff2003

Hello,
I have a report system that the worker enters a new task sheet number which is unique (they enter manually). They tick the following check boxes depending on their work status... received, entered,filed (date stamped in the database and also php page, NULL value if its not been selected)
and then a timestamp will appear when they update the page.
When they click a button called "show reports" a window appears which request manual entry of dates to search FROM and date to search TO
this report will output to the worker the missing task sheet number according to the NULL value on the date status's. (received, entered,filed) fields if they had not ticked off the check boxes saying its been received, entered or filed.

Now I would like to create some search system on dates. when they type in the TO box: 1st Aug 2003 and then type in the FROM box: 01 Sep 2003 for instance then all the data since 1st Aug 2003 that has no timestamp will output to a report.php as MISSING.

ANy ideas please on how to do the codes to compare dates? it seems very confusing....

Pig

It is very complicated, unless you use unix timestamps (which make everything very easy).

Store the date in your database as a unix timestamp (varchar col type). Then you can just use strtotime to get the upper and lower limits.

For instance, if you wanted to find everything that is 1 month old, you could do:

$result = mysql_query("SELECT * FROM table WHERE table_date < " . strtotime("now -1 month") );

or if you wanted everything between the first of the year and the middle of the year:
$result = mysql_query("SELECT * FROM table WHERE table_date < " . strtotime("2003/6/1") . " AND table_date >= " . strtotime("2003/1/1") );

Then when you want to output a date on a report, you just use date().

Bijan

It shouldn't be that hard. You put null for records that do not have a selected checkbox, right? So, all you have to do is to get the dates greater than FROM and less than TO whose value are null, something like:

$query = "SELECT * FROM myTable WHERE myDate > $from AND myDate < $to AND isNull(work_status)";

# work_status is the field that holds your values (received, entered,filed)

Pig

that would depend entirely on the format of your date.

sweetstuff2003

hiya,
discovered the DateAdd function ... its brill! does exactly what I need it to do compare with current date and subtract the right amount of days, hours etc... thanks for advice guys.