Perl MySQL Select Data

Summary: in this tutorial, we will show you how to query data from MySQL database tables using Perl DBI.

To query data from the MySQL database tables from the Perl program, you use the following steps:

• Connect to the MySQL database by calling the  connect() method.
• Prepare a SELECT statement for the execution by using the  prepare() method of the database handle object. The  prepare() method returns a statement handle object that represents a statement within the MySQL database.
• Execute the SELECT statement by calling the  execute() method of the statement handle object.
• Call fetchrow_array() ,  fetchrow_arrayref() or  fetchrow_hashref() method to fetch data from the result set until there is no row left. You can use the  while statement to iterate the result set.
• Disconnect from the MySQL database.

Perl MySQL SELECT examples

We will use the following tables for the examples:

If you have not yet created the tables, we strongly recommend that you create them by following the creating tables tutorial.

Querying data from one table example

The following example selects data from the links table:

#!/usr/bin/perl
use strict;
use warnings;
use v5.10; # for say() function

use DBI;

# MySQL database configurations
my $dsn = "DBI:mysql:perlmysqldb";
my $username = "root";
my $password = '';

# connect to MySQL database
my %attr = ( PrintError=>0,  # turn off error reporting via warn()
             RaiseError=>1   # report error via die()
           );
my $dbh = DBI->connect($dsn,$username,$password,\%attr);

# query data from the links table
query_links($dbh);

# disconnect from the MySQL database
$dbh->disconnect();Code language: Perl (perl)

The main logic is implemented in the  query_links subroutine:

sub query_links{
  # query from the links table

  my ($dbh) = @_;
  my $sql = "SELECT title,
                    url
             FROM links";
  my $sth = $dbh->prepare($sql);

  # execute the query
  $sth->execute();

  while(my @row = $sth->fetchrow_array()){
     printf("%s\t%s\n",$row[0],$row[1]);
  }       
  $sth->finish();
}Code language: Perl (perl)

The output of the script is as follows:

MySQL Tutorial  http://mysqltutorial.org/
Perl MySQL Insert Demo  https://www.mysqltutorial.org/perl-mysql/perl-mysql-insert/
Perl    http://www.perl.org
MySQL   http://www.mysql.com
Comprehensive Perl Archive Network      http://www.cpan.org/Code language: JavaScript (javascript)

Querying data from one table with parameter example

We often pass the values to the query to select data based on condition. The following example queries data from the  links with specified targets from the links table:

sub query_links_by_target{
  # query from the links table by target

  my ($dbh,$target) = @_;
  my $sql = "SELECT title,url,target
             FROM links
             WHERE target = ?";
  my $sth = $dbh->prepare($sql);

  # execute the query
  $sth->execute($target);

  while(my @row = $sth->fetchrow_array()){
     printf("%s\t%s\n",$row[0],$row[1],$row[3]);
  }       
  $sth->finish();
}Code language: Perl (perl)

We put a question mark as the placeholder in the WHERE clause of the SELECT statement. The placeholder will be substituted by the value that we passed to the  execute() method.

For example, to select links whose target are _self , you call the subroutine as follows:

query_links_by_target($dbh,'_self');Code language: Perl (perl)

The output is:

MySQL Tutorial  http://mysqltutorial.org/    _self
MySQL   http://www.mysql.com    _selfCode language: JavaScript (javascript)

Or to select links whose target are _blank , you pass the value to the  query_links_by_target() subroutine as follows:

query_links_by_target($dbh,'_blank');Code language: Perl (perl)

The output is:

Perl MySQL Insert Demo  https://www.mysqltutorial.org/perl-mysql/perl-mysql-insert/      _blank
Perl    http://www.perl.org     _blank
Comprehensive Perl Archive Network      http://www.cpan.org/    _blankCode language: JavaScript (javascript)

Querying data and dumping the result set

The statement handle object provides a convenient method named  dump_results() for displaying the result set quickly without calling the  fetchrow_* methods.

The following example selects data from the tags table and dumps the result set by using the  dump_results() method:

sub query_tags{
  # query data from the tags table 

  my ($dbh) = @_;
  my $sql = "SELECT * FROM tags";
  my $sth = $dbh->prepare($sql);
  $sth->execute();
  $sth->dump_results( );
  $sth->finish();
}Code language: Perl (perl)

The output is:

'1', 'Perl'
1 rowsCode language: JavaScript (javascript)

Querying data from multiple tables

The following script selects data from three tables: links , tags and  link_tags tables by using a SELECT statement with INNER JOIN clauses.

sub query_all{
  # query data from links, tags, link_tags tables
  my ($dbh) = @_;

  my $sql = "SELECT title, url, target,tag
             FROM link_tags
             INNER JOIN links ON links.link_id = link_tags.link_id
             INNER JOIN tags  ON tags.tag_id = link_tags.tag_id";

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

  $sth->execute();

  while(my $array_ref = $sth->fetchrow_arrayref()){
    printf("%s\t%s\t%s\t%s\n", $array_ref->[0],
                             $array_ref->[1],
                             $array_ref->[2],
                             $array_ref->[3]);
  }
  $sth->finish();
}Code language: Perl (perl)

In this example, we used the  fetchrow_arrayref() method to fetch data from the result set. The  fetchrow_arrayref() method returns a reference to an array rather than an array itself. The  fetchrow_arrayref() method does not copy data into a new array for each row in the result set like the  fetchrow_array() method, therefore, it is faster than the  fetchrow_array() method.

The following is the output of the script:

Comprehensive Perl Archive Network      http://www.cpan.org/    _blank  PerlCode language: JavaScript (javascript)

We got 1 row because there is only one row in the  link_tags table.

In this tutorial, you have learned various ways to query data from the MySQL database tables using Perl DBI API.