Table Generation based on Month

[barkley1979]

Hi
I have a table of the following data:
UID, URL, Logdate
1, www.foo.com, 2004-11-28
2, www.bar.com, 2004-11-27
3, www.foo.com, 2004-11-28
4, www.foo.com, 2004-10-08
etc

I have managed to scrape together the following code:

$sql = "SELECT URL, COUNT(*) FROM click WHERE log_date='2004-11-28' GROUP BY URL";

$result=mysql_query($sql);

echo "<TABLE BORDER='1'>";
while($thisrow=mysql_fetch_row($result))
{
$i=0;
while ($i < mysql_num_fields($result))
{
$field_name=mysql_fetch_field($result, $i);
$j = $i + 1;
echo "<TR><TD>" . $thisrow[$i] . "</TD><TD>" . $thisrow[$j] . "</TD></TR>";
$i = i + 2;
}
}

echo "</TABLE>";

I need to change the above, so that instead of a total count of URLs in the entire table is created, a table based on the month of the log date is created instead.
Something like:

URL October-04 November-04
www.foo.com 1 2
www.bar.com 0 1

I am very new to this and cannot understand how I change the code to pick up the month of the date - should it be done by GROUPING in the SQL?

Any help would be greatly appreciated.

[jonno946]

can you not just use your existing table and query it like this:

SELECT * FROM yourtable WHERE MONTH(date_field) = '11' && YEAR(date_field) = '2004'

that will select everything from this month for example 🙂

Jonno

[barkley1979]

That does produce what I need. Thanks
Although is there a way of including in the code a way so that it automagically produces the tables for me, in that I don't have to manually write 12 sql statements, one for each month of the year, and then specify each of these in the HTML table?
Is there an easy way to loop for each month in the table?
Thanks again

[rockerscc]

do something like this

$i=1;
while ($i < 13) {
SELECT * FROM yourtable WHERE MONTH(date_field) = '$i' && YEAR(date_field) = '2004'
//display the html, I see you know to do that.
$i++
}

[barkley1979]

Thanks!
With the help I am getting here I almost have it!
My one last problem before this thread is solved is the following:
My code now looks like this:

<?php

function test(){

echo "<TABLE><TR><TD>";
for ($j=1; $j<13; $j++) {
	$sql = "SELECT URL, COUNT(*) FROM click WHERE MONTH(log_date) = '$j' && YEAR(log_date) = '2004' GROUP BY URL";
	$result=mysql_query($sql);

	echo "<TABLE BORDER='1' width='400'><TR><TD width='280'><b>Website</b></TD><TD width='120'><b>" . $j . "/2004 Totals</b></TD></TR>";
	while($thisrow=mysql_fetch_row($result))
	{
		 $i=0;
 		 while ($i < mysql_num_fields($result))
		    {
  		    	$field_name=mysql_fetch_field($result, $i);
				$k = $i + 1;
  		    	echo "<TR><TD>" . $thisrow[$i] . "</TD><TD align='center'>" . $thisrow[$k] . "</TD></TR>";
  		  		$i = i + 2;
  			}	
	}
	echo "</TABLE><BR><BR>";
} 
echo "</TD></TR></TABLE>";
}

$context ["stats"] = test();


?>

Can anyone explain why if I assign function test to a variable, when I call to display the variable, it shows 1 and not the resulting HTML code?

For example, I have $context = test();

On another page I call $context, and this returns 1 instead of the full HTML code for all my tables.
Is there something obvious that I am overlooking?
Thanks

[jannoy]

your table must store in a variable like

function test(){
$mytable="<table><tr<td>hello </td></tr></table>";
return $mytable
}
$context ["stats"] = test();