Retrieving Data from MySQL with Perl, Print in HTML Table

In our last post about connecting to MySQL with Perl, we simply connected to the database and retrieved the MySQL version information. We have also looked at inserting data into MySQL via Perl.

In this post, we will connect to a MySQL database, retrieve multiple rows of data, and print a table in HTML with the data.

As in previous posts, we will continue to use an address table. Here is the SQL statement that we used to create the table:

SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `address` (
`name_first` varchar(30) NOT NULL,
`name_last` varchar(30) NOT NULL,
`address_01` varchar(40) NOT NULL,
`address_02` varchar(40) NOT NULL,
`address_city` varchar(30) NOT NULL,
`address_state` varchar(20) NOT NULL,
`address_postal_code` varchar(12) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS = 1;

Be sure to populate your address table with some dummy data.


insert into `address` values('John','Smith','100 Main Street','Suite 500','Buffalo','NY','14201'),
('Dave','Jones','500 Second Avenue','Suite 100','Atlanta','GA','30303'),
('Tom','Watson','123 Golf Course Lane','Suite 1','Macon','GA','31066'),
('Jack','Nicklaus','400 Laurel Oak Dr','Suite 49','Suwanee','GA','31044');


This script isn’t much different than the one mentioned in the earlier post. The data from the MySQL database is returned as an array, and this time we will loop through the array and print the results.

For this example, we will retrieve all of the fields from the address table. Again, we are using a text file to store our database information ($database, $host, $userid and $passwd). See the earlier post for more information about this file. The text file should be named accessDB and should be located in the parent directory from the script. The file should contain:

my_database_name
192.168.1.1
mysql_user
password


Here is the Perl script:

- - - - START SCRIPT - - - - (do not include this line in the script)
#!/usr/bin/perl -w

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the <a href="http://mysql.com/" />MySQL</a> database
use DBI;
use DBD::mysql;

# we use CGI since this will be executed in a browser
use CGI qw(:standard);

use warnings;

#----------------------------------------------------------------------
# open the accessDB file to retrieve the database name, host name, user name and password
open(ACCESS_INFO, "<..\/accessDB") || die "Can't access login credentials";

# assign the values in the accessDB file to the variables
my $database = <ACCESS_INFO>;
my $host = <ACCESS_INFO>;
my $userid = <ACCESS_INFO>;
my $passwd = <ACCESS_INFO>;

# the chomp() function will remove any newline character from the end of a string
chomp ($database, $host, $userid, $passwd);

# close the accessDB file
close(ACCESS_INFO);
#----------------------------------------------------------------------

# print the header
print header;

# HTML for the beginning of the table
# we are putting a border around the table for effect
print "<table border=\"1\" width=\"800\"> \n";

# print your table column headers
print "<tr><td>First</td><td>Last</td><td>Address 1</td><td>Address 2</td><td>City</td><td>State</td><td>Postal Code</td></tr>\n";

# invoke the ConnectToMySQL sub-routine to make the database connection
$connection = ConnectToMySql($database);

# set the value of your SQL query
$query = "select name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code from address";

# prepare your statement for connecting to the database
$statement = $connection->prepare($query);

# execute your SQL statement
$statement->execute();

# retrieve the values returned from executing your SQL statement
while (@data = $statement->fetchrow_array()) {
$name_first = $data[0];
$name_last = $data[1];
$address_01 = $data[2];
$address_02 = $data[3];
$address_city = $data[4];
$address_state = $data[5];
$address_postal_code = $data[6];

# print your table rows
print "<tr><td>$name_first</td><td>$name_last</td><td>$address_01</td><td>$address_02</td><td>$address_city</td><td>$address_state</td><td>$address_postal_code</td></tr>\n";

}

# close your table
print "</table>\n";

# exit the script
exit;

#--- start sub-routine ------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

# assign the values to your connection variable
my $connectionInfo="dbi:mysql:$db;$host";

# make connection to database
my $l_connection = DBI->connect($connectionInfo,$userid,$passwd);

# the value of this connection is returned by the sub-routine
return $l_connection;

}

#--- end sub-routine --------------------------------------------------
- - - - STOP SCRIPT - - - - (do not include this line in the script)


Here is what the script looks like when I run it from a browser:

 

—————————————–

Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.