While creating a simple table viewer using Perl, programmers will be able to understand how to connect to MySQL databases and run queries to extract data from tables.
The basic premise for the code will be to create a table viewer using Perl, Programmers will learn how to establish a connection with a database, as well as preparing and executing MySQL queries. This tutorial assumes that programmers already have a database with several tables set up.
The following code was created by Don Colton, Professor at Brigham Young University – Hawaii, and is used with his consent. The complete code can be found on his website.
Beginning the Script
The script begins with:
#!/usr/bin/perl -Tw
This first section tells the server where to find the Perl executable, the -Tw is a combination of -T and -w. -T enables taint checking, which stops unsecure code from running. -w turns warnings on, making the code easier to debug.
use DBI;
‘DBI’ stands for Database Interface, it is a Perl library which allows the code to connect to and interact with various databases.
chomp ( $in = <STDIN> ); # read cgi input
$in = “&$in&”; # add & sentinels
Chomp removes newline characters in the input, which comes from the form below. Adding sentinels is also good practice whenever searching for key phrases.
Connecting to the Database
Using DBI to connect:
# replace DDD, HHH, etc with your info
$st = DBI->connect ( “DBI:mysql:DDD:HHH”,”UUU”, “PPP” );
The variable $st will connect to the specified database whenever a query needs to be run. DDD stands for the database name, HHH is the host, UUU is the username and PPP is the password.
print “Content-type: text/html\n<html><head>
<title>Table Viewer</title></head>
<body><h1>Table Viewer</h1>
Please select a table for viewing.
<form method=post>\n”;
Although the program is written in Perl, the output will be displayed by the server on a webpage. So the basic html shell and heading are printed before the form.
tableButtons();
This code calls the subroutine ‘tableButtons()’ below. The subroutine will display a button for each table within the database. When a table is selected, the contents of the table will be displayed.
Processing Perl Input
To parse the input:
# if table is specified list its contents
if ( $in =~ /&table=([^&]+)&/ ) {
The variable $in contains the user input. ‘=~’ compares $in to the string /&table=([^&]+)&/. The string begins with ‘/&’ and ends with ‘&/,’ which are the sentinels declared previously. ‘table=’ refers to the input button’s name, when the form is processed every button is named table, but its value is the name of the actual table.
The value of the table is compared to ‘([^&]+).’ ‘[^&]’ refers to any character, while the ‘+’ means one or more. Therefore, the code is searching for any input where the table name contains one or more character. When found the value of ‘([^&]+)’ is stored in the variable $1, subsequent variables are stored in $2, $3 and so forth.
tableRows($1); }
The subroutine tableRows() is declared below, the function call above passes the variable $1, which contains the table name. The tableRows() subroutine will display the contents of that table.
Closing the Database Connection
To end the main shell:
# end of processing
print “</body></html>\n”;
$st->disconnect();
To finish the page, we end the html shell and disconnect from the database.
Preparing, Executing and Finishing the Query
The first subroutine begins:
sub tableButtons {$q1 = $st->prepare ( “show tables” );
After calling the subroutine, the variable $q1 prepares the MySQL query to be passed to the database, in this case it will show the names of every table in the database and create a button for each one.
$q1->execute();
After a query is prepared in Perl, it must then be executed.
while ( @z = $q1->fetchrow_array() ) {
For each table name the query does a ‘fetchrow_array()’ which stores the table details in the array ‘@z.’ As long as there are still table names to process, the while loop will continue to execute.
print “<input type=submit”;
print ” name=table value=’$z[0]’>\n”; }
A button is created each time the query retrieves a table name from the database. Each button will have the same name, but each will have a different value. The value is taken from the array storing the table’s details, ‘$z[0]’ is the first cell in the array and contains the name of the table. When the button is clicked, this value is passed into the input fro processing.
$q1->finish();}
Once the query is completed the finish() function is executed.
Displaying Table Content
The second subroutine begins:
sub tableRows {
my ( $table ) = @_; # local variable
The ‘tableRows()’ subroutine is called when a button is clicked and it takes in the name of the table. The table’s name is then stored in the local variable ‘$table.’ This subroutine will display the contents of the table.
print “<h1>Contents of Table $table</h1>
<table border=1>\n”;
$query = “select * from $table”;
After declaring the table whose contents will be shown, the table is created and the query setup. The query is setup a little differently from the previous query declaration. Whether the query is stored in a variable or passed directly into the prepare function does not affect the outcome of the program; however, it is useful to store the query in a variable for programs that require it to be executed more than once. This specific query selects every cell from the selected table.
$q2 = $st->prepare ( $query );
$q2->execute();
while ( @z = $q2->fetchrow_array() ) {
As described above, the query is then prepared, executed and the values of each row stored in an array.
print “<tr>”;
foreach $cell ( @z ) {
A new row is created for each row in the table being retrieved from the database. Then, for each cell in the row stored in ‘@z’ the following loop is run.
print “<td>$cell” }
print “\n”; }
For each cell in the table a new cell is created, with the contents of the cell being passed from the database to the array @z and then stored in the variable $cell. End tags for <td> and <tr> can also be easily added to make the code standards compliant.
$q2->finish();
print “</table>\n”;
}
Once again the query is finished, the table closed and the final subroutine is complete.
Recent Comments