Sample Perl script to select data from MySQL table using DBI module

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

You require these:

  1. Perl DBD::MySQL module
  2. 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:

CREATE TABLE IF NOT EXISTS `domains` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `url` varchar(100) NOT NULL,
  `descr` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=51 ;

Let's populate this table by executing this INSERT command in MySQL. This will insert 50 rows of domain names, URLs and descriptions.

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%");
$sth->execute();

# 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
$sth->finish;
$dbh->disconnect;

Here's a screenshot.

perl mysql select

Last Updated: Tue Jun 22 22:39:59 2010