Help with sql statement

[Hatch]

I'm trying to figure out an sql statement that will select all the dates in my calendar table where the month = whatver.

I'm trying to select all the events from my calendar and displsy them like this but month by month

January
event
event
event

February
event

March
event
etc... for the next 12 months from the current month

this is what I have so far...

	for ($i = 0; $i < 12; $i++)
	{
		echo "<b>"date("F", mktime(0,0,0,date("m")+$i,date("d"),date("Y")))."</b><br>";
		$query = "SELECT *,DATE_FORMAT(dateEntry, '%a, %l%p') as FormatedDate FROM calendar WHERE dateEntry"
	}

[nnichols]

This will certainly work though there may be a more efficient way of doing it -

$query = "SELECT *,DATE_FORMAT(dateEntry, '%a, %l%p') as FormatedDate
FROM calendar 
WHERE dateEntry >= CONCAT(YEAR(NOW()),'-',MONTH(NOW()),'-01')
AND dateEntry <= DATE_SUB( DATE_ADD( CONCAT(YEAR(NOW()),'-',MONTH(NOW()),'-01' ),INTERVAL 12 MONTH),INTERVAL 1 DAY )"

This will select the data for the current month and the following 11 months.

Hope this helps 😉

[Hatch]

If I just wanted to select everything for that month how would I do it? I guess I could use the same thing but change "interval 12 month" to "interval 30 day" ??? what if the month had 31 days though ? :-\

[nnichols]

change 12 MONTH to 1 MONTH

it will add 1 month and then subtract 1 DAY giving the last day of the current month

[Hatch]

Here's what I got so far for my function but it's giving me this error...
"Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource "

on this line

$num_results = mysql_num_rows($result);

<?view_admin_Cal();

function view_admin_Cal()
{
	for ($i = 0; $i < 12; $i++)
	{
		$month_txt = date("F", mktime(0,0,0,date("m")+$i,date("d"),date("Y")));
		$sql_date = date("Y-m-01", mktime(0,0,0,date("m")+$i,01,date("Y")));
		echo "<b>".$month_txt."</b><br>";
		$query = "SELECT *,DATE_FORMAT(dateEntry, '%a, %l%p') as FormatedDate FROM calendar WHERE dateEntry >= ".$sql_date." AND dateEntry <= DATE_SUB( DATE_ADD( '".$sql_date."',INTERVAL 1 MONTH),INTERVAL 1 DAY )";
		$result = mysql_query($result);
		$num_results = mysql_num_rows($result);

	echo "<table width='350' border='0' cellspacing='0' cellpadding='4'>";

	for ($j = 0; $j < $num_results; $j++)
	{
	?>
		  <tr>
    		    <td width="50%"><font size="2">
    	<? 
		$row = mysql_fetch_array($result);
		echo $row['FormatedDate']; 
	?> PST</font></td>
    		    <td><font size="2"><a href="http://www.domain.com/calendar/calendar_fnc.php?action=showEvent&id=<? echo $row['id']; ?>"><? echo $row['title']; ?></a></font></td>
 		  </tr>
	<?
		echo "</table>";
	}
}
}?>

[nnichols]

Change

$result = mysql_query($result);

to

$result = mysql_query($query);

[Hatch]

Here is what I've got now. I thought it would be easier to do all the date calculations in PHP and then just put them into the query statement but its not working and this is the error I get

"Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource "

From this code

function view_admin_Cal()
{
	for ($i = 0; $i < 12; $i++)
	{
		$month_txt = date("F", mktime(0,0,0,date("m")+$i,date("d"),date("Y")));
		$from_date = date("Y-m-d H:i:s", mktime(0,0,0,date("m")+$i,01,date("Y")));
		echo "From ".$from_date;
		$to_date = date("Y-m-d H:i:s", mktime(23,59,59,date("m")+$i+1,00,date("Y")));
		echo " To ".$to_date."<br>";
		echo "<b>".$month_txt."</b><br>";
		$query = "SELECT *,DATE_FORMAT(dateEntry, '%a, %l%p') as FormatedDate FROM calendar WHERE dateEntry >= ".$from_date." AND dateEntry <= ".$to_date;
		$result = mysql_query($query);
		$num_results = mysql_num_rows($result);

	echo "<table width='350' border='0' cellspacing='0' cellpadding='4'>";

[Hatch]

Just forgot to add quotes around the dates :-\