Getting the domain name from a referer URL in mysql

08 Nov

Posted by aRo` as MySQL

In this guide i’m going to give a detailed explanation on how u can get the domain name out of a referer URL wich is stored in mysql. The whole process will be executed on the MySQL server.

What i actually want to achieve is a MySQL function that returls me ‘google’ when my referer URL = ‘http://www.google.com’.

It was harder that i thought because there are TLD’s like ‘co.uk’ or you can have a referer like ‘en.search.yahoo.com’. That’s why i use a CASE. Depending on how many ‘.’ there are in the referer and if the referer ends on for instance ‘co.uk’.

All i use in this example is a MySQL database with a table called ‘tblReferer’ and a textfield called ‘referer’.

getting the full domain name

LEFT(referer,IF(LOCATE (’/',referer,8) = 0,LENGTH(referer),LOCATE (’/',referer,8)))

This results in a string like ‘http://www.google.com’

#of ‘.’ occurences in the domain name

LENGTH( LEFT( referer,
IF (LOCATE( ‘/’, referer, 8 ) =0, LENGTH( referer ) , LOCATE( ‘/’, referer, 8 ) ) ))
- LENGTH( REPLACE (LEFT( referer,IF (LOCATE( ‘/’, referer, 8 ) =0, LENGTH( referer ) , LOCATE( ‘/’, referer, 8 ) ) ) ,‘.’,”))

building the case
1:Depending on how many ‘.’ there are in the domain name we throw other functions. If there’s only one i take the text from the last ‘/’ (http://blabla.com)till the first ‘.’

2:I’l take te text between the ‘.’

else: If the reverse of the domain name has a TLD that is in a in a set, I take the string from that point till the first’.', Otherwise i’l take the substring between the first and the second ‘.’. This is needed for referers like ‘co.uk’ or ‘en.search’.
GROUP BY

Because the whole thing of this is that we want to know what company is sending us hits we have to put this code in a group by query.

The result

SELECT CASE LENGTH( LEFT( referer,
IF (

LOCATE( ‘/’, referer, 8 ) =0, LENGTH( referer ) , LOCATE( ‘/’, referer, 8 ) ) )

) - LENGTH(
REPLACE (

LEFT( referer,
IF (

LOCATE( ‘/’, referer, 8 ) =0, LENGTH( referer ) , LOCATE( ‘/’, referer, 8 ) ) ) ,
‘.’,
”

)

)
WHEN 1
THEN MID( referer, INSTR( referer, ‘/’ ) +2, INSTR( MID( referer, INSTR( referer, ‘/’ ) +2 ) , ‘.’ ) -1 )
WHEN 2
THEN REVERSE( SUBSTRING_INDEX( SUBSTRING_INDEX( REVERSE( LEFT( referer,
IF (

LOCATE( ‘/’, referer, 8 ) =0, LENGTH( referer ) , LOCATE( ‘/’, referer, 8 ) ) ) ) , ‘.’, 2 ) , ‘.’, -1 )

)
ELSE
IF (

FIND_IN_SET( SUBSTRING_INDEX( REVERSE( LEFT( referer,
IF (

LOCATE( ‘/’, referer, 8 ) =0, LENGTH( referer ) , LOCATE( ‘/’, referer, 8 ) ) ) ) , ‘.’, 2 ) ,
‘ku.oc,ft.eb’

) =0,
REVERSE( SUBSTRING_INDEX( SUBSTRING_INDEX( REVERSE( LEFT( referer,
IF (

LOCATE( ‘/’, referer, 8 ) =0, LENGTH( referer ) , LOCATE( ‘/’, referer, 8 ) ) ) ) , ‘.’, 2 ) , ‘.’, -1 )

),
REVERSE( SUBSTRING_INDEX( MID( REVERSE( LEFT( referer,
IF (

LOCATE( ‘/’, referer, 8 ) =0, LENGTH( referer ) , LOCATE( ‘/’, referer, 8 ) ) ) ) , LENGTH( SUBSTRING_INDEX( REVERSE( LEFT( referer, LOCATE( ‘/’, referer, 8 ) -1 ) ) , ‘.’, 2 ) ) +2, 100 ) , ‘.’, 1 )

)

)
END ,
COUNT( * )
FROM tblReferer
GROUP BY 1
HAVING count( * ) >1
ORDER BY 2 DESC