Click Logging Count script

[jonathen]

Hi,

Please help!! I have had a discussion about this problem before, and although one chap was very helpful, I still cannot get the script to do what I want.

I have a script that reports all clicks of url's on my site to a db, which I wrote so that i could see how many clicks each link got. Now I need to draw monthly reports from this and can't figure out the code I need to display it the way I want.

My columns are: date, url, clicks

I only have about 25 urls, and what I want to do is display a monthly report of how many times each url was hit (without listing the urls over and over again, of course)

So I figured I would have to: SELECT DISTINCT 'url' WHERE 'date' = "2003-02-%"

...so that I get a list of each url once for Feb 2002. But the problem I have is how to select the clicks, calculate them all together for each url, and then display a table like so:

month: Feb 2002
url: www.mydomain.com
hits: 17

(which would loop for all relevant domains)

The helpful guy suggested something like the code below, but this was coded for pgsql, and even though I tried to convert it to mysql syntax (which it is below), I could not make it work. Could anyone help me convert this, or find a solution to my problem?

<? 
mysql_connect('localhost','dbuser','dbpass');
@mysql_select_db(clicksdb) or die( "Unable to select database");
$str = "SELECT DISTINCT url FROM clicks" 
$qid = mysql_exec($con, $str); 
for($i = 0; $i < mysql_numrows($qid); $i++) { 
$fetch = mysql_fetch_object($qid, $i); 
$str2 = "SELECT count(*) FROM clicks WHERE url = '$fetch->url' AND date = '2003-02-%'"; 
$qid2 = mysql_exec($con, $str); 
$get = mysql_fetch_object($qid2,0); 
$arry['$fetch->url'] = $get->count; 
} 
?> 

The error I get is:

Parse error: parse error on line 5

Thanks in advance,

Jonathen

[sarah]

I think mysql_numrows should be mysql_num_rows...not sure if there are other issues.

[jonathen]

No, it's mysql_numrows (well at least that is what I have always used, and it works in my other scripts)

The parse error occurs on line 5 ($qid = mysql_exec($con, $str); )

Any ideas? Anyone? Please!

Thanks!

[jonathen]

OK, as I couldn't get anywhere with this, I spoke to someone else, who advised me to use some code along the lines of what I have below - after fiddling with it, I am a little closer, but still cannot get this to work! This is the code:

<?
mysql_connect('localhost','blah','blah');
@mysql_select_db(dbclicks) or die( "Unable to select database");
$month=2;  // or $month=date("n");
$year=2003; // or $year=date("Y");
$SQLquery="SELECT SUM(clicks) as how_many,url FROM clicks WHERE YEAR(date)='$ye$

MONTH(date)='$month' GROUP BY url ORDER BY how_many DESC";
$result=mysql_query($SQLquery);
print "month: $month $year";
while($row=mysql_fetch_array($result))
{
print "url: ".$row['url']."<br>hits: ".$row['how_many']."<br><br>";
}
?>

Now this should print out the results, but instead I get the following error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource on line 11

But I don't understand - surely the syntax is fine??? The line it is falling over on is: while($row=mysql_fetch_array($result))

Can anyone help?

Jonathen

[sarah]

Often, the problem is not with the line, but with the query. If the query is not valid, mysql_fetch_array() cannot process the $result.

Is part of this line missing?

$SQLquery="SELECT SUM(clicks) as how_many,url FROM clicks WHERE YEAR(date)='$ye$

MONTH(date)='$month' GROUP BY url ORDER BY how_many DESC";

[jonathen]

Sorry, that was because I copied & pasted from my unix shell - the complete line is intact, and is:

$SQLquery="SELECT SUM(clicks) as how_many,url FROM clicks WHERE YEAR(date)='$year' AND 
MONTH(date)='$month' GROUP BY url ORDER BY how_many DESC";