List:General Discussion« Previous MessageNext Message »
From:Jodie Date:December 27 2000 10:38pm
Subject:Re: Tracking downloads with MySQL/Perl
View as plain text  
You have at least a couple of options:

a.) You log all downloads which would help you produce statistics later - 
since you might include the ip, timestamp, filename etc. in each entry.
CREATE TABLE downloads (
         id int unsigned auto_increment primary key,
         filename char(255) not null,
         hostname char(255) default '',
         ts int unsigned default 0               # our timestamp
);

Each time somebody downloads a file, simply do something similar to this
INSERT INTO downloads (filename, ts) VALUES ('somefile', ?);

Then to get the number of downloads, you can simply do:
SELECT count(*) FROM downloads WHERE filename='somefile';

You might want to reference each filename by ID to speed up the query

b.) You can simply record hits for each file, not each individual download:
CREATE TABLE downloads (
         id int unsigned auto_increment primary key,
         filename char(255) not null,
         howmany bigint unsigned default 1,
         UNIQUE (filename)
);


So when somebody downloads a file, first we check if this file has been 
downloaded before?
SELECT count(*) FROM downloads WHERE filename='somefile'

If the count was 0 (doesn't already exist), we need to create the new entry:
         INSERT INTO downloads (filename) VALUES ('somefile');

If the count was larger than 0 (already exists), we want to increment it:
         UPDATE downloads SET howmany=howmany+1 WHERE filename='somefile'

To get how many times the file has been downloaded:
         SELECT howmany FROM downloads WHERE filename='somefile'


Hope that helps.

Regards,

Jodie

At 07:25  28/12/00, you wrote:


>Hi all,
>
>I am having an issue with a database schema I am trying to put together. 
>Basically I had a perl script that tracked downloaded & logged the count 
>of these downloads to a separate file. Now I would like to use a MySQL 
>database to do the job for me.
>
>Snippet of the old perl code I was using:
>
>#!/usr/bin/perl
>$file = $ENV{'QUERY_STRING'};
>if ($file eq "test"){
>         print "Location: http://www.testsite.com/test.exe\n\n";
>         &log;
>         }
>
>#log downloads to correct file
>sub log()
>         {
>         # Open Correct Logfile and add 1 to the log
>         open(NUMBER,"$file.num");
>         $entries = <NUMBER>;
>         close(NUMBER);
>         $entries++;
>         open(NUM,">$file.num") || die $!;
>         print NUM "$entries";
>         close(NUM);
>         }
>
>Question :
>
>How do I create a database that logs the amount of downloads & just 
>increments the value by one when a new copy is downloaded? There could be 
>in excess of 20 files to track & I just can't work out how to create the 
>tables to cope with this.
>
>I still want to use perl to update the database, just not sure exactly how 
>to. I have come up with this sample perl code which I am sure will do the job:
>
>use DBI;
>
>my $dbh = DBI->connect('DBI:mysql:MyDB', 'username', 'password')
>     or die "Couldn't connect to database: " . DBI->errstr;
>
>$SQL = "select * from motorbikes where model=Yamaha";
>
>$cursor = $dbh->prepare($SQL);
>
>$cursor->execute;
>
>while ( @columns = $cursor->fetchrow ) {
>         print ( ( map { "[$_]" } @columns ) , "\n");
>      }
>
>$cursor->finish;
>
>$dbh->disconnect;
>
>Obviously I don't need to return the  results in this case, just increment 
>the value by one.How?
>
>If anyone could help it would be appreciated.
>
>Regards
>
>Simon Steed
>
>
>--
>---------------------------------------------------------------------
>Please check "http://www.mysql.com/documentation/manual.php" before
>posting. To request this thread, e-mail mysql-thread60037@stripped
>
>To unsubscribe, send a message to:
>    <mysql-unsubscribe-meow=bigpond.net.au@stripped>
>
>If you have a broken mail client that cannot send a message to
>the above address (Microsoft Outlook), you can use:
>    http://lists.mysql.com/php/unsubscribe.php

Thread
Tracking downloads with MySQL/PerlSimon Steed27 Dec
  • Re: Tracking downloads with MySQL/PerlJodie27 Dec
    • Unrecognized function mysql_connect(***)CK Raju28 Dec
      • Re: Unrecognized function mysql_connect(***)Stephen Johnson28 Dec
        • Re: Unrecognized function mysql_connect(***)CK Raju28 Dec
    • Re: Unrecognized function mysql_connect(***)Eric Fitzgerald28 Dec