generating reports on basic db

jonathen

Hi,

I wonder if someone could help - this is probably very easy to do, but I'm struggling as usual!

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)

Any ideas? I would really appreciate some help!

Thanks

Jonathen

thna_brianb

if whatever db you are using supports a count() function, you might be able to do something like this:

SELECT count(*) from tablename WHERE date ='2003-02-%' AND url = '$url'

this should give you the number of entries (clicks) without using the 'click' field

jonathen

Thanks, I'll try that - I am using MySQL, which supports count, so that should work...

Ta again 🙂

jonathen

Okay, now I have two problems. The first is, that I add more and more urls to the db, so will end up losing track of them, so I need my script to automatically drag all unique url's from the db.

Secondly, I just get errors when trying to run the script, which is:

<?php
mysql_connect('localhost','user','pass');
@mysql_select_db(atwgdbclicks) or die( "Unable to select database");
$query="SELECT count(*) FROM clicks WHERE date ='2003-02-%' AND url = ('$url')";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

$i=0;
while ($i < $num) {

$url=mysql_result($result,$i,"url");

echo "$result";

++$i;
}

?>

What is missing from my script? I simply want it to drag the stats from the db, and display as such:

url clicks
url clicks
url clicks

...etc, basically listing all urls (uniquely) and their clicks.

Thanks,

Jonathen

thna_brianb

before you run the "SELECT count(*), you need to run a "SELECT DISTINCT url from tablename WHERE date = '$foo'" then loop through the results and grab a count for each url.

not syntatically great with MYSQL, but a PSQL solution (hopefully)...

<?
$str = "SELECT DISTINCT url FROM tablename"
$qid = pg_exec($con, $str);
for($i = 0; $i < pg_numrows($qid); $i++) {
$fetch = pg_fetch_object($qid, $i);
$str2 = "SELECT count(*) FROM tablename WHERE url = '$fetch->url' AND date = '2003-02-%'";
$qid2 = pg_exec($con, $str);
$get = pg_fetch_object($qid2,0);
$arry['$fetch->url'] = $get->count;
}
?>

hope this works/helps/answers your question ...
sorry so brief, but I'm juggling work and play :rolleyes: