Month() + mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bob_the _builder
Forum Contributor
Posts: 131
Joined: Sat Aug 28, 2004 12:25 am

Month() + mysql

Post by bob_the _builder »

Hi,

Im trying to grab all records that match the month from:

$eventid = '2007-05-11';

using query:

Code: Select all

$sql = mysql_query("SELECT id, firstname, lastname, phone, cellphone, email, starttime, finishtime, event FROM calendar WHERE MONTH(starttime) LIKE '".$eventid."%' ORDER BY starttime ASC");

Seems to bring back zero rows when there are 2 rows iin he database matching the 5th month.

Have I got it all wrong?

Thanks
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

MONTH() returns a number between 1 and 12. LIKE is a string comparison function.
Therefore MONTH(starttime) LIKE '".$eventid."%' is like e.g '12' LIKE '2007-05-11%' and that will never match.

try

Code: Select all

MONTH(starttime) = MONTH('$eventid')
instead
bob_the _builder
Forum Contributor
Posts: 131
Joined: Sat Aug 28, 2004 12:25 am

Post by bob_the _builder »

Hi,

Code: Select all

WHERE MONTH(starttime) = '$monthnum' && YEAR(starttime) = '$year'
Works


Thanks
Post Reply