Once You Know, You Newegg

mySQL How many rows?

Return the number of rows changed (updated, deleted, etc.) by the last command.

This is usually used after a non-SELECT execute statement.
$rowcount = $sth->rows;

$st->rows ##returns 1

This is handy for many reasons. I use it to bail out of a sub when no rows are returned and for loop security redundancy.
So I can do a :
until ($rows==0){
foreach (@somearray){
print $_.'<br />’;

Which in some cases, I am not actually using @somearray rows! I may just need to process @somearray up to a certain point.

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 = $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;
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
############### connect to the database ######################
sub hookup {
my $moe = ‘DBI:mysql:databasename:localhost:3306′;
my $larry = ‘username’;
my $curly = ‘password’;
return (“$moe”,”$larry”,”$curly”);

mySQL Update – On Duplicate Key

MySQL Performance Tip: ON DUPLICATE KEY is faster than INSERT IGNORE, but failing with a duplicate key error is a lot faster

I’m working on merging a couple of pretty big MySQL databases.

Each database consists of just three tables, but each of those tables has at least 9 million rows.

Because I need to maintain and update a number of data associations, it was necessary to write a custom script to perform the merge. (I chose to use Perl and DBI.)

At one point in my script, I need to do a bunch of “insert this row from database B into database A if it doesn’t exist in database A” type operations. Doing a separate lookup for each record would cost roughly 9 million queries.

At first, I just went ahead and did the insert, allowing the single insert to fail with a unique key error if the record already existed. This caused a bunch of DBD errors to fill the screen, none of which really concerned me, because it didn’t have any bearing on the result. Still, all of those messages printed to STDERR could bury a more serious error, so I looked into handling it another way.

I found this post that recommends using either INSERT IGNORE or ON DUPLICATE KEY to handle these queries. After some investigation, I found that while ON DUPLICATE KEY is a lot faster than INSERT IGNORE, just allowing the thing to fail is the fastest option.

First, some simplified pseudo-code to give you an idea of what I’m doing:


I wanted to avoid doing a separate SQL query at step 3, as that query would have to run 9 million+ times. Since I have a unique key set on the columns I’m interested in, any duplicate rows will not be inserted, thus maintaining my data integrity.

Here are the speed benchmarks I ran for my particular script with each of the three options I tried. The numbers are in terms of iterations of my particular loop (which does a lot more than the pseudo-code above) so the numbers should only be considered relative to each other.

Option 1: Let the query fail with a Unique Key error.

INSERT INTO A (COL1, COL2) VALUES (val1, val2);

Loop iterations per second: 24.5 (avg)

view plaincopy to clipboardprint?


Loop iterations per second: 13.3 (avg)



Loop iterations per second: 18.2 (avg)

As you can see, the fastest of the three options is letting the query fail, followed by ON DUPLICATE KEY as the second fastest.

I’ll leave it to the MySQL experts to explain why this is the case. I have some guesses, but I can’t say for sure.

But I do know one thing: On my setup, for my purposes, letting those queries fail is the quickest option.

Perl Split Function

Using the Perl split() function

The split() function is used to split a string into smaller sections. You can split a string on a single character, a group of characters or a regular expression (a pattern).

You can also specify how many pieces to split the string into. This is better explained in the examples below.
Example 1. Splitting on a character

A common use of split() is when parsing data from a file or from another program. In this example, we will split the string on the comma ‘,’. Note that you typically should not use split() to parse CSV (comma separated value) files in case there are commas in your data: use Text::CSV instead.


use strict;
use warnings;

my $data = ‘Becky Alcorn,25,female,Melbourne’;

my @values = split(‘,’, $data);

foreach my $val (@values) {
print “$val\n”;

exit 0;

This program produces the following output:

Becky Alcorn

Example 2. Splitting on a string

In the same way you use a character to split, you can use a string. In this example, the data is separated by three tildas ‘~~~’.


use strict;
use warnings;

my $data = ‘Bob the Builder~~~10:30am~~~1,6~~~ABC’;

my @values = split(‘~~~’, $data);

foreach my $val (@values) {
print “$val\n”;

exit 0;

This outputs:

Bob the Builder

Example 3. Splitting on a pattern

In some cases, you may want to split the string on a pattern (regular expression) or a type of character. We’ll assume here that you know a little about regular expressions. In this example we will split on any integer:


use strict;
use warnings;

my $data = ‘Home1Work2Cafe3Work4Home’;

# \d+ matches one or more integer numbers
my @values = split(/\d+/, $data);

foreach my $val (@values) {
print “$val\n”;

exit 0;

The output of this program is:


Example 4. Splitting on an undefined value

If you split on an undefined value, the string will be split on every character:


use strict;
use warnings;

my $data = ‘Becky Alcorn’;

my @values = split(undef,$data);

foreach my $val (@values) {
print “$val\n”;

exit 0;

The results of this program are:



Example 5. Splitting on a space

If you use a space ‘ ‘ to split on, it will actually split on any kind of space including newlines and tabs (regular expression /\s+/) rather than just a space. In this example we print ‘aa’ either side of the values so we can see where the split took place:


use strict;
use warnings;

my $data = “Becky\n\nAlcorn”;

my @values = split(‘ ‘,$data);

# Print ‘aa’ either side of the value, so we can see where it split
foreach my $val (@values) {
print “aa${val}aa\n”;

exit 0;

This produces:


As you can see, it has split on the newlines that were in our data. If you really want to split on a space, use regular expressions:

my @values = split(/ /,$data);

Example 6. Delimiter at the start of the string

If the delimiter is at the start of the string then the first element in the array of results will be empty. We’ll print fixed text with each line so that you can see the blank one:


use strict;
use warnings;

my $data = ‘,test,data’;

my @values = split(‘,’,$data);

# We print “Val: ” with each line so that you can see the blank one
foreach my $val (@values) {
print “Val: $val\n”;

exit 0;

The output of this program is:

Val: test
Val: data

Example 7. Split and context

If you do not pass in a string to split, then split() will use $_. If you do not pass an expression or string to split on, then split() will use ‘ ‘:


use strict;
use warnings;

foreach (‘Bob the Builder’, ‘Thomas the TankEngine’, ‘B1 and B2’) {
my @values = split;
print “Split $_:\n”;
foreach my $val (@values) {
print ” $val\n”;

exit 0;

This produces:

Split Bob the Builder:
Split Thomas the TankEngine:
Split B1 and B2:

Example 8. Limiting the split

You can limit the number of sections the string will be split into. You can do this by passing in a positive integer as the third argument. In this example, we’re splitting our data into 3 fields – even though there are 4 occurrences of the delimiter:


use strict;
use warnings;

my $data = ‘Becky Alcorn,25,female,Melbourne’;

my @values = split(‘,’, $data, 3);

foreach my $val (@values) {
print “$val\n”;

exit 0;

This program produces:

Becky Alcorn

Example 9. Keeping the delimiter

Sometimes, when splitting on a pattern, you want the delimiter in the result of the split. You can do this by capturing the characters you want to keep inside parenthesis. Let’s do our regular expression example again, but this time we’ll keep the numbers in the result:


use strict;
use warnings;

my $data = ‘Home1Work2Cafe3Work4Home’;

# \d+ matches one or more integer numbers
# The parenthesis () mean we keep the digits we match
my @values = split(/(\d+)/, $data);

foreach my $val (@values) {
print “$val\n”;

exit 0;

The output is:


Example 10. Splitting into a hash

If you know a bit about your data, you could split it directly into a hash instead of an array:


use strict;
use warnings;


my %values = split(/[=;]/, $data);

foreach my $k (keys %values) {
print “$k: $values{$k}\n”;

exit 0;

The output of this program is:


The problem is that if the data does not contain exactly what you think, for example FIRSTFIELD=1;SECONDFIELD=2;THIRDFIELD= then you will get an ‘Odd number of elements in hash assignment’ warning. Here is the output of the same program but with this new data:

Odd number of elements in hash assignment at ./test.pl line 8.
Use of uninitialized value in concatenation (.) or string at ./test.pl line 11.

mySQL DBI Interface Calls

Perl DBI for MySQL

This section documents the Perl DBI interface
for MySQL.

DBI is a generic interface for many databases. That means that
you can write a script that works with many different database engines
without change. You need a DataBase Driver (DBD) defined for each
database type. For MySQL, this driver is called

You can get man page information about DBI with these:
man DBI
man DBI::FAQ
man DBD::mysql

More DBI/DBD information

For more information on the Perl5 DBI, please visit the DBI web
page and read the documentation:


For more information on Object Oriented Programming
(OOP) as defined in Perl5, see the Perl OOP page:


And of course you can find the latest DBI information at
the DBI web page:


The DBI interface
Portable DBI methods

connect Establishes a connection to a database server
disconnect Disconnects from the database server
prepare Prepares a SQL statement for execution
execute Executes prepared statements
do Prepares and executes a SQL statement
quote Quotes string or BLOB values to be inserted
fetchrow_array Fetches the next row as an array of fields.
fetchrow_arrayref Fetches next row as a reference array of fields
fetchrow_hashref Fetches next row as a reference to a hashtable
fetchall_arrayref Fetches all data as an array of arrays
finish Finishes a statement and let the system free resources
rows Returns the number of rows affected
data_sources Returns an array of databases available on localhost
ChopBlanks Controls whether fetchrow_* methods trim spaces
NUM_OF_PARAMS The number of placeholders in the prepared statement
NULLABLE Which columns can be NULL
trace Perform tracing for debugging

MySQL-specific methods

insertid The latest AUTO_INCREMENT value
is_blob Which column are BLOB values
is_key Which columns are keys
is_num Which columns are numeric
is_pri_key Which columns are primary keys
is_not_null Which columns CANNOT be NULL. See NULLABLE.
length Maximum possible column sizes
max_length Maximum column sizes actually present in result
NAME Column names
NUM_OF_FIELDS Number of fields returned
table Table names in returned set
type All column types

The Perl methods are described in more detail in the following sections.
Variables used for method return values have these meanings:


Database handle


Statement handle


Return code (often a status)


Return value (often a row count)

Portable DBI methods

connect($data_source, $username, $password)

Use the connect method to make a database connection to the data
source. The $data_source value should begin with
Example uses of connect with the DBD::mysql driver:

$dbh = DBI->connect(“DBI:mysql:$database”, $user, $password);
$dbh = DBI->connect(“DBI:mysql:$database:$hostname”,
$user, $password);
$dbh = DBI->connect(“DBI:mysql:$database:$hostname:$port”,
$user, $password);

If the user name and/or password are undefined, DBI uses the
values of the DBI_USER and DBI_PASS
environment variables,
respectively. If you don’t specify a hostname, it defaults to
‘localhost’. If you don’t specify a port number, it defaults to the
default MySQL port (3306).

As of Msql-Mysql-modules version 1.2009,
the $data_source value allows certain modifiers:


Read `filename’ as an option file.


The default group when reading an option file is normally the
[client] group.
By specifying the mysql_read_default_group
option, the default group becomes the [group_name] group.


Use compressed communication between the client and server.


Specify the pathname of the Unix socket that is used to connect to the server.

Multiple modifiers may be given; each must be preceded by a semicolon.

For example, if you want to avoid hardcoding the user name and password into
a DBI script, you can take them from the user’s `~/.my.cnf’
option file instead by writing your connect call like this:

$dbh = DBI->connect(“DBI:mysql:$database”
. “;mysql_read_default_file=$ENV{HOME}/.my.cnf”,
$user, $password);

This call will read options defined for the [client] group in the
option file. If you wanted to do the same thing, but use options specified
for the [perl] group as well, you could use this:

$dbh = DBI->connect(“DBI:mysql:$database”
. “;mysql_read_default_file=$ENV{HOME}/.my.cnf”
. “;mysql_read_default_group=perl”,
$user, $password);


The disconnect method disconnects the database
handle from the database.
This is typically called right before you exit from the program.

$rc = $dbh->disconnect;


Prepares a SQL statement for execution by the database engine
and returns a statement handle ($sth) which you can use to invoke
the execute method.
Typically you handle SELECT statements
(and SELECT-like statements
by means of
prepare and execute.

$sth = $dbh->prepare($statement)
or die “Can’t prepare $statement: $dbh->errstr\n”;


The execute method executes a prepared statement. For
non-SELECT statements, execute returns
the number of rows
affected. I
f no rows are affected, execute returns “0E0”,
which Perl treats as zero but regards as true.
For SELECT statements,
execute only starts the SQL query in the database;
you need to use one
of the fetch_* methods described below to retrieve the data.

$rv = $sth->execute
or die “can’t execute the query: $sth->errstr;


The do method prepares and executes a SQL statement and returns the
number of rows affected. If no rows are affected, do returns
“0E0”, which Perl treats as zero but regards as true.
This method is
generally used for non-SELECT statements which cannot be prepared in
advance (due to driver limitations) or which do not need to executed more
than once (inserts, deletes, etc.). Example:

$rv = $dbh->do($statement)
or die “Can’t execute $statement: $dbh- >errstr\n”;


The quote method is used to “escape”
any special characters contained in
the string and to add the required outer quotation marks.

$sql = $dbh->quote($string)


This method fetches the next row of data and returns it as an array of
field values. Example:

while(@row = $sth->fetchrow_array) {
print qw($row[0]\t$row[1]\t$row[2]\n);


This method fetches the next row of data and returns it as a reference
to an array of field values. Example:

while($row_ref = $sth->fetchrow_arrayref) {
print qw($row_ref->[0]\t$row_ref->[1]\t$row_ref->[2]\n);


This method fetches a row of data and returns a reference to a hash
table containing field name/value pairs. This method is not nearly as
efficient as using array references as demonstrated above. Example:

while($hash_ref = $sth->fetchrow_hashref) {
print qw($hash_ref->{firstname}\t$hash_ref->{lastname}\t\
$hash_ref- > title}\n);


This method is used to get all the data (rows) to be returned from the
SQL statement. It returns a reference to an array of references to arrays
for each row. You access or print the data by using a nested
loop. Example:

my $table = $sth->fetchall_arrayref
or die “$sth->errstr\n”;
my($i, $j);
for $i ( 0 .. $#{$table} ) {
for $j ( 0 .. $#{$table->[$i]} ) {
print “$table->[$i][$j]\t”;
print “\n”;


Indicates that no more data will be fetched from this statement
handle. You call this method to free up the statement handle and any
system resources associated with it. Example:

$rc = $sth->finish;


Returns the number of rows changed (updated, deleted, etc.) by the last
command. This is usually used after a non-SELECT
statement. Example:

$rv = $sth->rows;


Returns a reference to an array of boolean values; for each element of
the array, a value of TRUE indicates that this
column may contain NULL values.

$null_possible = $sth->{NULLABLE};


This attribute indicates
the number of fields returned by a SELECT
statement. You may use this for checking whether a statement returned a
result: A zero value indicates a non-SELECT statement like

$nr_of_fields = $sth->{NUM_OF_FIELDS};


This method returns an array containing names of databases available to the
MySQL server on the host ‘localhost’.

@dbs = DBI->data_sources(“mysql”);


This attribute determines whether the fetchrow_* methods will chop
leading and trailing blanks from the returned values.

$sth->{‘ChopBlanks’} =1;


trace($trace_level, $trace_filename)

The trace method enables or disables tracing. When invoked as a
DBI class method, it affects tracing for all handles.
When invoked as
a database or statement handle method, it affects tracing for the given
handle (and any future children of the handle).
Setting $trace_level
to 2 provides detailed trace information.
Setting $trace_level to 0
disables tracing. Trace output goes to the standard error output by
default. If $trace_filename is specified, the file is opened in
append mode and output for all traced handles is written to that
file. Example:

DBI->trace(2); # trace everything
DBI->trace(2,”/tmp/dbi.out”); # trace everything to /tmp/dbi.out
$dth->trace(2); # trace this database handle
$sth->trace(2); # trace this statement handle

You can also enable DBI
tracing by setting the DBI_TRACE
environment variable. Setting it to a numeric value is equivalent to calling
Setting it to a pathname is equivalent to calling

MySQL-specific methods

The methods shown below are MySQL-specific and not part of the
DBI standard. Several of them are now deprecated:
is_blob, is_key, is_num,
is_not_null, length,
max_length, and table.
Where DBI-standard alternatives exist, they are noted below.


If you use the AUTO_INCREMENT feature of MySQL,
the new
auto-incremented values will be stored here.

$new_id = $sth->{insertid};

As an alternative, you can use $dbh->{‘mysql_insertid’}.


Returns a reference to an array of boolean values; for each element of the
array, a value of TRUE indicates that the
respective column is a BLOB.

$keys = $sth->{is_blob};


Returns a reference to an array of boolean values; for each element of the
array, a value of TRUE indicates that the
respective column is a key.

$keys = $sth->{is_key};


Returns a reference to an array of boolean values; for each element of the
array, a value of TRUE indicates that the
respective column contains numeric values.

$nums = $sth->{is_num};


Returns a reference to an array of boolean values; for each element of the
array, a value of TRUE indicates that the respective column is a primary key.

$pri_keys = $sth->{is_pri_key};


Returns a reference to an array of boolean values; for each element of the
array, a value of FALSE indicates that this column may contain NULL

$not_nulls = $sth->{is_not_null};

is_not_null is deprecated; it is preferable to use the
NULLABLE attribute (described above), since that is a DBI standard.



Each of these methods returns a reference to an array of column sizes. The
length array indicates the maximum possible sizes that each column may
be (as declared in the table description). The max_length array
indicates the maximum sizes actually present in the result table. Example:

$lengths = $sth->{length};
$max_lengths = $sth->{max_length};


Returns a reference to an array of column names.

$names = $sth->{NAME};


Returns a reference to an array of table names.
$tables = $sth->{table};

Returns a reference to an array of column types.
$types = $sth->{type};

This tutorial shows how to use Perl to retrieve rows from a MySQL table using Perl DBI.

You require these:

Perl DBD::MySQL module
MySQL server

If you haven’t got DBD::MySQL installed, you can install it with this:

sudo perl -MCPAN -e “install DBD::MySQL”

Then, test if DBD::MySQL is correctly installed with this:

perl -MDBD::MySQL -e “print 1234”

If it prints 1234, you know it is installed.
Create table “domains”

Create this table by running this SQL command in MySQL:

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`url` varchar(100) NOT NULL,
`descr` varchar(100) NOT NULL,

Let’s populate this table by executing this INSERT command in MySQL. This will insert 50 rows of domain names, URLs and descriptions.
INSERT INTO `domains` (`id`, `name`, `url`, `descr`) VALUES (1, ’99designs’, ‘www.99designs.com/’, ‘Graphic Design’), (2, ‘AddictLab’, ‘www.addictlab.com/index.php/Home’, ‘Advertising, General’), (3, ‘Article One Partners’, ‘www.articleonepartners.com/’, ‘U.S. Patent Reform / Prior Art Research’), (4, ‘ArtistShare’, ‘www.artistshare.com/home/default.aspx’, ‘Music’), (5, ‘Battle Of Concepts’, ‘battleofconcepts.nl/’, ‘Advertising Creative’), (6, ‘Blellow’, ‘www.blellow.com/’, ‘General’), (7, ‘Bon Bon Kakku’, ‘bonbonkakku.com/’, ‘Graphic Design’), (8, ‘BootB’, ‘www.bootb.com/en/’, ‘Advertising, Creative industries’), (9, ‘Brewtopia’, ‘brewtopia.com.au/about-brewtopia.php’, ‘Brewery’), (10, ‘Brownbook’, ‘www.brownbook.net/’, ‘Business, General’), (11, ‘Cafe Press’, ‘www.cafepress.com/’, ‘Accessories, Clothes, Shopping’), (12, ‘ambrian House’, ‘www.cambrianhouse.com/’, ‘General’), (13, ‘Cameesa’, ‘cameesa.com/’, ‘Apparel / Design’), (14, ‘Catwalk Genius’, ‘www.catwalkgenius.com/default.asp’, ‘Fashion / Design / Shopping’), (15, ‘College Prowler’, ‘collegeprowler.com/’, ‘Education’), (16, ‘Colspark’, ‘www.colspark.com/’, ‘Business / Sales / Marketing’), (17, ‘ConsultingCrowd’, ‘consultingcrowd.com/’, ‘Business, general’), (18, ‘Covestor’, ‘www.covestor.com/’, ‘Investing’), (19, ‘Creatad’, ‘www.creatad.com/en-US/watch.aspx’, ‘Advertising’), (20, ‘Create My Tattoo’, ‘www.createmytattoo.com/altD’, ‘Fashion, Design’), (21, ‘Crowdsound’, ‘www.crowdsound.com/’, ‘Customer feedback’), (22, ‘Crowdspirit’, ‘www.crowdspirit.com/’, ‘General’), (23, ‘Crowdspring’, ‘www.crowdspring.com/’, ‘Design’), (24, ‘CureTogether’, ‘www.curetogether.com/’, ‘Medicine’), (25, ‘Current’, ‘current.com/’, ‘Film/TV’), (26, ‘Data Discoverers’, ‘www.datadiscoverers.com/’, ‘Data’), (27, ‘DoNanza’, ‘www.donanza.com/’, ‘Agrregates All crowdsourcing projects from across the web into one place.’), (28, ‘Dream Heels’, ‘www.dreamheels.com/’, ‘Printed High Heels / Graphic Design / Shopping / Footwear’), (29, ‘Ebbsfleet FC’, ‘www.myfootballclub.co.uk/’, ‘Sports’), (30, ‘Ekotekoo’, ‘projets.ekotekoo.fr/’, ‘Cleantech’), (31, ‘Elastic Lab’, ‘www.elasticlab.com/’, ‘Advertising, Video Production’), (32, ‘Exuve’, ‘www.exuve.com/’, ‘Apparel’), (33, ‘EyeKa’, ‘en.eyeka.com/corporate/offers’, ‘Marketing’), (34, ‘Fat Muffin’, ‘fatmuffin.com/’, ‘Advertising’), (35, ‘FeVote’, ‘www.fevote.com/’, ‘General’), (36, ‘Featurelist’, ‘featurelist.org/’, ‘IT/Computers/Internet’), (37, ‘Feedback2.0’, ‘www.feedback20.com/’, ‘Ideas+Answers’), (38, ‘Fellowforce’, ‘www.fellowforce.com/’, ‘General’), (39, ‘Fiskateers’, ‘www.fiskateers.com/’, ‘Arts/Crafts’), (40, ‘Foldit’, ‘fold.it/portal/’, ‘Science’), (41, ‘Freerisk’, ‘freerisk.org/’, ‘Finance’), (42, ‘Galaxy Zoo’, ‘https://www.galaxyzoo.org/’, ‘Aerospace’), (43, ‘GeniusRocket’, ‘www.geniusrocket.com/’, ‘Video, Design, and Copywriting’), (44, ‘Get Satisfaction’, ‘getsatisfaction.com/’, ‘Customer Service’), (45, ‘Global Lives’, ‘globallives.org/’, ‘Film/TV’), (46, ‘Goosegrade’, ‘www.goosegrade.com/’, ‘Editing’), (47, ‘Graniph’, ‘www.graniph.com/’, ‘Apparel’), (48, ‘Guardian (Tech News on Sun Oracle)’, ‘spreadsheets.google.com/ccc?key=pzqNmTm9PsGwLdHQvGrCPLw’, ‘News’), (49, ‘Halfbakery’, ‘www.halfbakery.com/’, ‘Ideas?’), (50, ‘Zazzle’, ‘www.zazzle.com/’, ‘Accessories, Clothes, General, Shopping’);

Save this script as mysqlselect.pl and run it:

#!/usr/bin/perl -w

use strict;
use DBI;

# Variables
my $str = shift || die “– Please enter a search key for domain name\n”; # search string
my $username = ‘xxxx’; # set your MySQL username
my $password = ‘****’; # set your MySQL password
my $database = ‘dddd’; # set your MySQL database name
my $server = ‘localhost’; # set your server hostname (probably localhost)

# Remove end-of-line from input
chomp $str;

# Get the rows from database
my $dbh = DBI->connect(“DBI:mysql:$database;host=$server”, $username, $password)
|| die “Could not connect to database: $DBI::errstr”;
my $sth = $dbh->prepare(‘select name, url from domains where name like ?’)
|| die “$DBI::errstr”;
$sth->bind_param(1, “%$str%”);

# Print number of rows found
if ($sth->rows < 0) { print "Sorry, no domains found.\n"; } else { printf ">> Found %d domains\n”, $sth->rows;
# Loop if results found
while (my $results = $sth->fetchrow_hashref) {
my $domainname = $results->{name}; # get the domain name field
my $url = $results->{url}; # get the URL field
printf ” +— \e[1;42m %s (%s)\e[0m\n”, $domainname, $url;

# Disconnect

mySQL get data into arrays

Okay, say you have a database with some banner data. This was used for a PHP script but, I have not tested in Perl.
The placement there is a column `placement` which could contain data like sideadtop, sideadmiddle, sideadbottom or any position info.
Below will get each found `placement` into its’ own array.

$result = @mysql_query(“SELECT url, image, placement FROM advert WHERE user=’1′ ORDER BY placement”);
$i = 0;
while($process = @mysql_fetch_assoc($result)){
$row[$i] = $process;
$placement = $row[$1][‘placement’];
$$placement[‘url’] = $row[$1][‘url’];
$$placement[‘image’] = $row[$1][‘image’];

so now we have the three arrays
with contents url and image
sorted by placement alphabetically ascending

Here is another way to get data into rows:
############################### get lines into individual arrays ###################################
#This approach stores the results of a SQL call in an array. In this example,
#the query is calling for rows with three columns of data, and printing each

use DBI;

$dbh = DBI->connect(‘DBI:mysql:[DATABASE]‘, ‘[USER]‘, ‘[PASSWORD]‘)
|| die “ERROR: $DBI::errstr”;

$query = “[SQL QUERY GOES HERE]“;

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


$data = $sth->fetchall_arrayref();

foreach $data ( @$data) {

($variable1, $variable2, $variable3) = @$data;

print “$variable1\n”;
print “$variable2\n”;
print “$variable3\n”;



Access mySQL data Efficiently

Elegance, Efficiency, and Robustness

All of the above is fine when you know precisely what the query will be in advance. What about when you need to create a query where one or two of the variables might change? For example, say that you’re writing an application that looks up prices for furniture in a database. The user enters the name of the item of furniture, the program sends the query to the database, and bammo, you’ve got the price. (Sorry, I’m shopping for sofas right now and getting a bit obsessed.) How do you do it?

Well, one way would be to create a new SQL statement each time you run the query. This will mostly work, but it’s not recommended:

# A bad way of doing it while (my $furniture_name = ) { chomp($furniture_name); my $sth = $dbh->prepare("SELECT price FROM furniture_prices WHERE furniture_name='$furniture_name'") or die "Couldn't prepare: '$DBI::errstr'; stopped"; $sth->execute() or die "Couldn't execute: '$DBI::errstr'"; my ($price) = $sth->fetchrow_array(); print STDOUT "Item: $furniture_name Price: $price\n"; }

What’s the problem with this? (Other than that there’s no user interface.) Well, what if one of the chairs is a “Tess of the D’Ubervilles Chair”? Then we’d suddenly get an error, as the apostrophe would get read as an SQL end-quote. The second problem is efficiency. The computer must waste time in every query preparing a statement which is almost exactly like the statement it ran a second ago.

How can we solve these problems? Well, DBI allows the use of things called placeholders in statements. Placeholders tell the database, “at execution time, we’re going to plug a value into here.” Then, you pass the values to fill into the placeholders using the execute() command. Using placeholders, the code would look like this:

# A better way of doing it my $sth = $dbh->prepare("SELECT price FROM furniture_prices WHERE furniture_name=?") or die "Couldn't prepare: '$DBI::errstr'; stopped"; while (my $furniture_name = ) { chomp($furniture_name); $sth->execute($furniture_name) or die "Couldn't execute: '$DBI::errstr'"; my ($price) = $sth->fetchrow_array(); print STDOUT "Item: $furniture_name Price: $price\n"; }

There, the statement handle is only prepared once, and we pass $furniture_type via the execute() method. Plus, when placeholders are used, DBI and the driver know enough to escape any troublesome characters like “‘”.

All of the above works relatively quickly if there’s only one row in the response. But what if we wanted to print out a list of all of the different kinds of loveseats in the database? One might write something like this:

# One way of reading multiple rows my $sth = $dbh->prepare("SELECT furniture_name, price FROM furniture_prices WHERE furniture_type=?") or die "Couldn't prepare: '$DBI::errstr'; stopped"; while (my $furniture_type = ) { chomp($furniture_type); $sth->execute($furniture_type) or die "Couldn't execute: '$DBI::errstr'"; while ( my ($furniture_name, $price) = $sth->fetchrow_array() ) { print STDOUT "Item: $furniture_name Price: $price\n"; } }

Each fetchrow_array() adds a little bit of extra time. The variable has to be read out of the database, copied to the array, then copied to the database. It’ll work, but you can do it much faster. (And cooler.) How? Well, many databases allow you to bind variables directly to a statement, so that as soon as the row is fetched, the variable is filled in with the column’s value.

You bind a scalar variable to a column using the bind_col() method.

$sth->bind_col($column, \$scalar)

So, a better way of doing the above would be:

# Faster way of reading multiple rows my $sth = $dbh->prepare("SELECT furniture_name, price FROM furniture_prices WHERE furniture_type=?") or die "Couldn't prepare: '$DBI::errstr'; stopped"; while (my $furniture_type = ) { my ($furniture_name, $price); chomp($furniture_type); $sth->execute($furniture_type) or die "Couldn't execute: '$DBI::errstr'"; $sth->bind_col(1, \$furniture_name); $sth->bind_col(2, \$price); while ( $sth->fetch() ) { print STDOUT "Item: $furniture_name Price: $price\n"; } }

That’s the most efficient way of reading from the database. The fetch() call above is really just a handy alias to fetchrow_arrayref(), but since we’re not doing anything with the array reference, it’s clearer to say fetch().

So that’s about it. When you’re done with the database, call $dbh->disconnect() to close the connection. If you need or want more information on DBI, do ‘perldoc DBI’, go to the DBI pages. Programming the Perl DBI is an excellent book by Alligator Descartes and Tim Bunce.

Note 1: Different database drivers sometimes handle connection differently; at time of writing DBD::Pg is one of these. See your database driver documentation for how to connect if you’re having problems connecting.

I’m going to go through some things that I thought were lacking in this tutorial. They aren’t necessary, but are helpful.


One thing many perl coders try to do is make their code as short as possible. One way to shorten the code is to use selectrow_array, instead of fetcrow_array. This is helpful if you are only executing a query once, and only getting one row of data.

my @row = $dbh->selectrow_array(q{SELECT my_data1, my_data2 WHERE furniture_type=?},undef, $furniture_type);

This is an extremely simple, and elegant way of selecting, and will be as optimized as any selectrow, that is executed without binding, as $furniture_type is the data executed.


I find RaiseError to be extremely helpful for mission critical queries, and the majority of queries are mission critical to a script. Therefore it may be easier to just set RaiseError, and stop using or die.

$dbh = DBI->connect($DBDSN, $DBUser, $DBPassword, {RaiseError => 1});

To unset RaiseError on a query in which you want to specify something else to do, if the query doesn’t work just add the code: $dbh->{RaiseError} = 0;, and set raiserror back to 1, after you are done.


my ($furniture_name, $price); chomp($furniture_type); $sth->execute($furniture_type) or die "Couldn't execute: '$DBI::errstr'"; $sth->bind_col(1, \$furniture_name); $sth->bind_col(2, \$price);

You don’t need to bind a specific column here. Instead you can just bind all the columns in order, with bind_columns.
So instead of all that code you can write:

chomp($furniture_type); $sth->execute($furniture_type) or die "Couldn't execute: '$DBI::errstr'"; $sth->bind_columns(\my ($furniture_name, $price));

This gets rid of pre-declaring the variables, and using bind_col twice.

Update: Did some research yesterday and found that bind_columns is faster for *all* queries! Strange, but it seems that bind_columns is faster than a fetchrow_arrayref, even if only one row is being caught. This renders fetchrow_array(ref) obselete in a sense, without using bind_columns, as a few lines of code makes a query much faster, regardless of how many rows are being returned.

Perl Push function

Perl's push() function is used to push a value or values onto the end of an array, which increases the number of elements. The new values then become the last elements in the array. It returns the new total number of elements in the array. It's easy to confuse this function with unshift(), which adds elements to the beginning of an array.

@myNames = ('Larry', 'Curly');
push(@myNames, 'Moe');

Picture a row of numbered boxes, going from left to right. The push() function would push the new value or values on to the right side of the array, and increase the elements. In the examples, the value of @myNames becomes ('Larry', 'Curly', 'Moe').

The array can also be thought of as a stack - picture of a stack of numbered boxes, starting with 0 on the top and increasing as it goes down. The push() function would push the value into the bottom of the stack, and increase the elements.

@myNames = (
push(@myNames, 'Moe');

You can push multiple values onto the array directly:

@myNames = ('Larry', 'Curly');
push(@myNames, ('Moe', 'Shemp'));

Or by pushing on an array:

@myNames = ('Larry', 'Curly');
@moreNames = ('Moe', 'Shemp');
push(@myNames, @moreNames);

Custom PHP.ini for shared servers

Here is a php.ini that you can plop into any directory where your server has issues with your script. This overrides the memory, email and a couple other limitations.
Great for phplist and wordpress scripts when there are issues because you are on a shared environment. Be sure to change the email address section to your preferred email.

engine = On
short_open_tag = On
asp_tags = Off
precision = 12
y2k_compliance = On
output_buffering = Off
zlib.output_compression = Off
implicit_flush = Off
serialize_precision = 100
allow_call_time_pass_reference = On
safe_mode = Off
safe_mode_gid = Off
safe_mode_include_dir =
safe_mode_exec_dir =
safe_mode_allowed_env_vars = PHP_
safe_mode_protected_env_vars = LD_LIBRARY_PATH
disable_functions =
disable_classes =
expose_php = On
max_execution_time = 60 ; Maximum execution time of each script, in seconds
max_input_time = 120 ; Maximum amount of time each script may spend parsing request data
memory_limit = 512M ; Maximum amount of memory a script may consume (32MB)
error_reporting = E_ALL & ~E_NOTICE
display_errors = On
display_startup_errors = Off
log_errors = On
ignore_repeated_errors = Off
ignore_repeated_source = Off
report_memleaks = On
track_errors = Off
error_log = error_log
variables_order = “EGPCS”
register_globals = Off
register_argc_argv = On
post_max_size = 8M
gpc_order = “GPC”
magic_quotes_gpc = On
magic_quotes_runtime = Off
magic_quotes_sybase = Off
auto_prepend_file =
auto_append_file =
default_mimetype = “text/html”
include_path = “.:/usr/lib/php:/usr/local/lib/php”
doc_root =
user_dir =
extension_dir = “/usr/local/lib/php/extensions/no-debug-non-zts-20060613″
enable_dl = On
; File Uploads ;
file_uploads = On
upload_max_filesize = 50M
; Fopen wrappers ;
allow_url_fopen = Off
default_socket_timeout = 60
; Dynamic Extensions ;
; Module Settings ;
define_syslog_variables = Off
;[mail function]
; For Win32 only.
;SMTP = localhost
;smtp_port = 26
; For Win32 only.
;sendmail_from = me@localhost.com
;sendmail_from = email@your.com
; For Unix only. You may supply arguments as well (default: “sendmail -t -i”).
;sendmail_path = “/usr/sbin/sendmail -t -i”
;sendmail_path = “/usr/sbin/sendmail -t -i -f email@your.com -r email@your.com”
sql.safe_mode = Off
odbc.allow_persistent = On
odbc.check_persistent = On
odbc.max_persistent = -1
odbc.max_links = -1
odbc.defaultlrl = 4096
odbc.defaultbinmode = 1
mysql.allow_persistent = On
mysql.max_persistent = -1
mysql.max_links = -1
mysql.default_port =
mysql.default_socket =
mysql.default_host =
mysql.default_user =
mysql.default_password =
mysql.connect_timeout = 60
mysql.trace_mode = Off
msql.allow_persistent = On
msql.max_persistent = -1
msql.max_links = -1
pgsql.allow_persistent = On
pgsql.auto_reset_persistent = Off
pgsql.max_persistent = -1
pgsql.max_links = -1
pgsql.ignore_notice = 0
pgsql.log_notice = 0
sybase.allow_persistent = On
sybase.max_persistent = -1
sybase.max_links = -1
sybase.min_error_severity = 10
sybase.min_message_severity = 10
sybase.compatability_mode = Off
sybct.allow_persistent = On
sybct.max_persistent = -1
sybct.max_links = -1
sybct.min_server_severity = 10
sybct.min_client_severity = 10
dbx.colnames_case = “unchanged”
bcmath.scale = 0
ifx.default_host =
ifx.default_user =
ifx.default_password =
ifx.allow_persistent = On
ifx.max_persistent = -1
ifx.max_links = -1
ifx.textasvarchar = 0
ifx.byteasvarchar = 0
ifx.charasvarchar = 0
ifx.blobinfile = 0
ifx.nullformat = 0
session.save_handler = files
session.save_path = /tmp
session.use_cookies = 1
session.name = PHPSESSID
session.auto_start = 0
session.cookie_lifetime = 0
session.cookie_path = /
session.cookie_domain =
session.serialize_handler = php
session.gc_probability = 1
session.gc_divisor = 100
session.gc_maxlifetime = 1440
session.bug_compat_42 = 1
session.bug_compat_warn = 1
session.referer_check =
session.entropy_length = 0
session.entropy_file =
session.cache_limiter = nocache
session.cache_expire = 180
session.use_trans_sid = 0
url_rewriter.tags = “a=href,area=href,frame=src,input=src,form=,fieldset=”
mssql.allow_persistent = On
mssql.max_persistent = -1
mssql.max_links = -1
mssql.min_error_severity = 10
mssql.min_message_severity = 10
mssql.compatability_mode = Off
mssql.secure_connection = Off
[Ingres II]
ingres.allow_persistent = On
ingres.max_persistent = -1
ingres.max_links = -1
ingres.default_database =
ingres.default_user =
ingres.default_password =
[Verisign Payflow Pro]
pfpro.defaulthost = “test-payflow.verisign.com”
pfpro.defaultport = 443
pfpro.defaulttimeout = 30

Get two (or more) rows into array

This method uses Perl, mySQL and fetchrow_hashref.
This will allow you to get two or more rows from a mySQL database and have each row with its’ columns available for use later.

Per Row Method: We can get row1 and have access to all its variables like:
$cities[0]->{‘city’} is city from first row.
$cities[1]->{‘city’} is from the second row.
Per Column Method: Each column can be in its’ own array and accessed like:
$all_cities[0] is first city in @all_cities array.
$all_cities[1] is the second city.

So, you can write:

my @cities;
while ($results = $query->fetchrow_hashref)
push @cities, $results;

and then perform any other assignments you want:

my $city = $cities[0]->{'city'}; # city from first row
my $city1 = $cities[1]->{'city'}; # city from second row


my @all_cities = map { $_->{'city'} } @cities;

my $city = $all_cities[0]; # city from first row
my $city1 = $all_cities[1]; # city from second row



#!/usr/bin/perl -w
use strict;
use CGI;
use CGI ‘:standard’;
use CGI::Carp qw(fatalsToBrowser warningsToBrowser);
print CGI::header(-expires => ‘-1d’);
use DBI;

use vars qw($getstate $hookup $query $results $city $state $pages $category $lastupdate $progress $actual $city1 $state1 $pages1 $category1 $lastupdate1 $progress1 $actual1 @cities $city $city1 $state $state1);

$getstate = ‘Arizona’; ## actually comes in from a query eg: $getstate = shift;

my ($moe,$larry,$curly) = hookup(); ##the sub below
my $hookup = DBI->connect($moe,$larry,$curly) or die “$DBI::errstr”;

$query = $hookup->prepare(qq{SELECT `city`,`state`,`pages`,`category`,`lastupdate`,`progress`,`actual` FROM `statesettings` WHERE `pages` > `progress` AND `state` LIKE ? ORDER BY `lastupdate` ASC LIMIT 2});
$query->bind_param(1, “%$getstate%”);
$query->execute() or die “Query did not execute”;
##### end if no data #######
if(!$query->rows) {
print “We have no data to process! All Done.\n”;
while ($results = $query->fetchrow_hashref){

## The Per Row Method ###
## optionally if I want to print them: ##
foreach my $col (keys %{$results}) { print “$col: “.$$results{$col}.”<br />”; }
#OR preferably this will print each row neatly whether you include all data or not.
print ‘City:’ .$results->{‘city’}.'<br />’;
print ‘State:’ .$results->{‘state’}.'<br />’;
print ‘Progress:’ .$results->{‘progress’}.'<br />’;
print ‘Actual:’ .$results->{‘actual’}.'<br /><br />’;
## end of print option ##

push @cities, $results;

my $city = $cities[0]->{‘city’}; # city from first row
my $city1 = $cities[1]->{‘city’}; # city from second row
my $state = $cities[0]->{‘state’}; # state from first row
my $state1 = $cities[1]->{‘state’}; # state from second row
## and so on
print qq~city $city ~;
print qq~city1 $city1 ~;
print qq~state $state ~;
print qq~state1 $state1 ~;
## and so on

##Now to get even better! Assign all the variables to their names in these two lines.

my ($city,$state,$pages,$category,$lastupdate,$progress,$actual) = @{$cities[0]}{qw/city state pages category lastupdate progress actual/};

my ($city1,$state1,$pages1,$category1,$lastupdate1,$progress1,$actual1) = @{$cities[1]}{qw/city state pages category lastupdate progress actual/};

## OR The Per Column Method ##

my @all_cities = map { $_->{‘city’} } @cities;
my @all_states = map { $_->{‘state’} } @cities;
## and so on
my $city = $all_cities[0]; # city from first row
my $city1 = $all_cities[1]; # city from second row
my $state = $all_states[0]; # state from first row
my $state1 = $all_states[1]; # state from second row
## and so on
print qq~b city $city ~;
print qq~b city1 $city1 ~;
print qq~b state $state ~;
print qq~b state1 $state1 ~;
## and so on

############### connect to the database ######################
sub hookup {
my $moe = ‘DBI:mysql:databasename:localhost:3306’;
my $larry = ‘username’;
my $curly = ‘password’;
return (“$moe”,”$larry”,”$curly”);