Hey all!
I have created my own little statistics page for my website where the visitor count includes both browser, os, time of visit and referer-address. I have a little problem now though.
I wanted to make the main statistics page so it would show the referer-domain and the number of hits from the particular domain. For example instead of having this:
http://www.domain.com/filename1.html -> 10 visits
http://www.domain.com/filename2.html -> 17 visits
Then it would just say:
http://www.domain.com -> 27 visits
That works just fine - BUT - Now I'd like to be able to click on that domain-name to view the details of all the visits I have from that particular domain, and that's where I get the problem.
How do I make an SQL-string that searches through the full referer-addresses in the database - looking only for the domain leaving out the filenames and subfolders
so that I would be able to for example get a listing of all those 27 visits from above split up into the first URL and the second URL like this:
The SQL I used on the main statistics page to list the domains and their total visits looks like this:
$sql = "SELECT *, SUBSTRING(`referrerURL`, 1, LOCATE('/', `referrerURL`, LOCATE(':', `referrerURL`) + 3) - 1) AS `domain`, SUM(`referrerCount`) AS `count` FROM `referrer` GROUP BY `domain` ORDER BY `count` DESC, `domain` ASC";
But for some reason I can't quite figure out how to come any further from here!
Thanx in advance!
/// NetRoam
