--- "Mahmoud M. Abdel-Fatah" <[EMAIL PROTECTED]> wrote:
> I'm making a simple inventory management system; I want the user to
> add date in data entry process, then he can make reports between
> specified 2 dates !!
>
> I have a column in MySQL DB called "timestamp" & i'm inserting the
> date into it in the format :
> strtotime("yyyy/mm/dd");
>
> in the report page , I make MySQL select statement :
> ================================
> SELECT * FROM history WHERE type LIKE '$type' AND category LIKE
> '$category' AND name LIKE '$name' AND toward LIKE '$freezer' AND
> ('$from_date_tt' <= timestamp <= '$to_date_tt') LIMIT $offset,
> $rowsPerPage ;
> ================================
> where :
> $from_date_tt=strtotime("yyyy/mm/dd");
> $to_date_tt=strtotime("yyyy/mm/dd");
> and ofcourse it's different times.
>
> and my problem that it show all entries !! even that it's not between
> these 2 dates !!
>
> is there a better way to use to store dates in MySQL and compare
> between them??
>
> Best Regards,
> Mahmoud M. Abdel-Fattah
I think your first problem is the data you are inserting into the table. In
MySQL 3 the timestamp used a format of "YYYYMMDDhhmmss" as an integer number.
In MySQL 4+ the format looks like the datetime field "YYYY-MM-DD hh:mm:ss".
The strtotime() function, on the other hand, returns a number of seconds since
an epoch date for the operating system (1 Jan 1970 for Unix/Linux).
If you want to use strtotime() then you should enclose it in a date() function
to provide a good format for MySQL:
$from_date = date("Y-m-d H:i:s", strtotime("YYYY/mm/dd"));
If you want to count only the date and not the time, you may want to use the
MySQL date function to evaluate only part of the value in the field.
You can use BETWEEN with the proper date format. Watch out to see which
endpoints are included to make sure your desired range is covered.
SELECT * FROM tablename WHERE event BETWEEN '$from_date' AND '$to_date';
If you want to look at only the dates you can use:
SELECT * FROM tablename WHERE DATE(event) BETWEEN '$from_date' AND '$to_date';
Work with MySQL on the command line or using phpMyAdmin to be sure that your
queries are working correctly. Look at the data in the table and make sure it
looks correct.
The timestamp datatype has a "feature" which causes the value to be updated to
the current server date-time whenever the record is updated or inserted. This
applies to the first timestamp defined in a table and not the others. This can
be very surprising and confusing if you don't expect it. It is usually better
to use datetime since these values only change when you specifically manipulate
them.
James Keeline
