Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I know I'm thinking too hard on this but I'm missing the obvious way to go about doing this.

I have a forum (SMF) on my site. I want to log into that database and pull everyone's username and current post count. I then setup a new database (called 'contest', table called 'entrants') in which I want to dump every username and count total that is > 0.

From there, the contest db has 3 total fields (username, original and current). Original is the first time it found their name and dumped their post count (this won't change). Current is each time after that that shows what their CURRENT post count is.

But how do I do that? I'm not sure why I'm confused. I need to update if the username was already found but setup a new entry in my new database if they weren't in there yet. How do I do this? It's going to be one script that does both..

This is what I have so far to show you what I'm trying to do 

#!/usr/bin/perl

use CGI::Carp qw(fatalsToBrowser);

use warnings;
use strict;
use CGI qw/:standard/;
use DBI;

###################
# configurations
###################
my $dbase = "everyday_forum";
my $mysql_user = "everyday_everyda";
my $mysql_pass = "";

my $dbase2 = "everyday_contest";
my $mysql_user2 = "everyday_everyda";
my $mysql_pass2 = "";


###################
# end configurations
###################
print header;

my $dbh = DBI->connect("DBI:mysql:$dbase", $mysql_user, $mysql_pass) o
+r
print DBI->errstr;

my $dbh = DBI->connect("DBI:mysql:$dbase2", $mysql_user2, $mysql_pass2
+) or
print DBI->errstr;



######################################
# first things first, read from chat database and pull back everyone w
+ith a positive post count
########################################
my $data = qq(SELECT memberName, posts FROM smf_members where posts > 
+"0");
my $sth = $dbh->prepare($data);
$sth->execute() or die $dbh->errstr;

my ($name, $posts);
$sth->bind_columns(\$name, \$posts);

while($sth->fetch)
{
   print "$name -> $posts<br>";
   my $data = qq(INSERT into forum (
}

[download]

Replies are listed 'Best First'.
Re: database setup
by graff (Chancellor) on Jun 03, 2007 at 18:09 UTC
    MySQL has a nice idiom for this, assuming that the "memberName" field in your "contest.forum" table has a uniqueness constraint (i.e. it is the "PRIMARY KEY" field, or it has "CONSTRAINT UNIQUE" attached to it), and this is enforced by the DB engine (I think this might only work for InnoDB type tables): 
    INSERT into forum (memberName,original) values (?,?)
  on duplicate key update forum set current=? where memberName=?

    [download]
    (not tested -- look around for more info about the "on duplicate key update" syntax, and try it out).

    So, I guess this isn't really a perl question after all, but if you wanted a perl solution instead (e.g. if the DB engine you're using appears not to enforce the uniqueness constraint), the idea would be to do a select first on the "contest" table for the given memberName value; if that returns a non-empty value, update the "current" field for that row; otherwise, insert a new row.

    (If the process involves doing everyone in a single pass, it would be better to pull all the existing names from the "contest" db and hold those in a hash, then do updates or inserts for the new information, depending on whether a given name exists in the hash.)

    update: better yet, do as ysth says below.

[reply]
[d/l]
      INSERT into forum (memberName,original) values (?,?)
  on duplicate key update forum set current=? where memberName=?

      [download]
      Assuming you want current set even the first time, do it with: 
      INSERT into forum (memberName,original,current) values (?,?,?)
  on duplicate key update forum set current=values(current) where memb
+erName=?

      [download]
      if you wanted a perl solution instead (e.g. if the DB engine you're using appears not to enforce the uniqueness constraint), the idea would be to do a select first on the "contest" table for the given memberName value; if that returns a non-empty value, update the "current" field for that row; otherwise, insert a new row.
      The select is redundant; try the update, and if it processes no rows, try the insert.

      Better yet, if the database engine you're using doesn't enforce a uniqueness constraint, get a better database system.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom