Blog for U's Bargain Network

We at U’s Bargain Network collect and compile deals across the Internet every  day. Best deals will be selected based on 1) Must be current and 2) Lowest price by comparing with price history. Since we have Twitter account (@usbargains), we post these best deals to Twitter to help general consumers like you. To do that manually it is tedious and error-prone. We have developed a nice PERL script to help us do this. It can be modified and adopted to other purpose. We’d like to share this with you here. 

==============

#!/usr/bin/perl

# include libraries

#——————————-

require 5.6.0;

use warnings;

use strict;

use DBI;

use Time::Local;

use Data::Dumper;

if ($#ARGV != 3) {

    print “usage: twitter_friends username password\n”;

    exit;

}

my $username = $ARGV[0];

my $password = $ARGV[1];

my $newdeal  = $ARGV[2];

my $direct   = $ARGV[3];

my $dbname   = “your mysql database name”;

my $dbh;

my $sql;

my $sth;

my $todayStr;

getTodayStr();

getConnected();

if ($newdeal != 1) { 

$sql = “SELECT price, name, id_items FROM deals “.

“WHERE (expiration_date >= ‘$todayStr’) AND (imagefile <> ”) AND featured = 1;”;

}

else

{

$sql = “SELECT price, name, id_items FROM deals “.

“WHERE (expiration_date >= ‘$todayStr’) AND (imagefile <> ”) “.

“ORDER BY id_items DESC “.

“LIMIT 0,18;”;

}

updateStatus();

disConnected();

#############################################

#

# send status update to twitter 

#

sub updateStatus {

$sth = $dbh->prepare($sql);

$sth->execute();

my ($price, $name_items, $id_items);

$sth->bind_columns(\$price, \$name_items, \$id_items);

while ($sth->fetch()) 

{

my $price_str = “”; 

if ($price > 0.0)

{

$price_str = “[\$".$price."]“;

}

my $short_name = substr($name_items, 0, 70); 

my $url = “http://usbargains.net/deals/$id_items.html”;

if ($direct == 1)

{

$url = “http://usbargains.net/items/usb_$id_items.html”;

}

my $status=”Deal-”.$price_str.” “.$short_name.” “.$url;

#print $status.”\n”;

$status =~ s/([^A-Za-z0-9])/sprintf(”%%%02X”, ord($1))/seg;

my $cmd = “curl -X POST -u $username:$password \”http://twitter.com/statuses/update.xml?status=”.$status.”\” >/dev/null”;

system($cmd);

}

$sth->finish();

}

#############################################

#

# connect to database and generate a handle

#

sub getConnected {

# return the database handle object to the caller

# Set the parameter values for the connection

#——————————-

my $host=”localhost”;

my $connectionInfo = “DBI:mysql:$dbname;$host”;

my $databaseUser = “your database username”;

my $databasePw = “your database password”;

# Connect to the database

# Note this connection can be used to 

# execute more than one statement

# on any number of tables in the database

#——————————-

$dbh = DBI->connect($connectionInfo, $databaseUser, 

   $databasePw) || die “Connect failed: $DBI::errstr\n”;    

}

######################################

#

# disconnect the database handle

#

sub disConnected {

$dbh->disconnect();

}

######################################

#

# generate today’s date string

#

sub getTodayStr {

# get local time

my ($sec,$min,$hour,$iDay,$iMonth,$iYear,$wday,$yday,$isdst) = localtime time;

$iYear = 1900 + $iYear;

$iMonth++;

$todayStr = “$iYear-$iMonth-$iDay”;

}