While running thebandmind.com, I thought it would be fun to have a Twitter account which automatically is updated twice a day with a random quote from the database of quotes. This Perl script is run from Cron.
use lib "/home/sceneki/perl-libs"; use Data::Dumper; use Net::Twitter::Lite; ######################### # Settings our($cfg); $cfg->{'sqlserv'} = 'localhost'; $cfg->{'sqluser'} = 'user'; $cfg->{'sqlpass'} = 'password'; $cfg->{'sqlbase'} = 'database'; $cfg->{'twitteruser'} = 'twitteruser'; $cfg->{'twitterpass'} = 'twitterpass';
use DBI;
my $dbh = DBI->connect("dbi:mysql:".$cfg->{'sqlbase'}.':'.$cfg->{'sqlserv'}.":3306", "$cfg->{sqluser}", "$cfg->{sqlpass}") ordie $DBI::errstr; # connect to db my $sth = $dbh->prepare("SELECT `twitter`,`ID` FROM `quotes` WHERE ID >= (SELECT FLOOR( MAX(ID) * RAND()) FROM `q uotes` ) AND `twitter` IS NOT NULL ORDER BY `tweets` ASC, RAND() LIMIT 1;") ordie"Couldn't prepare statement: " . $dbh->errstr; $sth->execute()ordie"Couldn't execute statement: " . $sth->errstr; while (my $dbpull = $sth->fetchrow_hashref()) {$current=$dbpull;}
$dbh->do("UPDATE `quotes` SET `tweets` = `tweets`+1 where `ID` = '$current->{'ID'}'") ordie("Can't find record to update, died");