[SOLVED] Query syntax

[ninedoors]

Is there a way that I can retrieve all the events from a the current month with an SQL query?  I have tried a couple query and it has returned nothing.  I didn't know if I could use the Mysql MONTH() function or something liek that.  Here is how I have tried to do it so far:

 

<?php
//todays month
$m = date('m');

$query = "SELECT id FROM events WHERE date LIKE '____/" . $m . "/__' ORDER BY date";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result))	
{
$id[] = $row['id'];
}
?>

 

I am retrieving the this for a calendar that I am linking the days to events.  If anyone has doen this or knows of a good tutorial that would be great.  I am using the calendar written by Kevin Devens here :http://keithdevens.com/software/php_calendar

 

Thanks

[MadTechie]

try this

$query = "SELECT id FROM events WHERE MONTH(date) = $m ORDER BY date";

[revraz]

Get the first day of the month and the last day of the month, then do a query for the values between those two dates.  That's how I do it at least.

[ninedoors]

Thanks guys.  MadT that worked!  For some reason I knew I could use the MONTH() function but didn't know the syntax.  Thanks again.

 

Nick

[Barand]

If there is more than a single year's data

<?php

//todays month
$m = date('n');
$y = date('Y');

$query = "SELECT id FROM events 
		WHERE YEAR(date) = $y
		AND MONTH(date) = $m
		ORDER BY date";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result))	
{
$id[] = $row['id'];
}
?>

[ninedoors]

Thanks Barand.  You are always thinking ahead.  I would have had to fix that next year when my site went all screwy.