Once You Know, You Newegg

Perl mySQL Create Table

$result = $dbh->prepare(“CREATE TABLE `?` If NOT EXISTS ( `ID` INT( 255 ) NOT NULL AUTO_INCREMENT , `URL` VARCHAR( 255 ) NOT NULL , PRIMARY KEY ( `ID` )) ENGINE = MYISAM ;”);
$result->execute($host);
$result = $dbh->prepare(“INSERT INTO `?` (URL) VALUES (‘?’)”);
$result->execute($host, $href);
print “Host added: ” . $host . “\n”;


So here is a sub from one of my scripts showing this in use.

#!/usr/bin/perl -w
use strict;
use CGI;
use CGI ‘:standard’;
use CGI::Carp qw(fatalsToBrowser warningsToBrowser);
print CGI::header(-expires => ‘-1d’);
use DBI;
$|=1;
my $us_phone_regex =’1?\W*([2-9][0-8][0-9])\W*([2-9][0-9]{2})\W*([0-9]{4})(\se?x?t?(\d*))?';
my $email_regex =’^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$';
my $link_regex = ‘^(((ht|f)tp(s?))\://)?(www.|[a-zA-Z].)[a-zA-Z0-9\-\.]+\.(com|edu|gov|mil|net|org|biz|info|name|museum|us|ca|uk)(\:[0-9]+)*(/($|[a-zA-Z0-9\.\,\;\?\’\\\+&%\$#\=~_\-]+))*$';
##
sub makecitytable{
$city = shift;
$pagelink = shift;
$email = shift;
$phone= shift;
#######################################################
unless( $pagelink =~ m/$link_regex/ ){$pagelink=’failed';}
#######################################################
unless( $phone =~ m/$us_phone_regex/ ){$phone=’none';}
#######################################################
unless( $email =~ m/$email_regex/ ){$email=’none';}
#######################################################
############# I create a table based on a city name $city. Then populate it with all the links found
my ($moe,$larry,$curly) = hookup();
my $hookup = DBI->connect($moe,$larry,$curly) or die “$DBI::errstr”;
### if I wanted unique email or whatever:
### $result = “CREATE TABLE IF NOT EXISTS `$city` (`ad_page` VARCHAR(255),PRIMARY KEY(ad_page),`email` VARCHAR(255),`phone` VARCHAR(26),UNIQUE KEY `email` (`email`))”;
### end unique example
$result = “CREATE TABLE IF NOT EXISTS `$city` (`ad_page` VARCHAR(255),PRIMARY KEY(ad_page),`email` VARCHAR(255),`phone` VARCHAR(26))”;
$result = $hookup->prepare($result);
$result->execute() or die “SQL Error: $DBI::errstr\n”;
#$result->execute($city) or die “Make table did not execute”;
$result = “INSERT INTO `$city` (ad_page,email,phone) VALUES (?,?,?) ON DUPLICATE KEY UPDATE `email`=values(email), `phone`=values(phone)” or die “Prepare insert did not execute”;
$result = $hookup->prepare($result);
$result->execute($pagelink,$email,$phone) or die “Insert email and phone did not execute”;
print qq~ added: pagelink=$pagelink email=$email and phone=$phone into $city
~;
$result->finish;
$hookup->disconnect;
#exit;
}# END SUB
############### connect to the database ######################
sub hookup {
my $moe = ‘DBI:mysql:databasename:localhost:3306′;
my $larry = ‘username’;
my $curly = ‘password’;
return (“$moe”,”$larry”,”$curly”);
}

Comments are closed.