Posted by JohnForsythe on September 8, 2008 at 1:11am
I thought you guys might be interested in these search numbers from DrupalModules.com. It turns out they're very similar to what's being reported by Drupal.org. One thing to note, my data has been filtered for IP-uniqueness to prevent result spamming (intentional, or not), the Drupal.org data is just coming right out of the watchdog log unfiltered.
Comments
Code
Here's the code used to generate the numbers. It's targeted at Drupal 5. This code is released under the GPL.
<?php
header("Content-type: text/plain");
// how many results to return?
$limit = 250;
$dbh = mysql_connect ("localhost", "username", "password") or die ('I cannot connect to the database.');
mysql_select_db('drupal') or die('Could not select database.');
// we use a subquery to eliminate dupilicate searches by the same hostname
$sql = "SELECT COUNT(message) as count, message ";
$sql .= "FROM (";
$sql .= " SELECT DISTINCT message, hostname ";
$sql .= " FROM watchdog ";
$sql .= " WHERE type = 'search' ";
$sql .= ") as t ";
$sql .= "GROUP BY message ";
$sql .= "ORDER BY count DESC ";
$keywords = array();
// go through the results, clean them up, and deal with duplicates
$result = mysql_query($sql);
while ($row = mysql_fetch_row($result)) {
$keyword = $row[1]; // raw watchdog message for the keyword
$count = $row[0]; // times keyword was searched for
// lowercase everything and remove watchdog cruft
$keyword = strtolower($keyword);
$keyword = str_replace('<em>', '', $keyword);
$keyword = str_replace('</em> (content).', '', $keyword);
// remove categories and content types. we're only interested in the actual keyword.
$keyword = preg_replace('%category\:.*%', '', $keyword);
$keyword = preg_replace('%type\:.*%', '', $keyword);
$keyword = trim($keyword);
// keywords will show up more than once due to removing category and content types from the search phrases.
// we handle this by using an associative array to keep track of the sum for each keyword.
if ($keyword) {
@$keywords[$keyword] += $count;
}
}
// sort keywords by count, descending
arsort($keywords);
// display the data
foreach($keywords as $keyword => $count) {
$num++;
print "$num. $keyword, $count\n";
if ($num == $limit) { break; }
}
?>
This is just a raw PHP script. The advantage is that you can run it without having Drupal installed, you just need a copy of the watchdog table. It could be turned into a Drupal module with a little work.
--
John Forsythe
http://blamcast.net/
Here it is
Here is a version for Drupal 5. I haven't created it for Drupal 6 since I'm much less familiar with the architecture of the new watchdog module so I wasn't sure the query would work.
Rename the file so it has a .zip extension and unzip it.
Hunh it looks like I cannot:
A. attach the file
B. edit the file afterwards because the fieldsets are closed.
Dave
Oh well, just contact me if you want a copy
I guess just contact me if you want a copy of it.
Dave