[SOLVED] Selecting Within a Date Range

[Fearpig]

Hello again!

Can someone take a look at my code for me... or suggest a better way of doing things!

 

Basically I want to list all events that are active on a particular day. The events are saved in an SQL table with a start date and a duration. The following code appears to work but only returs one event when there should be 3!

 

<?php
$Year = '2007';
$Month = '4';
$Day = '10';
$Calendar_Date = date("d/m/Y",mktime(0,0,0,$Month,$Day,$Year));

echo "$Calendar_Date<br>";


//Get all of the events
$sql_Event_Details="SELECT * FROM tbl_Visit";
$Event_Details=odbc_exec($conn,$sql_Event_Details);
if (!$Event_Details)
{exit("Error in SQL");}
  

while (odbc_fetch_row($Event_Details)) 
{	


$Event_ID=odbc_result($Event_Details,"Event_ID");
$Duration=odbc_result($Event_Details,"Duration");
$Duration_Seconds=86400 * $Duration; 
             //86400 - seconds in a day
$Start_Date=odbc_result($Event_Details,"Event_Date");		
$Start_Date_Seconds= strtotime($Start_Date);
$End_Date_Seconds=$Start_Date_Seconds + $Duration_Seconds;
$End_Date=($Start_Date !='')?date("Y-m-d",$End_Date_Seconds):'';

$sql_Event_Details="SELECT * FROM tbl_Visit WHERE Event_Date > '$Start_Date' AND Event_Date < '$End_Date'";
$Event_Details=odbc_exec($conn,$sql_Event_Details);
if (!$Event_Details)
	{exit("Error in SQL");}

while (odbc_fetch_row($Event_Details)) 
		{
		$Event_Title=odbc_result($Event_Details,"Event_Title");
		$Format_Start_Date = ($Start_Date !='')?date("d/m/Y",strtotime($Start_Date)):'';
		$Format_End_Date = ($End_Date !='')?date("d/m/Y",strtotime($End_Date)):'';

		echo "<span class='BodyText1'><a href='Result_byEvent.php?id=$Event_ID'>$Event_Title</a> - $Format_Start_Date - $Format_End_Date</span><br>";
		}

}
?>

 

Any help would really be appreciated as I'm having real problems dates!!

[Fearpig]

Well took a deep breath, looked at it again and managed to sort it myself....

 

<?php
$Calendar_Date = date("d/m/Y",mktime(0,0,0,$Month,$Day,$Year));

$sql_Event_Details="SELECT * FROM tbl_Visit";
$Event_Details=odbc_exec($conn,$sql_Event_Details);
if (!$Event_Details)
{exit("Error in SQL");}
  

while (odbc_fetch_row($Event_Details)) 
{	

$Event_Title=odbc_result($Event_Details,"Event_Title");
$Duration=odbc_result($Event_Details,"Duration");
$Duration_Seconds=86400 * ($Duration - 1);
$Start_Date=odbc_result($Event_Details,"Event_Date");		
$Start_Date_Seconds= strtotime($Start_Date);
$End_Date_Seconds=$Start_Date_Seconds + $Duration_Seconds;
$End_Date=($Start_Date !='')?date("Y-m-d",$End_Date_Seconds):'';

$Format_Start_Date = ($Start_Date !='')?date("d/m/Y",strtotime($Start_Date)):'';
$Format_End_Date = ($End_Date !='')?date("d/m/Y",strtotime($End_Date)):'';

if (($Format_Start_Date <= $Calendar_Date) AND ($Format_End_Date >= $Calendar_Date)){

	echo "<span class='Body2'>$Event_Title</span><br>";

}else{
	echo "";
}	
?>