grouping http_referer string data

olaf

Hello,

I use this sql statement to count some http_referer information in my mysql table:

SELECT SUBSTRING_INDEX( referer, '/', 3 ) AS ref, COUNT( * ) AS teller
FROM `visits`
WHERE referer <> ''
GROUP BY ref
ORDER BY teller DESC

The problem is that referer information from hotscripts.com is splitted from the value of www.hotscripts.com.

I store the whole referer information in my database.

What is the best way solve this regex?

Najjar

Could you be more specific on what you need?

olaf

I want to select only the domain name from a url:

like:

phpbuilder.com

from

http://www.phpbuilder.com/board/newreply.php?do=newreply&noquote=1&p=10674652

http://phpbuilder.com/board/newreply.php?do=newreply&noquote=1&p=10674652

Najjar

Check this: http://www.webmasterworld.com/forum88/10656.htm

olaf

:bemused:

I want to do this inside my sql statement, how to do this in php is not the point...

Anyone a better idea?

Najjar

You sound angry :-)
Will go to: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
There are 3 string functions you can use to achieve what you want!!!

olaf

Najjar wrote:
You sound angry :-)
Will go to: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
There are 3 string functions you can use to achieve what you want!!!

Like you can see in my question I used already a string function what string functions should I use? Do you checked by yourself what I try to do? and what do you think about regex?

Najjar

This is from http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

Posted by J Soza on March 10 2005 9:28pm

Here's an example of using SUBSTRING_INDEX and TRIM to enable the grouping and counting of referring URLs, where you might have stored URLs like 'http://www.example.com/links', 'http://www.example.com/index.php?func=links', or 'http://example.com' (assuming URL storage field named 'url'):

SELECT COUNT( * ) AS total, TRIM( LEADING 'www.' FROM SUBSTRING_INDEX(TRIM( LEADING 'http://' FROM url ) ,'/', 1)) AS host FROM referers GROUP BY host ORDER BY total DESC

This will take the above-mentioned URL examples, group them as 'example.com' and give you an accurate count of how many referrals that host has generated. Tested on MySQL 4.1.10.

Read the rest of the posts too.
I hope this helps

olaf

Thank you for searching...

It works in mysql 3.23 too...