We have our tables in place.
Its time to get with the code.

Lets get all the search engines and their attributes first.

$res_se = mysql_query('select * from search_engines', $conn);
$se = array();
while ($row_se = mysql_fetch_object($res_se)) {
    $se['id'][] = $row_se->se_id;
    $se['name'][] = $row_se->se_name;
    $se['regex'][] = $row_se->se_regex;
}

Lets collect our referers

$sql = 'select view_ref from view_log';
$res = mysql_query($sql, $conn);
$refs = array();
while ($row = mysql_fetch_object($res)) {
    $refs[] = $row->view_ref;
}
mysql_free_result($res);

Before we find out the keywords from the Referers, Let me explain how do you form a regular expression to grab out the keyword.
Let us consider someone searches a keyword “ruturaj” on google.com.
So the url where google shows the listing of my page will be
http://www.google.co.in/search?hl=en&q=ruturaj&btnG=Google+Search&meta=
This will also be the referer.

The most important part of this URL is the string “q=ruturaj”, and then the keyword “ruturaj” from that string.
let us start…

We have the Search Engine Attributes, and the referers, now is the time to apply the regular expression of the search engines to referers and grab out the keyword term.

$keywords = array();
$keyword = '';
$keyword_count = 0;
$se_cnt = array();

for ($i=0; $i<count($refs); $i++) {
   for ($j=0; $j<count($se['id']); $j++) {
       if(preg_match($se['regex'][$j], $refs[$i], $matches))
       {
           $k = strtolower($matches[1]);
           if ( !isset($keywords[$k]) ) { //exists....
               $keywords[$k] = 1;
           } else {
               $keywords[$k] += 1;
           }
           if ( !isset( $se_cnt[$se['name'][$j]] ) ) { //exists....
               $se_cnt[$se['name'][$j]] = 1;
           } else {
               $se_cnt[$se['name'][$j]] += 1;
           }
           //echo "<p>$refs[$i]<br/><b>{$se['name'][$j]}</b> - " . urldecode($matches[1]) . '</p>';
           //echo "<b>{$se['name'][$j]}</b> - " . urldecode($matches[1]) . '<br/>';
           break;
       }
   }
}

Get the array sorted in descending order maintaining their indexes

arsort($keywords);
arsort($se_cnt);

Grab the indexes and the values in arrays…

$query_term = array_keys($keywords);
$query_term_cnt = array_values($keywords);

Finally strip out the url encoding out of the keyword.

$final = array();
for ($i=0; $i<count($query_term); $i++) {
    $final[] = array(urldecode($query_term[$i]), $query_term_cnt[$i]);
}

The first and foremost need is to have access logs, Since in this tutorial, we’ll be logging in MySQL, we need to have a table in which we can maintain a good access log.

For an access log here are the following necessary data fields

• Accessed URL
• Referer to the Acessed URL
• User Agent accessing the document
• IP of the Client
• Datetime stamp

Access Log Table
Here is the table view_log

CREATE TABLE `view_log` (
 `view_id` int(11) NOT NULL auto_increment,
 `view_url` varchar(255) NOT NULL default '',
 `view_ref` varchar(255) NOT NULL default '',
 `view_ua` varchar(255) NOT NULL default '',
 `view_ip` varchar(20) NOT NULL default '',
 `view_datetime` datetime NOT NULL default '0000-00-00 00:00:00',
 PRIMARY KEY  (`view_id`)
) TYPE=MyISAM COMMENT='Saves the log of viewing log'

Search Engines Table
Now we need to have a list of the Search Engines that link us to the site. I’ve taken the most popular of them, and the ones that I know..
The most important field that we have in this table is the se_regex, this field stores the regular expression, that will parse out the keyword from the referered URL.

CREATE TABLE `search_engines` (
`se_id` int(11) NOT NULL auto_increment,
`se_name` varchar(255) NOT NULL default '',
`se_regex` varchar(255) NOT NULL default '',
PRIMARY KEY (`se_id`)
) TYPE=MyISAM COMMENT='Search Engines'

Now that we have a table structure in place lets populate some data into it.
I’ll explain the regular expressions for the engines in the next page.

INSERT INTO search_engines VALUES (1,'Google','/.*google.*?q=([^&]*).*/i');
INSERT INTO search_engines VALUES (2,'Yahoo','/.*yahoo.*?p=([^&]*).*/i');
INSERT INTO search_engines VALUES (3,'MSN','/.*msn.*?q=([^&]*).*/i');
INSERT INTO search_engines VALUES (4,'Netscape','/.*netscape.*?search=([^&]*).*/i');
INSERT INTO search_engines VALUES (5,'AOL','/.*aol.*?query=([^&]*).*/i');
INSERT INTO search_engines VALUES (6,'Alexa','/.*alexa.*?q=([^&]*).*/i');
INSERT INTO search_engines VALUES (7,'AltaVista','/.*altavista.*?q=([^&]*).*/i');
INSERT INTO search_engines VALUES (8,'AllTheWeb','/.*alltheweb.*?q=([^&]*).*/i');
INSERT INTO search_engines VALUES (9,'A9','/.*a9.*?search=([^&]*).*/i');
INSERT INTO search_engines VALUES (10,'DMoz','/.*dmoz.*?search=([^&]*).*/i');
INSERT INTO search_engines VALUES (11,'Lycos','/.*lycos.*?query=([^&]*).*/i');
INSERT INTO search_engines VALUES (12,'Terra Lycos','/.*terra.*?query=([^&]*).*/i');
INSERT INTO search_engines VALUES (13,'Alexa','/.*alexa.*?q=([^&]*).*/i');
INSERT INTO search_engines VALUES (14,'Rediff','/.*rediff.*?MT=([^&]*).*/i');

Keyword Statistics Table
This table will hold keyword and its hit counter.

CREATE TABLE `keyword_search_stats` (
`keyword_search_stats` int(11) NOT NULL auto_increment,
`keyword` varchar(255) default NULL,
`keyword_count` int(11) default NULL,
`update` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`keyword_search_stats`),
KEY `keyword_index` (`keyword`)
) TYPE=MyISAM

I’m creating an index on the keyword column, so that it is easier to search the keywords

Search Engine Statistics Table
This table will hold Search Engine and no. of referals from it.

CREATE TABLE `search_engine_stats` (
`search_engine_stats_id` int(11) NOT NULL auto_increment,
`search_engine` varchar(255) default NULL,
`search_engine_count` int(11) NOT NULL default '0',
`update` datetime default NULL,
PRIMARY KEY (`search_engine_stats_id`)
) TYPE=MyISAM