php mysql select month and year

[stonedeft]

Hello forums I need to select the month and year of a mysql database (datetime)

2010-05-06 15:33:45
2010-05-06 15:33:45
2010-05-06 15:33:45
2010-05-06 15:33:45

This one didn't work :

$m = 'May';
$y = '2010';

$sql = mysql_query("SELECT MONTH(s_in) AS month, YEAR(s_in) AS year FROM sd_record WHERE MONTH = '$m' AND YEAR = '$y'");
echo  mysql_num_rows($sql); 

[smftre]

How does it look in phpMyAdmin?

[stonedeft]

Nevermind I got it

http://dev.mysql.com/doc/refman/5.1/...functions.html

$sql = mysql_query("SELECT MONTHNAME(s_in) AS month,
YEAR(s_in) AS year
FROM sd_record
WHERE MONTHNAME(s_in) = '$m' AND
YEAR(s_in) = '$y' ");


QUESTION -> How do I select only the records for the current month and year? Is there a mysql function to return the current month and year? or I need php to get the current month and year for me before passing the query?

thnx

[r937]

it would probably be easier if you let php determine the starting and ending dates for the date range you want

your target code will look like this, to retrieve all rows for April 2010 --

WHERE s_in >= '2010-04-01'
  AND s_in  < '2010-05-01'

note that if you use MONTH, MONTHNAME, or YEAR in your WHERE clause, your query will be inefficient

only the range test as shown on the full date is efficient

[stonedeft]

it would probably be easier if you let php determine the starting and ending dates for the date range you want

your target code will look like this, to retrieve all rows for April 2010 --

WHERE s_in >= '2010-04-01'
  AND s_in  < '2010-05-01'

note that if you use MONTH, MONTHNAME, or YEAR in your WHERE clause, your query will be inefficient

only the range test as shown on the full date is efficient

You are definitely right and the query is much shorter Thanks I'll try that