//============================================================================
//
// Google Query Logger
//
// R Fisher 03/2002
//
// If someone hits your site as the result of a Google search, then the
// HTTP_REFERER environment variable will contain the query string they used
// which points to your site.
//
// This set of functions makes it easy to log those query strings in a MySQL
// database. The results can be amusing, and could also help you focus on
// exactly what it is that people visiting your site are looking for.
//
// Bear in mind that if you have a high-traffic site the database could grow
// very big very quickly, and if you use this then you'll most likely have a
// DB search with each page access. So it's probably not much good for big
// sites, but is pretty cool on small ones.
//
// Visit http://www.thecatflap.co.uk/pages/catflap/google.php to see these
// functions in action.
//
// If it hoses all the data on your computer it's not my fault etc. etc.
//
//============================================================================
//----------------------------------------------------------------------------
// DATABASE SCHEMA
// You'll need a table something like this:
// CREATE TABLE $TABLE (
// pkey INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
// page VARCHAR(80) NOT NULL,
// referer VARCHAR(255) NOT NULL,
// last INT NOT NULL,
// total INT NOT NULL
// ) g
// CREATE UNIQUE INDEX g_google_idx1 ON $TABLE (pkey)
// CREATE UNIQUE INDEX g_google_idx2 ON $TABLE (referer)
//----------------------------------------------------------------------------
// VARIABLES
// You need a global variable called $db, which stores the following:
// $db["user"] = "user";
// the username you use to connect to MySQL
// $db["pw"] = "password";
// the password you use to connect to MySQL
// $db["name"] = "database";
// the name of your MySQL database
// $db["host"] = "localhost";
// the name of your MySQL server
// $db["con"] = make_db_con($db);
// calls one of my functions to create a connection to MySQL
//----------------------------------------------------------------------------
// EXAMPLE
//
// include the following code in any page you want to look out for Google
// searches. I have this in a file which is included in every page on my
// site
//
// if (isset($HTTP_REFERER)):
//
// if ($referer = get_google_search_str($HTTP_REFERER))
// google_key_db_update($db, $PHP_SELF, $referer);
//
// endif;
//----------------------------------------------------------------------------
// FUNCTIONS
function get_google_search_str($referer) {
// Pull out the Google search string as best we can. Not 100% accurate,
// but gets enough information to be interesting. Normally you'll call
// this with the value of $HTTP_REFERER as the first argument
if (!ereg("google", $referer) || !ereg("[pq]=", $referer))
return false;
// some google front ends use q= to begin the query string, some use p=
$qd = ereg("q=", $referer) ? "q" : "p";
$delimiter = (ereg("cache", $referer)) ? "+" : "${qd}=";
// Compress whitespace and return the string
return trim(ereg_replace("[ "+t]+", " ",
urldecode(ereg_replace("&.*$", "", preg_replace("/^.*$delimiter/U", "",
$referer)))));
}
function google_key_db_update($db, $page, $referer) {
// Does the database already contain this query? Do case insensitive
// searches
if ($key = get_pkey($db, "g_google", "referer", $referer)):
mysql_query("UPDATE g_google SET last = UNIX_TIMESTAMP(), total =
total + 1 WHERE referer LIKE '$referer'", $db["con"]);
else:
mysql_query("INSERT INTO g_google VALUES(null, '$page', '$referer',
UNIX_TIMESTAMP(), 1)", $db["con"]);
endif;
}
function last_referers($db, $count = 10) {
// A function to help you print the most recent Google searches which
// have brought people to your site. Returns a multidimensional array
// where the elements are:
// page - the page on the site the search pointed to,
// referer - the search string used
// last - the timestamp of the search
$referers = mysql_query("SELECT page, referer, last FROM g_google WHERE
total >=1 ORDER BY last DESC LIMIT $count", $db["con"]);
$ret_arr = array();
$i = 0;
while($row = mysql_fetch_array($referers)) {
$ret_arr[$i]["page"] = $row["page"];
$ret_arr[$i]["referer"] = $row["referer"];
$ret_arr[$i++]["last"] = $row["last"];
}
return $ret_arr;
}
function top_referers($db, $count = 10) {
// just like the last_referers() function, but the "last" element is
// replaced by "total", which is the total number of hits from the
// "referer" search string
$referers = mysql_query("SELECT page, referer, total FROM g_google WHERE
total >=1 ORDER BY total DESC LIMIT $count", $db["con"]);
$ret_arr = array();
$i = 0;
while($row = mysql_fetch_array($referers)) {
// You might have to change this to suit the format of your site
$ret_arr[$i]["page"] = $row["page"];
$ret_arr[$i]["referer"] = $row["referer"];
$ret_arr[$i++]["total"] = $row["total"];
}
return $ret_arr;
}
function google_entry_link($row) {
// return the (Catflap specific) line of HTML for the Google search
// histories. Probably not much good for your site, but left in as an
// example
$col_2 = (isset($row["last"])) ? date("H:i M d", $row["last"]) :
$row["total"];
if ($row["page"] != "none")
$col_2 = "<A HREF="$row[page]">$col_2</A>";
return "n<TR><TD>" . google_link_back($row["referer"]) .
"</TD><TD ALIGN="RIGHT">$col_2</TD></TR>";
}
function google_link_back($str) {
// returns the HTML for a link to the google search for the given
// string. Useful for formatting results. Just feed it the search string
// as the only argument
$str = ereg_replace("[ nt]+", " ", $str);
return "<A HREF="http://www.google.com/search?q=" . urlencode($str) .
"">$str</A>";
}
function make_db_con($db) {
// Looks for a connection to MySQL. If there isn't one, makes one
return @mysql_connect($db["host"], $db["user"], $db["pw"]);
}
//----------------------------------------------------------------------------
// SHELL SCRIPT
// From this point to the end of the file is a script with, provided you
// have a PHP binary on your system, will take a regular Apache log file and
// import the Google query strings into your database.
// --- START SCRIPT ---
#!/usr/local/bin/php -q
<?php
//============================================================================
//
// A simple script which will take a standard Apache log file and put all
// the Google search strings into your database
//
//============================================================================
// put all the functions from earlier in here
// change these
$db["user"] = "user";
$db["pw"] = "password";
$db["name"] = "database";
$db["host"] = "localhost";
$db["con"] = make_db_con($db);
$file = file("/var/log/apache/access_log");
mysql_select_db($db["name"]);
function get_cache_search_str($referer) {
//$referer = ereg_replace("%2.", "", $referer);
//$referer = ereg_replace("hl=..&", "", $referer);
$qstart = strpos($referer, "+");
if (!$qend = strpos($referer, "&"))
$qend = strlen($referer);
$referer = trim(str_replace("+", " ", substr($referer, $qstart + 1,
$qend - $qstart - 1)));
return strtolower(ereg_replace("[ t]+", " ", $referer));
}
foreach ($file as $line) {
$arr = explode(" ", $line);
if ($referer = get_google_search_str($arr[10])):
google_key_db_update($db, "none", $referer);
endif;
}
?>
// --- END SCRIPT ---
?>
|
|
Store
Your email: