1 (edited by I like pie 2007-05-16 09:17)

Topic: nifty perl stuff with punbb

It was awhile ago that I integrated my main website's news posts with punbb.  But recently I took it a step further by fine-tuning my old webcomic's navigation and comic-posting with it as well.

This isn't really a 'show off your website' type deal, more like a 'look at a nifty script i wrote' that ties two sets of files together with punbb's db rather nicely.

Honestly, I'm just giddy that it worked so I want to show other people. =P

anyway...

#!/usr/bin/perl/

# 0) preface
# 1) modules, includes, declare variables
# 2) create db connections
# 3) create an array from all images in the strips dir
# 4) run through each strip in the array
#   a) check for an entry in the html dir, set $newspost to either empty or the contents
#   b) create a topic/post in the forum, set timestamp to date of image (name)
#   c) insert row into snat_strips table

# 1) modules, includes, declare variables
  use strict;
  use DBI;
  require "time.pl";
  my $newspost;
  my $subject;
  my $created;
  my $topic;
  my $post;
  my $myquery;

# 2) create db connection
  my $dbh = DBI->connect('DBI:mysql:this:info', 'is', 'fake') or die "Couldn't connect to database: " . DBI->errstr;
  my $dbh2 = DBI->connect('DBI:mysql:this:info', 'is', 'fake2') or die "Couldn't connect to database: " . DBI->errstr;

# 3) create an array from all images in the strips dir
  my @strips = <strips/*>;
  foreach my $strip (@strips) {
    $strip =~ s/\w+\///;
  }

# 4) run through each strip in the array
  foreach my $strip (@strips) {
    my $date = $strip;
    $date =~ s/\.\w+//;
    $newspost = "http://dev.snatcomic.com/index.php?strip=".$date."\n\n";
    if (-e "news/".$date.".html") {
      # news file exists, so open file and set $newspost to contents
      open (TEMP, "<" . 'news/'.$date.'.html') or die "Couldn't open old file: $!";
      while (my $line = <TEMP>) {
        $line =~ s/<br\s*\/>//;
        $newspost .= $line;
      }
      close TEMP;
    } else {
      # nothing found
      $newspost .= "";
    }
    
    # gonna need some variables for the field values.
    # first, $subject will be the strip date
    my $subject = $date;
    
    # second, $created will be the timestamp converted from the file name
    my $created = convertTime($date);
  
    # chomp $newspost
    chomp($newspost);
  
    # add a new topic
    $myquery = "INSERT INTO foo_pun_topics (poster, subject, posted, last_post, last_poster, forum_id) VALUES (?, ?, ?, ?, ?, ?)";
    my $sth = $dbh->prepare($myquery) or die "Couldn't prepare statement: " . $dbh->errstr;
    $sth->execute('I like pie', $subject, $created, $created, 'I like pie', '4') or die "Couldn't execute statement: " . $sth->errstr;
 
    # get id of topic created
    $topic = $sth->{'mysql_insertid'};

    # add a new post
    $myquery = "INSERT INTO foo_pun_posts (poster, poster_id, message, posted, topic_id) VALUES (?, ?, ?, ?, ?)";
    my $sth = $dbh->prepare($myquery) or die "Couldn't prepare statement: " . $dbh->errstr;
    $sth->execute('I like pie', '3', $newspost, $created, $topic) or die "Couldn't execute statement: " . $sth->errstr;

    # get id of post just created
    $post = $sth->{'mysql_insertid'};

    # update topic with recent post info
    $myquery = "UPDATE foo_pun_topics SET last_post_id = $post WHERE id = $topic";
    my $sth = $dbh->prepare($myquery) or die "Couldn't prepare statement: " . $dbh->errstr;
    $sth->execute() or die "Couldn't execute statement: " . $sth->errstr;

    # insert row into snat_strips
    $myquery = "INSERT INTO snat_strips (strip_id, filename, thread_id) VALUES (?, ?, ?)";
    my $sth = $dbh2->prepare($myquery) or die "Couldn't prepare statement: " . $dbh->errstr;
    $sth->execute($date, $strip, $topic) or die "Couldn't execute statement: " . $sth->errstr;
  }

The result?  check out the last page of my (dev) forum sub-section for the webcomic:

http://dev.foohonpie.net/forums/viewfor … =4&p=5

Yep, that's posts seemingly written 3 years ago, way before I had ever heard of punbb.  Nifty!

As if that isn't well enough, I've also modded punbb a bit to upload comic strips on the fly via the new topic post page.

On a side-note: I've decided that I don't need wordpress or anything else for any of my projects.  PunBB is going to be the central content management system for all my projects.  Who needs anything else?

A newfound love for perl, and a rediscovered love for punbb.  w00t

[edit]
hmm.... on second glance it seems my timestamp converting is bugged.  oh well, the meat and potatoes of it all works fine still

Re: nifty perl stuff with punbb

I don't know a thing about Perl... but from what I can tell it looks like you've done some very slick stuff there! Kudos! smile