[aseek-devel] Stats module for aspseek, more queries ...

[Elsner]

Hello,

I programmed a little Perl-based stats module (based on
qtracking.cgi from Udmsearch contrib directory),
which relies on DBI-DBD (for Mysql), to be downloaded
at http://www.cpan.org/modules/index.html,
and installed via perl Makefile.pl, make, make install):


----
#!/usr/bin/perl

use CGI;
use DBI;
use DBD::mysql;
use POSIX;

my $dbh,$sth;
my @row;
my $q = new CGI;

# Change  here !!!
my $database_name="aspseek12";
my $database_host="localhost";
my $database_port="3306";
my $database_user="root";
my $database_passwd="xyz";
my $sql_query;


print $q->header;
print $q->start_html(-title => "Search Engine Statistics", -BGCOLOR => 
'#FFFFCC');
print "\n";


#############################################
#
#  Connect to DB
#
#############################################

# print "Connected to database Successfully<BR>\n" if
        # $dbh = 
DBI->connect("DBI:mysql:database=$database_name:$database_host:$database_port;","$database_user","$database_passwd");
        $dbh = 
DBI->connect("DBI:mysql:$database_name","$database_user","$database_passwd");


print "<H1>Search engine statistics</H1>\n";

print "<H2>Most popular queries</H2>\n";

$sql_query = "SELECT query, count(query) as query_count
                 FROM stat
                GROUP BY query
                HAVING query_count>1
                ORDER BY query_count DESC;";


print "Prepared Query<BR>\n" if
        $sth = $dbh->prepare($sql_query);

print "Exceuted Query<BR>\n" if
        $sth->execute();


print "<TABLE BORDER=\"1\">\n";
print " <TR>\n";
print "  <TH> Query word[s] </TH>\n";
print "  <TH> # of queries</TH>\n";
print " </TR>\n";


while (@row = $sth->fetchrow_array)
{
         print " <TR>\n";
         foreach (@row)
         {
                 print "  <TD> ";
                 print $_;
                 print " </TD>\n";
         }
         print " </TR>\n";
}

print "</TABLE>\n";

# More queries to add here ...

#############################################
#
#  Disconnect from DB
#
#############################################


# print "Disconnected from database Successfully<BR>\n" if
        $dbh->disconnect;


print $q->end_html;

exit 0;
---

Maybe somebody would like to contribute with some
more useful SELECT statements against the aspseek12 database,
which provide statistics about search engine usage.

Frank
#-------------------------------------------------------#
Dipl.-Math. Frank Elsner
Universitaet Osnabrueck (University of Osnabrueck)
- Rechenzentrum - (Computing Center)
Albrechstrasse 28, AVZ
D-49076 Osnabrueck
Deutschland (Germany)

Tel. (Phone): ++49 (0)541/969-2343 Fax: -2470
E-Mail: [EMAIL PROTECTED]
#-------------------------------------------------------#