SQL query help

Jaffa_Cake · 29 May 2007 at 13:32

marc2003 said:
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

marc2003 · 29 May 2007 at 14:32

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

well i never had negative results. maybe it was a platform/php version thing. i'm running php5 at home on windows.

Manlove is my forte · 29 May 2007 at 16:45

Jaffa_Cake said:
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

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

Jaffa_Cake · 29 May 2007 at 17:23

Kronologic said:
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.

marc2003 · 29 May 2007 at 17:26

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

$result3 = mysql_query("SELECT COUNT(ref), ref FROM logs WHERE ref <> '' GROUP BY ref ORDER BY COUNT(ref) DESC LIMIT 10");

Jaffa_Cake · 29 May 2007 at 17:32

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

I am using your code

LazyManc · 29 May 2007 at 19:59

Just a few tips, they won't make a great deal of difference to your script, but it's best to get into good habbits early on so that you automatically write optimized code.

If your string doesn't contain a variable that needs to be evaluated or a special character, then use single quotes instead of double quotes. This saves execution time because PHP knows that the string is just a string and doesn't require any special attention.

When all you're doing is essentially counting stuff, let the database do the work for you. Try and return as few rows as possible, preferably with everything already totaled:

Code:

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

That should give you a list of agents, with a count of their frequency, ordered by frequency.

If you're filtering by time, then provide your time range in the query rather than returning all rows and then discarding unneeded data with PHP:

Code:

SELECT agent, COUNT(agent) AS frequency 
FROM logs
WHERE time >= '$someDateInPast' 
GROUP BY agent 
ORDER BY COUNT(agent) DESC

When you're returning results from mysql, use mysql_fetch_assoc or mysql_fetch_row rather than mysql_fetch_array unless you explicitly need both indexed and associative arrays (unlikely), otherwise you're returning duplicate rows uneccessarily.

When you're comparing the result of an expression multiple times, first assign the result of the expression to a variable and compare against that so you're not performing exactly the same process over and over again.

i.e.

Code:

..snip..

$agent = strtolower($row2['agent']);
if(strstr($agent , "linux")) {
	if(strstr($agent , "debian")) {

..snip..

marc2003 · 29 May 2007 at 20:07

good stuff, thanks.

i'm completely new to php and mysql so it's just trial and error on my part to get things working.

marc2003 · 29 May 2007 at 22:01

LazyManc said:

just been playing with that and it works well but i want to only to run it on unique ips. i couldn't find anything much to help until i stumbled upon creating a "view". it works but is that the right way to go about it? this is how my useragent loop looks now....

Code:

mysql_query("CREATE VIEW v AS SELECT agent, time FROM logs GROUP BY ip");
$result2 = mysql_query("SELECT agent, COUNT(agent) AS frequency FROM v GROUP BY agent ORDER BY COUNT(agent) DESC");
while($row2 = mysql_fetch_assoc($result2)) {
	$agent = strtolower($row2['agent']);
	$frequency = $row2['frequency'];
	if(strstr($agent, "firefox")) {
		$browser['firefox'] += $frequency;
	}
	if(strstr($agent, "opera")) {
		$browser['opera'] += $frequency;
	}

edit: doing this and changing how i count the hits has shaved a few tenths of a second off the time even though i now have 8 queries now instead of 3. it's now always under 0.2s. :cool:

Jaffa_Cake · 29 May 2007 at 23:18

LazyManc said:

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

SQL query:

Code:

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

marc2003 · 31 May 2007 at 13:00

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.

Jaffa_Cake · 31 May 2007 at 13:38

marc2003 said:
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]

LazyManc · 31 May 2007 at 17:25

Have you guys seen google analytics?

marc2003 · 31 May 2007 at 17:33

LazyManc said:
Have you guys seen google analytics?

i think we both realise there is much better stuff out there, i just did it to learn a little php/mysql.

bbclone is a nice stat counter too. i've used that in the past. (it's where i pinched the browser/os icons for my page.

)