Searching DateTime field

[IronCode]

I'm having a bit of trouble getting this to work. What i have is a DateTime field in a table and need to pull the records for only a particular day (based on a variable passed through). I looked on the MySQL website and found the date and time functions but it states the following,


DAYOFMONTH(date)
Returns the day of the month for date, in the range 1 to 31:
mysql> SELECT DAYOFMONTH('1998-02-03');
-> 3


Now how would i translate that into a workable PHP query? I tried the following with no luck,


$Query = mysql_query("SELECT *,date_format(date, '%M, %d, %Y ( %r )')as date
FROM $Table_diary WHERE DAYOFMONTH('date,$Day') && MONTH('date,$month')")
or die(mysql_error());


Any help would be great, thanks in advance guys.

[raf]

Heuh. There's no such thing as a PHP query.
You have PHP functions that can run a query like mysql_insert_id()but if you use mysql_query() --> then you just need to include the sql-command you want to be executed by MySQL. So you can't use PHP functions insude that sql-command. Only MySQL functions.

Also the * and then another variable from the same table on top of it ... might be to much ;) Only use * if you really need all variabels.

On the output, the returned recordset, you can then start using PHP functions. So i assume your query would be something like
$Query_=_mysql_query("SELECT_* FROM_$Table_diary_WHERE_DAYOFMONTH(datevar)=$day &&_MONTH(datevar)=$month")_

or_die(mysql_error());

(where datevar is the column-name of the datetimevariabel)
But if it is really a specified date (the above query will return all records for all years), you can use other datefunctions in the condition, like

$Query_=_mysql_query("SELECT_* FROM_$Table_diary_WHERE_EXTRACT(YEAR_MONTH_DAY FROM datevar)=$datevariabel")_

or_die(mysql_error());

(the $datevariabel should have this format yyyymmdd)
Which function you use, depends on the datvalue you have in your PHP script.

[IronCode]

Thanks for your reply raf. I did finally get it working last night before crashing for the evening. I did end up making the query as follows,



SELECT *,date_format(date, '%M, %d, %Y (%r)')as date FROM
$Table_diary WHERE DAYOFMONTH(date)=$Day && YEAR(date)=$year && MONTH(date)=$month ORDER BY date DESC


It seems to work well but that you for your reply though, it is greatly appreciated.