Category: CGI Programming
Author/Contact Info Hero Zzyzzx
Description: Neat little program to create a categorized link list with click-thru tracking. It uses CSS for output formatting. I've only been perl programming for a couple of months, so please go easy on me. I know I need to learn the CGI module, etc., but all things in due time.
This works well included as SSI
The mySQL table was created with the command below, I added the indeces thinking I might use them down the road.
create table links (urllink varchar(120) not null, 
index urllink (urllink),  
urlcategory char(120) not null,
index urlcategory (urlcategory),
description tinytext not null, 
visits smallint unsigned not null);

[download]

I'm sure you could tweak the field definitions.

Comments?

UPDATE, 4/5/01

I fixed the code to run under strict and use CGI.pm for parameter parsing. If I had more time, I'd also fix it to use CGI.pm to output all the HTML, but I don't think it's worth fixing at this point.

 
#!/usr/bin/perl -wT
use strict;
use DBI;
use CGI;

my $q= new CGI;

my $dbh;

#Database Connect
$dbh = DBI->connect( "dbi:mysql:dansite","dan","vindaloo") or
dienice("Can't connect: ", $dbh->errstr);


if (!$q->param("action")){
    linklist();
        
}elsif($q->param("action") eq "loadpage"){
    loadpage();
    
}elsif($q->param("action") eq "savepage"){
    savepage();
    
}else{
    linklist();
    
}
    
$dbh->disconnect();

sub loadpage{
    my $ath;
    my $linktoprint= substr($ENV{'QUERY_STRING'},24,100);
    $ath=$dbh->do("update links set visits=visits+1 where urllink=\"$l
+inktoprint\"") 
    or dienice($dbh->errstr);
    print"Location:$linktoprint\n\n";
}

sub savepage{
    my $category= $q->param("category");
    $category=~s/\+/ /g;
    my $description=$q->param("description");
    $description=~s/\+/ /g;
    my $sth = $dbh->prepare("insert into links values(?,?,?,?)");
    $sth->execute($q->param("urllink"),$category,$description,$q->para
+m("visits")) 
    or dienice($dbh->errstr);
    $sth->finish();
    print"Location:/addlink.html\n\n";
}

sub linklist{
    my ($urllink,$urlcategory,$description,$visits,$catttest,$tbkgrnd)
+;
    print "Content-type:text/html\n\n";
    my $sth=$dbh->prepare("select urllink,urlcategory,description,visi
+ts ".
        "from links order by urlcategory,description") 
        or dienice("can't execute query ",$dbh->errmsg);
    $sth->execute;
    my $cattest = "bb";
    print"<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" widt
+h=\"100%\">\n";
    while(($urllink,$urlcategory,$description,$visits)= $sth->fetchrow
+_array){
        if ($urlcategory ne "Our Site"){$tbkgrnd = "#993300"} 
            else {$tbkgrnd = "#660000"}
        
        if ($urlcategory ne $cattest){
            if ($cattest ne "bb"){
                    print"<tr><td colspan=\"3\"><hr noshade style=\"co
+lor: AAAAAA\">",
                    "</td></tr>\n";
                    }
            print"<tr>\n<td colspan=\"3\" bgcolor=\"$tbkgrnd\">",
                "<font class=\"littleheader\">$urlcategory</font></td>
+</tr>\n";
            }
            
        print"<tr>\n<td class=\"linkfont\"  bgcolor=\"$tbkgrnd\">"
            "<font size=\"1\">&nbsp;&nbsp;</font></td>\n",
            "<td class=\"linkfont\" bgcolor=\"$tbkgrnd\"><font size=\"
+1\">",
            "<a href=\"/cgi-bin/link.pl?action=loadpage&urllink=$urlli
+nk\" ",
            "target=\"_win\">$description</a>",
            "</font></td>\n<td class=\"linkfont\" bgcolor=\"$tbkgrnd\"
+>",
            "<font size=\"1\">&nbsp;&nbsp;$visits</font>",
            "</td>\n</tr>\n";
        $cattest = $urlcategory;
        }
        print"<tr>\n<td align=\"right\" class=\"linkfont\" colspan=\"3
+\"><font size=\"1\">",
            "<hr noshade style=\"color: AAAAAA\">Total<br>",
            "Visits From<br>This Page</font></td>\n</tr>\n",
            "</table>\n";
        $sth->finish();
    }
Replies are listed 'Best First'.
Re: mySQL Based link list, with click-thru tracking
by mirod (Canon) on Feb 20, 2001 at 13:10 UTC

    OK, so a couple of generic comments:

    • first this section is meant for complete code, not code where some sub-routines (parseget and dieniceare missing.
    • I know I need to learn the CGI module, etc., but all things in due time.
      _Now_ is the time. Actually before you wrote this program would have been the time. It's not like CGI.pm is hard to grasp: add a use CGI pw(:cgi-lib); at the top of your code, then my %FORM= Vars; instead of your call to parseget and your code will run unchanged, except that the parsing is most likely way more robust than what you have right now. It takes about 10 minutes of scanning the CGI.pm doc to find it, how long did it take you to write parseget?
    • Your indentation style is highly confusing, in your main loop the block under <code>if ($urlcategory ne $cattest){ is not indented, this is the kind of code that makes Python evangelists happy ;--(
    • I see you use -wT which is good, but you forgot their friend use strict;. I would recommend you use strict for anything that's not a one-liner. It will catch typos in variable names for examples.
[reply]
[d/l]
[select]
Re: mySQL Based link list, with click-thru tracking
by Anonymous Monk on Feb 20, 2001 at 22:57 UTC
    Oops. I got a little excited with my first post *blush*. Thanks for the feed back. I'll clean up my indenting and only post complete code in the future.
[reply]

Back to Code Catacombs