Hi everyone,
I'm just re-documenting the Popular Searches (query log) modification for the latest distribution as there are numerous older threads covering the same topic so this will help new users find the instructions in one place.
Firstly, create and run the following dbmod.php script to create a new `querylog` table to hold search terms and stats:
<?php
require("includes/common.php");
$sql = "
CREATE TABLE `".$config_databaseTablePrefix."querylog` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`count` INT ( 11 ) NOT NULL ,
`query` VARCHAR( 255 ) NOT NULL ,
UNIQUE (`query`)
) ENGINE = MYISAM";
database_queryModify($sql,$result);
print "Done.";
?>
Next, since we only want to log requests to search results invoked by a user submitting the search form we need to add a hidden field "log" to the form that can be used to trigger the logging function. To add this, edit html/searchform.php and look for the closing form tag at line 34:
</form>...and REPLACE with:
<input type='hidden' name='log' value='1' />
</form>Next, in search.php, look for the following code at line 426:
if ($resultCount)
{...and REPLACE with:
if ($resultCount)
{
if (isset($_GET["log"]))
{
$sql = "INSERT IGNORE INTO `".$config_databaseTablePrefix."querylog` SET query='".database_safe($q)."'";
database_queryModify($sql,$result);
$sql = "UPDATE `".$config_databaseTablePrefix."querylog` SET count=count+1 WHERE query='".database_safe($q)."'";
database_queryModify($sql,$result);
}Finally, wherever within your template you wish to display, for example top 3 searches, start with the following basic example, which will display a link to the top 3 search results as an HTML unordered list:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."querylog` ORDER BY count DESC LIMIT 3";
if (database_querySelect($sql,$rows))
{
print "<ul>";
foreach($rows as $row)
{
print "<li><a href='".$config_baseHREF."search.php?q=".urlencode($row["query"])."'>".$row["query"]."</a></li>";
}
print "</ul>";
}Cheers,
David
--
PriceTapestry.com