This script tracks referrers over a time period to FuguTabetai.com. You can use the slider below to set the interval. The slider bar control is the excellent DHTML Slider from webfx.eae.net, so you will have to go there to pick that up if you would like to use it. Sorting by referrer or request will generate a count for each referrer and url over the time period.

365 days1 day

= DATE_SUB(NOW(), INTERVAL 4 DAY) GROUP BY request_referrer ORDER BY rcount DESC; // Count unique requests, sort by count // SELECT *, COUNT(*) AS rcount FROM request WHERE request_date >= DATE_SUB(NOW(), INTERVAL 4 DAY) GROUP BY request_url ORDER BY rcount DESC; // database.php creates the database connection with mysql_connect and mysql_select_db require("database.php"); // returns query string as an associative array function parse_query_string($query_string) { $name_value_pairs = explode("&", $query_string); $query_string_array = array(); foreach ($name_value_pairs as $name_value_pair) { $name_value_array = explode("=", $name_value_pair); if (count($name_value_array) == 2) { $query_string_array[$name_value_array[0]] = $name_value_array[1]; } else { $query_string_array[$name_value_array[0]] = ""; } } return $query_string_array; } // query records that have been created in the last 24 hours if (strcmp($sortType, "recent") == 0) { $sql = "SELECT * FROM request WHERE request_date >= DATE_SUB(NOW(), INTERVAL " . $dayRange ." DAY) ORDER BY request_id DESC"; } else if (strcmp($sortType, "url") == 0) { $sql = "SELECT *, COUNT(*) AS rcount FROM request WHERE request_date >= DATE_SUB(NOW(), INTERVAL " . $dayRange ." DAY) GROUP BY request_url ORDER BY rcount DESC;"; } else if (strcmp($sortType, "referrer") == 0) { $sql = "SELECT *, COUNT(*) AS rcount FROM request WHERE request_date >= DATE_SUB(NOW(), INTERVAL " . $dayRange ." DAY) GROUP BY request_referrer ORDER BY rcount DESC;"; } $resultset = mysql_query($sql) or die($sql); // create array of custom search engine search query variable names // 'q' is the default $search_engines = array("search.yahoo" => "p", "images.google" => "prev", "www.google" => "as_q", "www.netster" => "Keywords" ); // print table header print "

Referrer logging during the last " . $dayRange ." days(s)

"; print "\n"; if (strcmp($sortType, "recent") == 0) { print ""; } else { if (strcmp($sortType, "referrer") == 0) { print ""; } else { print ""; } } $numberOfRows=0; // loop through database results one at a time while ($row = mysql_fetch_array($resultset)) { $numberOfRows++; //initialize various variables for output $referrer_query_string = array(); $query_variable = ""; $search_engine = ""; $request_date = $row['request_date']; $referrer_href = $row['request_referrer']; $referrer_display = $row['request_referrer']; $referrer_title = $row['request_referrer']; $request_href = $row['request_url']; $request_display = $row['request_url']; // parse referrer and request url $referrer_parsed = parse_url($row['request_referrer']); $request_parsed = parse_url($row['request_url']); // get referrer hostname and parsed query string $referrer_hostname = isset($referrer_parsed['host']) ? $referrer_parsed['host'] : ""; $referrer_query_string = isset($referrer_parsed['query']) ? parse_query_string($referrer_parsed['query']) : ""; // assume existance of a query string means it's a search engine if (count($referrer_query_string) != 0) { // look for default search keywords variable if (isset($referrer_query_string['q'])) { $query_variable = "q"; } // otherwise loop through list of custom keywords else { foreach($search_engines as $search_engine => $query_variable) { // if the custom search engine hostname fragment occurs anywhere in the referrer hostname // and the custom search engine query variable exists in the query string if(strpos($referrer_hostname, $search_engine) !== false && isset($referrer_query_string[$query_variable])) { break; } // clear out foreach variables $search_engine = ""; $query_variable = ""; } } } // if query variable was set, // change referrer display to the search engine keywords if ($query_variable != "") { $search_engine = $referrer_hostname; $referrer_display = urldecode($referrer_query_string[$query_variable]); } // do some funky urldecode to get at google's image search keywords if (strpos($referrer_hostname, "images.google") !== false) { // decode the prev variable in the query string $previous_query_string = urldecode($referrer_query_string['prev']); // parse the prev variable like a query string $referrer_display = parse_query_string($previous_query_string); // extract the value of /images?q, which is the original image search keywords $referrer_display = urldecode($referrer_display['/images?q']); // do some fancy formating for google image search urls // emulates the "back to results" feature $referrer_href = "http://" . $referrer_hostname . $previous_query_string; $referrer_title = $referrer_href; } // only display the path of the requested page if there is one if (isset($request_parsed['path']) && $request_parsed['path'] != "" && $request_parsed['path'] != "/") { $request_display = $request_parsed['path']; } // reduce display width of referrer urls // as they tend to be very long $referrer_display = substr($referrer_display, 0, 60); // output each "hit" as a table row // with formated time and search engine in separate column print "\n"; if (strcmp($sortType, "recent") == 0) { print "\t"; } else { print "\t"; } print "\t"; print "\t"; print "\t"; print "\n"; } print "

Time	Search Engine	Referrer	Request
Count	Search Engine	Referrer	Request
Count	Search Engine	Referrer	Request
" . date("Y-m-d D G:i:s",strtotime($request_date)) . "	" . $row['rcount'] . "	$search_engine	" . htmlentities($referrer_display, ENT_QUOTES) . "	" . $request_display . "

\n"; print "Total number of rows parsed from query: " . $numberOfRows . "\n"; ?>