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.
About these ads

11 Responses to Retrieving Data from MySQL with Perl, Print in HTML Table

  1. Dave Cross says:

    The CGI module has the table, Tr and td functions which are useful for keeping raw HTML out of your Perl programs.

    But really, you should be using some kind of templating system for doing this. I recommend the Template Toolkit.

  2. Fred says:

    Tony,
    Your example using a key using fetchrow_array looks like a clean way to accomplish having section headers.

    I’ve been struggling trying to do the same with fetchrow_hashref(). Can grouping row data be done with fetchrow_hashref()? How do you specify returning a select set of rows (same last name for example) using fetchrow_hashref() and putting that in a loop for each set? Not yet ready to scrap fetchrow_hashref() but getting close!

    Thanks for your reply,
    Fred

    • Tony Darnell says:

      Fred,

      I would think that you could do the same with fetchrow_hashref() – it just returns a hash reference containing the field values.

      I haven’t used fetchrow_hashref much. I would check out this page for more info on how to use it.

      DBI-1.616

      Thanks.

  3. somya says:

    please help me sir! i want execute a program of getting information from the mysql using perl codings when i give certain details in the html tags but i am getting the internal server error why this happens give me a solution

    • Tony Darnell says:

      I would need to know more information about what you are trying to do and what errors you are receiving. A copy of your example code would help as well.

  4. Rakesh says:

    Hi Tony i have used this very same script to display the data but my problem is the output is not shown in html i.e in browser. but i can see the correct output when script is ran from terminal.

    • Tony Darnell says:

      Can you paste your output from your screen into the comments? Be sure to use “code” tags around the comment.

  5. Rakesh says:

    sorry tony i did not defined scope for variable @data in ” while (@data = $statement->fetchrow_array()) ” condition.
    Now the script is working fine.

  6. sathish says:

    nice script Tony.!!! Works well.

    How do i put the whole output to a single variable ? . since i need to pass this to my email module.

    • Tony Darnell says:

      In this part of the script, you could create one variable that contains all of the variables, separated by a delimiter. I will use a tilde (~). This is assuming that you are only retrieving one row.

      # 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];

      $one_variable = “$data[0]~$data[1]~$data[2]~$data[3]~$data[4]~$data[5]~$data[6]”;

      }

      Then you can split the $one_variable later.

      $array = split(“~”, $one_variable);

      $name_first = $array[0];
      $name_last = $array[1];
      $address_01 = $array[2];
      $address_02 = $array[3];
      $address_city = $array[4];
      $address_state = $array[5];
      $address_postal_code = $array[6];

      I haven’t tested this – but it should work.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 40 other followers

%d bloggers like this: