How to save search queries on Google Custom Search Engine

Does anybody out there run a Google Custom Search Engine?

Still wondering how to save your user’s search queries?

If you host yourself your own CSE, and your server can run PHP/MySQL, here is the solution!

First of all, you have to set up a MySQL table where the search terms searched on your Google Custom Search Engines will be saved: to do so, open your favourite MySQL frontend (like PhpMyAdmin), and run this query:

CREATE TABLE `query` (
`id` int(11) NOT NULL auto_increment,
`query` varchar(255) NOT NULL default '',
`datetime` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM


Done? Well. Now, open the page in which you pasted the Google’s code that displays search results, and add to that page the code that will save data: it grabs search term from the URL:

< ?php
/*-----------------------
First part: db connection
-------------------------*/
$dbhost = "localhost";
$dbname = "mylittledb";
$dbuser = "root";
$dbpass = "password";
$db=mysql_connect($dbhost, $dbuser, $dbpass);
if ($db==FALSE)
die("Error while connecting to MYSQL ".mysql_error());
mysql_select_db($dbname ,$db);
/*------------------------------
Read and save the search query
-------------------------------*/
$querystat = mysql_real_escape_string($_GET['q']);
$datetime = time();
if( ($_SERVER['HTTP_REFERER'] == '') AND ( ($querystat != $_SESSION['prev_search']) OR ( ($datetime - $_SESSION['datetime']) > 60) ) ) {
$insertquery = “INSERT INTO `query` ( `query` , `datetime`) VALUES ( ‘$querystat’ , ‘$datetime’);”;
mysql_query($insertquery, $db);
}
$_SESSION[’datetime’] = $datetime;
$_SESSION[’prev_search’] = $querystat;
?>

That’s all.

This code saves also the time/date of each search, so you can may later analyze searches e.g. per day, per month, or per time period.

Example’d code saves queries only if the referer is blank (i.e. the user did not request the search results page clicking on a link to it, but he has to manually submit the form), and it prevents multiple page refreshes (a minute at least has to pass, or the search query has to be different, before the term is saved into database).

If you don’t want filters, and you want to save right every query, keep just the database connection and the two lines inside the IF statement for the second part.