SQL query help

[Jaffa_Cake]

As you can see my signature updates, does this using the GD library in PHP. It currently stores a ton of log information in SQL which is nearly 24,000 entries all containing IP, browser, time and referrer.

Im making a stats file, i've got many things such as how many times your IP viewed the signature, total number of unique viewers etc. ( got some very interesting numbers )

I am trying to find the most popular referrer. To do this i currently use a SELECT DISTINCT then loop through every result which takes absolutly ages seeing as its over 1800 queries.

I'm sure this is possible but i can't seem to find it. Need to find which link is most common in the logs table.

Help please

 

[Jaffa_Cake]

Could you not have one record for each IP Address?

...adding one to the record every time they view?

The IP wouldn't view from the same referrer everytime.

Do you have individual fields for your log entries? I.e [Time], [date], [c-ip] etc.. Or is it literally one long text entry per field of 'visitors'?

Its a table with fields ID, IP, referrer, browser, time and it adds a new entry everytime signature is viewed.

Edit: Don't worry just got it working by looking within PHP instead of sending billions of queries.

It normally takes me 3 revisions to get my code effective. Im terrible heh.

 

[Jaffa_Cake]

Edit: Ignore

 

[Jaffa_Cake]

Your counter just went down on a refresh

It what???

Anyway, I finally found out the issue. The most referrers was no referrer, it that makes any sense ( dam you people with referrer turned off ).

Just changed the query to not include ones with a blank referrers and it works now.

Stats below

http://jaf.richieward.com/sigcounter/jaffa_cake/stats.php

 

[Jaffa_Cake]

I feel its time for me to add more detailed stats to the image. Watch this space

 

[Jaffa_Cake]

one little criticism of your stats page if i may....

you should be basing your browser/os stats off the unique hits, not the total views.

It looks more impressive with total hits I know what you are saying and I will consider it.

Its about 9000 SQL queries to change the method of logging to make it effective.

Only about 20 to change the method of logging and to make it ineffective

It could well be my eyes but it went from blue xxxx6 to red xxxx5 the other day when I refreshed to see if it was measuring a unique IP referral or not. I could be going mad, no wait, madder, though.

Possibly went up 9 ?

Soon I will also be adding more options and custom text on the bottom left/right.

I am currently creating a full signup/control panel for signatures making them fully customizable instead of revising!

 

[Jaffa_Cake]

Thats the method I am currently using. To convert it to an effective method I figured using a SELECT DISTINCT then looping through every IP and creating a new single log for that IP in a different table.

Effectivly having one entry per IP to make things easier, maybe I should have thought this through when i originally posted it Im seeing problems with that now.

Its still being improved and I would love to steal some of your code if you want to MSN me pleeease

Im taking signature offline for maintenance and optimisation. I do like your stats page very much and my method of finding the most popular referrer.. works. BADLY, im sure there must be a better way.

My logging query looks as follows

$query = "INSERT INTO `jaffa_cake_logs` (`id`, `ip`, `referrer`, `browser`, `time`)
VALUES (NULL, " . $sql->safe($_SERVER['REMOTE_ADDR']) . ", " . $sql->safe(@$_SERVER['HTTP_REFERER']) . ", " . $sql->safe(@$_SERVER['HTTP_USER_AGENT']) . ", " . $sql->safe(time()) . " )";

In good tradition my code ALWAYS has to be terribly optimised until about 3 revisions later.

 

[Jaffa_Cake]

well you've already seen my insert statement above. is there any need to validate the server variables? i honesty didn't know they needed checking? i have just the one table containing just the 4 columns. ip, refererer, time and useragent.

as mentioned my results page has 3 queries - one for total hits, one for unique hits and one to get the top 10 referring pages. i don't comment my code unfortunately and it's probably a mess as i'm still a noob - i've only just figured out how to use functions. can't get my head around classes at all...

Classes took me a while too

I always filter ALL of my SQL input just from good practice. The HTTP_USER_AGENT and HTTP_REFERRER variables are taken from the HTTP headers sent by the users browser like mine is:
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.8.1.3) Gecko/20070309 Firefox/2.0.0.3

This can easily by modified sending bad input, same with the referrer.

Ty for code

 

[Jaffa_Cake]

Btw im not sure your generated time is correct, i used the method you are using originally. Now using this

Top

$mtime = microtime();
$mtime = explode(" ",$mtime);
$mtime = $mtime[1] + $mtime[0];
$starttime = $mtime;

Bottom

$mtime = microtime();
$mtime = explode(" ",$mtime);
$mtime = $mtime[1] + $mtime[0];
$endtime = $mtime;
$totaltime = ($endtime - $starttime);

 

[Jaffa_Cake]

well i've changed it but i'm not too sure if it makes any difference. for example if i dump the whole database on one page, it takes about 8 seconds using both timing methods.

Figured it was wrong on mine when i got timings like -3.043 seconds

 

[Jaffa_Cake]

Using Oracle SQL
Select referrer, count (referrer) group by referrer from table;

Im using MySQL, I am using a very similar syntax and I have it working but it has yet to be implemented Pending lots of cool new features too.

 

[Jaffa_Cake]

pretty much identical to the mysql i posted in my code earlier...

I am using your code

 

[Jaffa_Cake]

SELECT agent, COUNT(agent) AS frequency 
FROM logs 
GROUP BY agent 
ORDER BY COUNT(agent) DESC

Showing rows 0 - 29 (967 total, Query took 1.5675 sec)

SQL query:

SELECT browser, COUNT( browser ) AS frequency
FROM jaffa_cake_logs
GROUP BY browser
ORDER BY COUNT( browser ) DESC
LIMIT 0 , 30

I'm not liking the look of that 1.5 seconds. I'l have a play with it.

Maybe its my server being sucky Sends server CPU to 100% while it does the query.

Edit: Or maybe its because its trawling through 54,927 entries

 

[Jaffa_Cake]

i've finished mine now. i'm using the builtin mysql date/time functions instead of php and added a little colour.



got rid of the referring pages as it's useless. :D[/QUOTE]

I like very much. Will be trying to beat your stats page today. Have been working on the physical signature code yesterday.

All text on the image is pulled from SQL, added special commands such as {UVIEWS} and {TVIEWS} {CREATED} etc. Main message is pulled from SQL which looks like this:
[CODE]
You are viewer: {TVIEWS}
Unique viewers: {UVIEWS}
[/CODE]

and in the bottom left is lifted from SQL which uses the {CREATED} command :).

Will be working on stats soon :) Been looking at this [url]http://www.aditus.nu/jpgraph/[/url]

[SIZE=1][COLOR=Red]Yes, I do have too much free time[/COLOR][/SIZE]