Tracking Searches to your site

You may be wondering why in the world would I write a script, when control panels like cPanel have web stats programs with them that tell you this already. Well I say to you, I want to know more than just the top 20 search strings, and I want to be able to use them in my scripts by accessing a databse. So here is how I did it.

Required Files
When I did this, I went the easy route and downloaded a referer class that had the search engines and their query urls setup inside it, so it could detect which engine and string was used for me. The class that I used is called "rc_SEReferer.class". It is free to use too ;-)

Get the class now

Storing in a database
Like I said, I wanted to be able to use the search information in my own scripts in the future. I like knowing what terms and position my sites are on, so it gives me something to do. Below is the MySQL code to create the table. Just copy and paste it into phpMyAdmin or which ever SQL editor you use.

CREATE TABLE `searchstrings` (
  `ID` int(11) NOT NULL auto_increment,
  `search` text NOT NULL,
  `engine` text NOT NULL,
  `page` text NOT NULL,
  `enginepage` text NOT NULL,
  `num` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ID`)
) TYPE=MyISAM;

The PHP Script
The only things you'll need to change are the mysql username, password, and database name. And if you have PHP 4 you'll need to rename the rc_SEReferer.class.php4 to .php.

<?php
include_once('rc_SEReferer.class.php');

$r = urldecode($r);
$u = urldecode($u);

$objRef = new rc_SEReferer($r);
if ($objRef->isSearchEngine()) {

$global_dbh = mysql_connect("localhost","dbuser","dbpass");
mysql_select_db("dbname", $global_dbh);

$engine = $objRef->getSearchEngine();
$search = $objRef->getSearchQuery();

$query = "UPDATE `searchstrings` SET `num`=`num`+1 WHERE `search`".
"='$search' AND `engine`='$engine' AND `page`='$u' AND `enginepage`='$r'";
$result = mysql_query($query, $global_dbh);
if (mysql_affected_rows($global_dbh) == 0) {
$query = "INSERT INTO `searchstrings` ( `ID` , `search` , `engine` , `page` , ".
    "`enginepage` , `num` ) VALUES ('', '$search', '$engine', '$u', '$r', '1')";
   $result = mysql_query($query, $global_dbh);
}

}
?>
var page_didit = 1;

The Javascript File
This site is partially dynamic. I say that because everything is driven by templates, a database, and such, but it actually generates the html pages you are viewing. I did this to reduce the load on the server so if the site becomes popular it won't get slow. So I couldn't just include the script before all my pages, instead I made a javascript that would tell the PHP script the information it needs.

var page_url = ""+document.location;
var page_refer = ""+document.referrer;

document.writeln('<scr' + 'ipt language="JavaScript"'
 + ' src=/pathto/script.php?u=' + escape(page_url)
 + '&r=' + escape(page_refer) + '&z=' + Math.random()
 + '></scr' + 'ipt>');

The final javascript
All you have to do is call the above javascript in any page you want to track, which would be your entire site if you don't block any pages. I haven't made an easy page to view the results but you can easily view them in phpMyAdmin.