Making Search on DATE in MySQL with PHP

lordstjohn

Hi,

I've stored some date (YYYY-MM-DD) type DATE in a MySQL table.

I have the following dates in the MySQL Table :

2000-12-02
2000-12-03
2000-12-04
2000-12-05

The following code is used to select the date :

$vardate = "2000-12-02"

$query = "select * from table where stored_date like \"$vardate\""

$mysqlresult = mysql_query($query, $mysqllink);
$nbrarg = mysql_affected_rows($mysqllink);

My problem : The $nbrarg value = 0.

Is my Query OK, or what i'm doing wrong...

Thank's for your help.

Anon

Do not use mysql_affected_rows on SELECT statments. That is for Updates, Deletes etc. You want mysql_num_rows() when using select.

Also, the argument in your example would be $mysqlresult, not $mysqllink.

toma42

Using unix_timestamp will make working with dates a lot easier...I strongly suggest you modify your code to use that function.

Anon

Poppycock!

When using dates and times in a database, it is significantly easier to use the built in formats and functions of the db. Usually you need to do one of two things. First, format the date to your liking. You might consider it a wash between using the date_format() function of mysql and the various unix timestamp functions of PHP and I'd differ but I'll give it to you.

The second, and most difficult, is to select based on given date criteria. Let's say you want to select all records that have the date field 'expire' during a given month. Using PHP and unix timestamps you'd have to figure out the timestamp for the first day of the month at midnight, then calculate the last day of the month and figure out the timestamp for 23:59:59 on that day.

Using a mysql formatted time all you have to do is:

$query="select * from table where expire like '".$year."-".$month."%'";

Unix timestamps have their place. They're great for flat files and when using the file system, but when working with a DB you are better off using the DBs built in functions and datatypes.

toma42

Poppycock touche! 🙂

The reason I prefer unix timestamps is the date() function in php takes one as a parameter and you can easily switch back ends if you use them.

e.g. to switch from mysql to postgresql, use this pgsql function

CREATE FUNCTION unix_timestamp (timestamp) RETURNS float8 AS 'SELECT date_part(\'epoch\', $1) AS RESULT;' LANGUAGE 'sql';

I don't know of a back end that doesn't support unix timestamps and working with seconds only makes date manipulation and comparisons very easy.

Tom