PerlWiz - Using PerlWiz with MySQL

Click here for the perlwiz.biz documentation home page
Contents Prior Page Next Page

 

MySQL is a database management tool, similar to database server products such as Oracle and Microsoft SQL Server.  The biggest difference is that MySQL is open source, and free to use so long as you don't try to redistribute the software (or change it and redistribute it).  This is why so many Internet Service Providers give you access to create MySQL databases for your web site.

MySQL stores data in a structured format - so you list the items of data (columns or fields) you wish to store to form what is known as a table. A typical table might be 'Contact' for a contact in your name and address book. Each page in your book holds the same slots for information (columns) but the contents can be different on each page. Each page is known in a database as a row or record.

You could store this sort of information as a file, but you can get problems when two people try to change data at once, and the time it takes to retrieve the whole address book can get longer as the address book gets bigger - and of course, what a waste of time if you only wanted to retrieve one address!

A Database Manager stores the data in a form that makes it much faster and safer to retrieve data - just the ticket for a web site where unexpected results could drive traffic away.

Furthermore, data is put into the tables and extracted from the tables using a common language known as SQL (pronounced Sequel) - or Structured Query Language.  This language is shared between all of the major database vendors, so if you learn SQL for MySQL, most of your knowledge will transfer across to building and accessing data in Oracle and SQL Server.

PerlWiz can interact with MySQL in a number of ways:-

In this tutorial, we will be implementing a simple book list or library search database and link it to a web search form.

Our first task will be to create a database and table to hold the book details. We will then populate (i.e. put data into) the book table with a few sample rows of information.

We will read this data back, and when we are happy with the results, create a HTML form to take search criteria to be fed to a Perl program, which will search the books and return a list in the form of HTML tabular output meeting specified criteria.

We will utilise the Perl DBI module to connect to the MySQL database. To do this, we need to make sure that the MySQL DBI driver is available on your computer. We can use the PerlWiz Module Installer to help with this task...

Installing the Perl MySQL DBI Module

Note that at the time of writing, if you wish to use the Module Installer, it will be necessary to have ActivePerl version 5.6.1 or lower installed.  This is due to a problem with ActivePerl which should be rectified in a future release of ActivePerl (currently 5.8.0). 

If you do not have the Perl DBI module installed, you can do so by Clicking on the Tools menu, choosing Module Installer..., typing in DBI in the Module Name text box, and clicking on Search CPAN - note, you need to be connected to the internet before doing this.

Pick out the DBI module, and choose the Install option. Chances are, its already installed, in which case try the Upgrade option to ensure you have the latest version.

Next,  type out MySQL in the Module Name text box, and click the Search CPAN button again. Choose the DBD-mysql option line, and click on the Install option, which installs the MySQL Perl DBI driver.

Click on the Done button when you have finished. You now have access to use MySQL from Perl programs.

Have a look at your ActivePerl help documentation (access via Help and ActivePerl Documentation). Click on where it says Table of Contents to ensure you have the left-hand frame in focus, and Click on Edit and Find (or Ctrl+F key combination).

Type out mysql and click on the Find Next button the Find dialog box twice to see the mysql module under the DBD section. Click on the Cancel button to close the find dialog box.

ActivePerl has added the instructions for DBD::myqsl into the documentation for you. Just click on the highlighted link to see the notes relating to this module.

Create SQL Script

We will start by creating a list of direct SQL instructions to pass to the MySQL database. This works using the MySQL tool, which does not require any Perl modules to be installed - indeed it does not use Perl at all.

To create a new SQL script file, we can create a new file by clicking on the arrow to the right of the button on the toolbar, and then Use New SQL Script menu option. Then using the File and Save As... menu options, pick the .sql file type, and type a new name - for example book.sql

Notice that a MySQL template is automatically created for you to start from. Delete this (e.g. using Ctrl+A and pressing the Delete key), and type out the following listing. Do not execute it yet:-

CREATE DATABASE books;

USE books;

CREATE TABLE book (
  isbn VARCHAR(14) NOT NULL,
  title VARCHAR(50) NOT NULL,
  author VARCHAR(50),
  publisher VARCHAR(30),
  year_published SMALLINT,
  description TEXT,
  price FLOAT NOT NULL DEFAULT 0.0,
  sold ENUM('Y','N') NOT NULL DEFAULT 'N',
  PRIMARY KEY(isbn),
  KEY (title,author),
  KEY (publisher,author,title),
  KEY (author,title)
);

INSERT INTO book
VALUES
  ('1-85224-204-3','Writing Poems','Sansom, Peter',
   'Bloodaxe Books',1994,
   'Introduction on how to write poetry better<br>Also recommended reading etc.',
   7.95,'N')
,
  ('1-57610-465-6','Perl Black Book','Holzner, Steven',
   'Coriolis Open Press',1999,
   'Indispensible problem solver on many intermediate-advanced Perl Programming topics',
   34.99,'N')
,
  ('0-13-576984-1','Automating Business Process Reengineering','Hansen, Gregory A.',
   'Prentice Hall PTR',1997,
   'Using automation simulation techniques to facilitate organizational change',
   29.95,'Y')
;

SELECT * FROM book;

Let's break this down into sections:-

CREATE DATABASE books;

This creates a new database called books. The semi-colon (;) on the end terminates the SQL instruction, which tells the server to execute the instruction.

A database is made up of a collection of related tables. Typically, you would create a new database for each new application, unless you create several applications that share the same data - and thus the same database.

It is possible to switch between several databases inside a single Perl application, so don't worry too much if you think you may need to do this at a later stage.

To switch to using a specified database, we need the use command as follows:-

USE books;

A table is made up of a number of related pieces of data. For example, in our book list table called books, a table is made up of an isbn number, title, author, publisher etc. These are all items of data that help to define what a book is.  To define a new table, and state  what sort of information is going into it, we use the following CREATE TABLE command:-

CREATE TABLE book (
  isbn VARCHAR(14) NOT NULL,
  title VARCHAR(50) NOT NULL,
  author VARCHAR(50),
  publisher VARCHAR(30),
  year_published SMALLINT,
  description TEXT,
  price FLOAT NOT NULL DEFAULT 0.0,
  sold ENUM('Y','N') NOT NULL DEFAULT 'N',
  PRIMARY KEY(isbn),
  KEY (title,author),
  KEY (publisher,author,title),
  KEY (author,title)
);

Each book is stored in a separate row which fills in all (or most) of the items (called columns) described in the CREATE TABLE command.

Each item has an associated type description to give an indication as to what sort of information that item (or column) may hold. For example, VARCHAR(n) refers to a character string (i.e. text) of variable-length up to a maximum of n characters.

A SMALLINT is a whole number in the range of -32768 to 32767.  A TEXT field is a text string that can be any length up to 65535 characters.  A FLOAT is a decimal number which is stored to a certain precision, but is not exact - it can store a number from -3.4 x 1038 to 3.4 x 1038.  An ENUM is a choice of values (in this case, it can be either Y if the book has been sold, or N if still in possession).  When a new row is created, the default value for this column is N - i.e. not sold.

Where a field state that it is NOT NULL this means that a value must be given for that field for the data to be stored. Otherwise, a field can have a value of NULL which means that no data is available. NULLs aren't very popular with programmers, because they can give you headaches when you wish to join two tables together to link-up information. There is always the "what does NULL mean anyway" debate that has inspired many-a-yawn for several decades.

You can also make access to data quicker from specific fields, and also order data quicker by creating one or several sets of keys called indexes (rather like the idea of a card-index which gives you quick access to a folder in a filing cabinet). These will typically identify the record uniquely - for example, it's no good having a key purely on an author, because the author may have written several books. So include the title as well as the author. In this example, we have made it easier / quicker to access data by creating indexes for the isbn field (which should be unique), the title and author (so if we search by title, then a list of authors should come up quickly) and also by a combination of publisher/author/title and by author/title only. You only really need to start thinking about using indexes if you are going to access the data very frequently, or if the number of books in your table starts getting large (e.g. tens of thousands or millions etc.)

The Primary Key is the key which is used by default to order your table and ensure that you can find rows in your table. The key MUST therefore be unique, and consequently any fields listed in the key must be defined as NOT NULL.

Okay, so that's our table. Once we have created it, we will want to populate (fill) it with data. We will ultimately wish to do this with a web form online, but we can put some initial data in for test purposes in SQL using the INSERT statement as follows:-

INSERT INTO book
VALUES
  ('1-85224-204-3','Writing Poems','Sansom, Peter',
   'Bloodaxe Books',1994,
   'Introduction on how to write poetry better<br>Also recommended reading etc.',
   7.95,'N')
,
  ('1-57610-465-6','Perl Black Book','Holzner, Steven',
   'CoriolisOpen Press',1999,
   'Indispensible problem solver on many intermediate-advanced Perl Programming topics',
   34.99,'N')
,
  ('0-13-576984-1','Automating Business Process Reengineering','Hansen, Gregory A.',
   'Prentice Hall PTR',1997,
   'Using automation simulation techniques to facilitate organizational change',
   29.95,'Y')
;

Note that the items of data must go in the same order that they were originally defined in the CREATE TABLE statement. Each set of data is enclosed with single parentheses - ( and ) - and each row of data is separated with a comma.

Finally, just to make sure everything has worked okay, we will list all the data in the table using the SELECT statement:-

SELECT * FROM book;

The * means "list all fields" and the FROM book tells us to take the data from the book table. We have not specified any limiting criteria using the WHERE clause, so everything in the table will be shown.

Execute SQL Script

Okay, so assuming you have installed MySQL in the default location on your computer (or otherwise set up its location), and have typed out the script and saved it to the .sql type file, you should now be able to execute the script.

Assuming you have the default MySQL setting that checks to see whether the MySQL server is up (details in the reference section), when you execute the script (e.g. by clicking on the Text Output window or clicking on the button) PerlWiz will check to see if the MySQL database server is up and running.

If you get an error message saying "Can't create TCP/IP connection" then just try executing again to try the connection between PerlWiz and MySQL again.

If it is not, PerlWiz will ask MySQL Server Down. Attempt to bring it up?  Click on the Yes button to allow PerlWiz to do this. There will be a short delay while the server is being brought up. Re-run the script again (e.g. by right-clicking on the Text Output window or clicking on the button), and you should see the results on the screen, which should look something like this:-

--------------
CREATE DATABASE books
--------------

--------------
CREATE TABLE book (

isbn VARCHAR(14) NOT NULL,

title VARCHAR(50) NOT NULL,

author VARCHAR(50),

publisher VARCHAR(30),

year_published SMALLINT,

description TEXT,

price FLOAT NOT NULL DEFAULT 0.0,

sold ENUM('Y','N') NOT NULL DEFAULT 'N',

PRIMARY KEY(isbn),

KEY (title,author),

KEY (publisher,author,title),

KEY (author,title)

)
--------------

--------------
INSERT INTO book

VALUES

('1-85224-204-3','Writing Poems','Sansom, Peter',

'Bloodaxe Books',1994,

'Introduction on how to write poetry better<br>Also recommended reading etc.',

7.95,'N')

,

('1-57610-465-6','Perl Black Book','Holzner, Steven',

'CoriolisOpen Press',1999,

'Indispensible problem solver on many intermediate-advanced Perl Programming topics',

34.99,'N')

,

('0-13-576984-1','Automating Business Process Reengineering','Hansen, Gregory A.',

'Prentice Hall PTR',1997,

'Using automation simulation techniques to facilitate organizational change',

29.95,'Y')
--------------

--------------
SELECT * FROM book
--------------

isbn title author publisher year_published description price sold
1-85224-204-3 Writing Poems Sansom, Peter Bloodaxe Books 1994 Introduction on how to write poetry better<br>Also recommended reading etc. 7.95 N
1-57610-465-6 Perl Black Book Holzner, Steven CoriolisOpen Press 1999 Indispensible problem solver on many intermediate-advanced Perl Programming topics 34.99 N
0-13-576984-1 Automating Business Process Reengineering Hansen, Gregory A. Prentice Hall PTR 1997 Using automation simulation techniques to facilitate organizational change 29.95 Y

You can see each of the commands being executed, and the final command gives some results. This is a little difficult to read, so you could try executing the script again to the browser using the button or by clicking on the Browser Output tab.

Executing a single line

You may be surprised by the result.  You will also get an error message at the bottom of the screen in the error box reading "Can't create database 'books'. Database exists."

By trying to execute the script again, we start from the top again.... and the first statement is to create the database books, which we have already created.

So how do we execute just a single statement? Simple. Just highlight the SQL command, or commands in the Source tab that you want to execute, and push the button again to execute the script. Only the highlighted SQL will now be executed.

So highlight the single SELECT line at the bottom of the script and try this out.

Oops! Not what we expected? We get an error message in the error message box saying No database selected.

When we execute anything, we need to let MySQL know which database we are working with.

We could copy and paste the USE books; command just before the SELECT line and highlight them both so that they are executed together. That would work fine.

However, this can be tiresome if you are trying out different bits of a script.

The solution is to utilise a special feature of PerlWiz, where you identify a block of lines in the script that will be executed first, every time you select a specific portion of SQL to execute. So, if we put the USE books; command inside this block, it will be executed before any of the highlighted instructions are executed, so we know we will have the books database selected automatically.

Specify the section as follows:-

#PERLWIZ-HEADER-START
USE books;
#PERLWIZ-HEADER-END

The lines highlighted in bold show where the beginning and end of the block are, and must be typed exactly as seen here, or they will not be identified by PerlWiz.

Note that your script would still execute from another SQL application, as headers are prefixed with a comment - # - character, and so would be ignored by any other application.

If you want to prove to yourself that duplicate entries are not allowed, try highlighting the entire INSERT statement (all the way to the terminating ; character) and clicking on the button. You will get an error message indicating that there is a Duplicate Entry. Highlight the SELECT statement again and execute, and you will see that only the original three entries are in the table.

SELECT browser output

Highlight the SELECT statement once more, and try executing using the button again. All should be well now! The USE books; command will be executed first, followed by the highlighted SELECT statement.

If you get a "Can't Create TCP/IP Socket." error, try clicking the again once or twice.

Notice that in the HTML view, the select results will be displayed in a table format (with the same background colour as the SQL editor window), which is easier to read than the Text Output view. If you maximize the PerlWiz window, the table will automatically resize, and be even more readable.

Search form - HTML document

Now that we have created our data structure, and populated it with some test data, let's start by creating a form in HTML so that we may search using a few useful criteria - e.g. author, title, Sold/Unsold and price under a certain amount.

Create a new HTML file by clicking on the arrow to the right of the button and selecting the New HTML Document menu option. This gives us a basic template for a HTML document to start typing into.

Then choose the File | Save As... menu, and save the file under a reasonable name - e.g. search_form.html

The <title> section should be highlighted automatically, so you can just start typing to replace the  default text. Fill between <title> and </title> with MySQL Book Database Example and between the heading (<h1> and </h1>) tags with MySQL Book Database Example too.

The rest of the body can be filled in with the form required for searching. I used the form_ol code template (pressing F6 to accept it) to create the basic form outline, replacing the rest of the non-heading body text.

I will call the Perl module that accesses the database book.cgi

The table was created by using the table2 template and indenting the table by highlighting it and using the button on the toolbar.

The whole HTML document should look something like this:-

<html>

<head>
  <title>MySQL Book Database Example</title>
</head>

<body>
  <h1>MySQL Book Database Example</h1>
  <FORM ACTION="cgi-bin/book.cgi" METHOD="GET">
  
  <table border="3" width="100%" bgcolor="#D5D5FF" bordercolor="blue" cellspacing="0">
    <tr>
      <td width="30%" align="right"><b>Author:</b></td>
      <td width="70%"><INPUT TYPE="text" NAME="author" SIZE=40></td>
    </tr>
    <tr>
      <td width="30%" align="right"><b>Title:</b></td>
      <td width="70%"><INPUT TYPE="text" NAME="title" SIZE=40></td>
    </tr>
    <tr>
      <td width="30%" align="right">&nbsp;</td>
      <td width="70%">  <INPUT TYPE="checkbox" NAME="sold" VALUE="Y" CHECKED> Sold?</td>
    </tr>
    <tr>
      <td width="30%" align="right"><b>Price less than</b></td>
      <td width="70%"><INPUT TYPE="text" NAME="max_price" SIZE=10></td>
    </tr>
  </table>
  <br>
  <hr size="2" color="blue">
  <center>
    <INPUT TYPE="submit" VALUE="Search">
    <INPUT TYPE="reset" VALUE="Clear Criteria">
  </center>
  <hr size="2" color="blue">
  </FORM>
</body>

</html>

You may copy and paste this if you would like to save typing!

Make sure you save the file before you continue.

If you try to submit this form, it will create an error for two reasons:-

Read form input using Perl

So we really need to create our Perl program to accept the data from our Search Form. Create a new file by clicking on the right-hand arrow next to the button on the toolbar and selecting the New Perl Program option, choose the File | Save As... menu, and save the file under a reasonable name - e.g. book.cgi if you used this filename in the HTML FORM ACTION value as described above.

The following program will do the job for us. We'll take it apart in sections to see how it works...

#!/usr/bin/perl

use warnings;
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use DBI;

# Connection to CGI and Database
$q = new CGI;
$dbh = DBI->connect('dbi:mysql:database=books','','',{RaiseError=>1});

# Retrieve variables from form via GET or POST
$author = $q->param('author');
$title = $q->param('title');
$sold = $q->param('sold');
if(!defined($sold)) { $sold = "N" };
$max_price = $q->param('max_price');
if(defined($max_price) && ($max_price !~ /^\d+(\.\d+)?$/)) {
  $max_price = "";
}

# Build SQL statement from search script
$sql = "select * from book";
$where = ""; # where clause of SQL
%params; # associative array of parameters used
if ($author) {
  $params{'Author'} = $author;
  $where = " author like '%$author%'";
}
if ($title) {
  $params{'Title'} = $title;
  if ($where) { $where .= " and"; }
  $where .= " title like '%$title%'";
}
if ($sold) {
  $params{'Sold?'} = ($sold eq "Y") ? "Yes" : "No";
  if ($where) { $where .= " and"; }
  $where .= " sold = '$sold'";
}
if ($max_price) {
  $params{'Maximum Price'} = $max_price;
  if ($where) { $where .= " and"; }
  $where .= " price <= $max_price";
}

# Combine where clause with SQL, prepare and execute it
if ($where) { $sql .= " where $where"; }
$sth = $dbh->prepare($sql);
$sth->execute();

# show document
print "Content-Type: text/html\n\n";
print "<html><body>";

# List valid parameters in a light-green table
print "<table width=300 bgcolor=#CCFFCC>\n";
foreach $item (keys %params) {
  print "<tr><td width=50% align=right><b>$item:</b></td>";
  print "<td width=50%>$params{$item}</td></tr>\n";
}
print "</table>";

# List search results in a light-blue table
print "<table bgcolor=#D5D5FF>";
# First row shows column headings in bold
print "<tr>";
print "<td><b>ISBN</b></td>";
print "<td><b>Title</b></td>";
print "<td><b>Author</b></td>";
print "<td><b>Publisher</b></td>";
print "<td><b>Year</b></td>";
print "<td><b>Description</b></td>";
print "<td><b>Price</b></td>";
print "<td><b>Sold</b></td>";
print "</tr>";

# Fetch each row in results set into an array of fields called @row
@row;
while (@row = $sth->fetchrow_array()) {
  # New table row <tr> for each row from database
  print "<tr>\n";
  # New <td> element in table row for each field in @row array
  foreach $td (@row) { print "<td>$td</td>\n"; }
  print "</tr>\n";
}
print "</table>";
print "</body></html>";

# Finish results array fetch and database connection
$sth->finish();
$dbh->disconnect if $dbh;

Using standard modules

The first section describes the modules that will be used within the program:-

use warnings;
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use DBI;

The CGI::Carp module is used to provide meaningful browser output in case of an error.

The CGI module contains classes relating to web (particularly HTTP) input and output - for example, reading form parameters.

The DBI module is the generic database access module which is designed to work with any number of installed DBD drivers so that you can access any type of database using the same interface. We will be using it to access a MySQL database, as we installed the DBD::MySQL "driver" for the DBI module.

Connecting to the database and CGI interface

# Connection to CGI and Database
$q = new CGI;
$dbh = DBI->connect('dbi:mysql:database=books','','',{RaiseError=>1});

The next line sets up a new object to access the CGI class. This object will be called $q.

We then use the DBI module / class to connect to our database using the connect subroutine or method. This takes as parameters the database to connect to (specified by the fact that we are using a DBI driver to access the database, and then the name of the DBI driver (MySQL) and then the name of the database that we are connecting to (equivalent to the MySQL use books; command).

The following two parameters are left blank, but would contain the user name and password required to connect to the database. In our sample database, we have not set up a user name and password, so we have left these blank.

The {RaiseError=>1} parameter specifies a hash element called RaiseError that represents a flag to turn on the raising of errors as they occur (which will then be intercepted by the Carp module and sent to the browser automatically.

The result of this, if the process works okay, is a database handle stored in the $dbh variable. This is an object, and we can use this to perform all of our future actions on the database we have now connected to.

This takes a lot of the hard work out of checking for errors every time we use a DBI subroutine in our program.

Retrieving form data

# Retrieve variables from form via GET or POST
$author = $q->param('author');
$title = $q->param('title');
$sold = $q->param('sold');

if(!defined($sold)) { $sold = "N" };
$max_price = $q->param('max_price');

The next lines retrieve the parameters sent from our HTML form, which are passed via environment variables (and STDIN input in the case of a POST) to the CGI program.

Note that this uses the param method from the $q object (which is an instance of the CGI class). Thus, we are using the CGI class to read a form parameter, and placing the result in four variables - $author, $title, $sold and $max_price - the four possible inputs from our form. Note that the variable will be undefined if the form parameter was not passed (i.e. was not filled-in on the form). This particular method will successfully read either GET or POST data - it works out which has been used, so takes a lot of the work out of reading form data.

The if statement checks to see if $sold brought back a value. If it did not, then the check box must have been unchecked, to indicate that the book must not be sold. Therefore, to check the specific value for a book not being sold - i.e. N - the $sold variable is set to "N" instead of undefined.

Validating the Maximum Price using pattern matching

if(defined($max_price) && ($max_price !~ /^\d+(\.\d+)?$/)) {
  $max_price = "";
}

We could have performed some processing using JavaScript in our HTML form in order to check that the price was in the correct format.

In this case, we have opted to check the format of the price, and if it is not correct, to set it to being blank.

So, we check to see the the $max_price variable has been defined (i.e. whether the form field was completed and sent to the web server at all), and (&&) if this is the case, then the next part of the condition will be parsed.

This part  of the condition is a check utilising the pattern-matching facilities of Perl. The !~ operator means that the price must not match the pattern given (which is the pattern for a correct decimal number). If this does not match, then the price will be ignored by setting it to be blank.

The pattern itself is given between the two forward slashes. The ^ at the beginning means that the pattern must match from the beginning, and the $ at the end means that the pattern must match to the end. The combination implies that the pattern must match exactly, and can't just be somewhere within the string.

Using \d represents a single numeric digit from 0 to 9, and the + means that this must be present at least 1 or more times (so 1 or 55 or 99 is fine, but missing out a digit here or putting a letter would fail the matching).

Next, we have grouped together a single period (escaped with a backslash \ as a period has a different meaning within a pattern) and one or more digits.  This represents the decimal part of a number. Note that after the grouping (in brackets) appears a single question mark ? - this means that the combination of a single period and any number of digits can occur 0 or 1 times. I.e you can omit the decimal part and have a whole number here if you like.

Note that the above example follows the style of typical programming languages. In Perl, you have the option of suffixing the if statement onto the end of the command to conditionally execute it, which looks a little neater with a single statement:-

$max_price = "" if(defined($max_price) && ($max_price !~ /^\d+(\.\d+)?$/));

Building up an SQL select query

An SQL select statement is used to retrieve data from a database. The exact form of the statement will vary according to which form fields have been filled in, so we will build up our SQL query as a string inside a variable before we pass it to MySQL to process.

# Build SQL statement from search script
$sql = "select * from book";
$where = ""; # where clause of SQL
%params; # associative array of parameters used

The first step is to select all fields (*) from the appropriate table - in this case the book table we created earlier. This is placed in the $sql command.

Now we need to limit the rows that we bring in from the table using a where clause. This specifies a number of conditions that must be met in order to read in rows. We will start of with the variable representing this clause ($where) being empty.

We will build up a list of the parameters that we have actually used in the where clause and their values in an associative array called %params.  We will be using this to list the parameters that make up the results on our web page as confirmation of what we are looking for is what we expect.

if ($author) {
  $params{'Author'} = $author;
  $where = " author like '%$author%'";
}

The if checks to see if the $author variable has been populated (if it has then it will return a non-zero and defined value which means that the result will be true).

If it has, we set the Author element of the %params associative array to be the form field data passed in to the program.  We also set the $where clause to compare the contents of the database field author on every row with the contents of the $author variable. In SQL, a string is enclosed with single-quotes.  The % symbols are wildcards that means "anything can go here" - so the author on every row must have a string that starts with anything, has the author entered as a form field, and end with anything - i.e. contain the author we have asked for. This means we could enter either the author, or a partial author name, and still get a result.

Note that the like operator is case-insensitive, so upper case, lower case, or a mixure of both will all be matched successfully.

For example, if $author contained son then a table might return all rows which contain  names of Sonny, Clarkson, and PARSONS quite happily.

if ($title) {
  $params{'Title'} = $title;
  if ($where) { $where .= " and"; }
  $where .= " title like '%$title%'";
}

The next form field to be evaluated is title.  In this case, we may (if the author was also used as part of the search) need to separate the author condition from the title condition using the and keyword to show that both must be true in order for the row to be retrieved.

Thus, if $where contains any text, we need to add on the text " and " - using the string concatenation operation .= in this case.

We can then add on to the end of the where clause a condition for the $title similar to the author.

if ($sold) {
  $params{'Sold?'} = ($sold eq "Y") ? "Yes" : "No";
  if ($where) { $where .= " and"; }
  $where .= " sold = '$sold'";
}

It's a similar story for the Sold form field, except that we interpret Y (as returned by the form field) as the word Yes and anything else as the word No. Note that we use the string comparison operator to test this (eq) rather than the numeric comparison operator (=). This is necessary as Perl does not by default distinguish between variable types. The ? operator evaluates the condition on its left, and if it is true, returns the value after the ? symbol and if it is value, returns the value after the : symbol.

In reality, the No would never be picked up, as if the checkbox was unchecked, the value would be blank, and thus the form field sold would never be sent to the web server, so would not be picked up by the program.

if ($max_price) {
  $params{'Maximum Price'} = $max_price;
  if ($where) { $where .= " and"; }
  $where .= " price <= $max_price";
}

The $max_price variable is then tested as before. The only difference is that we do not need to use single-quotes in our where clauses, as the price field on the table is numeric. The <= operator specifies that the price on the database row must be less than or equal to the amount we specified in our form field in order for the row to be pulled back from the table.

Executing the SQL Query

The next stage is to put $sql and $where clauses together to form a single SQL instruction, and then pass this to the MySQL database server to execute and return the results:-

# Combine where clause with SQL, prepare and execute it
if ($where) { $sql .= " where $where"; }
$sth = $dbh->prepare($sql);
$sth->execute();

The first line will, if there is anything in the $where clause, put it onto the end of the $sql statement, with the where keyword starting the where clause.

A new statement handle is then prepared (for efficiencies sake - you may have to execute an SQL statement several times in the course of a program). We do this by taking the database handle ($dbh) we created earlier, and using its prepare method to prepare the SQL we have created for execution on the server. The result is a statement handle stored in the $sth variable.

We then use this statement handle variable to execute the statement on the server using the $execute statement.  Note that if we wanted to do this again in a program, we could do so without having to pass the SQL into the statement handle again, allowing us to re-execute the query as many times as we like. However, in our program, we only need to execute it once.

Creating the web page

In order to create the web page output from the retrieval of the data from the database, we need to output a properly structured HTML document to STDOUT - using the print statement is fine.

# show document
print "Content-Type: text/html\n\n";
print "<html><body>";

The Content-Type line is required to tell the web server that this is a text html document that is being output.

So, the start of our document will be <html><body> (we're omitting the title section in this case for clarity).

# List valid parameters in a light-green table
print "<table width=300 bgcolor=#CCFFCC>\n";
foreach $item (keys %params) {
  print "<tr><td width=50% align=right><b>$item:</b></td>";
  print "<td width=50%>$params{$item}</td></tr>\n";
}
print "</table>";

The next section involves setting up a table 300 pixels wide with a light-green background.

We then work out way through the keys (names) in the %params associative array using a foreach loop, and pick out each of the names into the $item variable. For each of these, we Display a table row <tr> and in the first column <td> we put the parameter item name, and in the second <td> column we put the value associated with that parameter that was passed in from the form fields - $params{$item}. This is closed with a </table> tag to show the end of the table.

# List search results in a light-blue table
print "<table bgcolor=#D5D5FF>";
# First row shows column headings in bold
print "<tr>";
print "<td><b>ISBN</b></td>";
print "<td><b>Title</b></td>";
print "<td><b>Author</b></td>";
print "<td><b>Publisher</b></td>";
print "<td><b>Year</b></td>";
print "<td><b>Description</b></td>";
print "<td><b>Price</b></td>";
print "<td><b>Sold</b></td>";
print "</tr>";

The next part starts a new table coloured light-blue, whose first row shows the column headings in bold for each row that is to be retrieved from the database.

# Fetch each row in results set into an array of fields called @row
@row;
while (@row = $sth->fetchrow_array()) {
  # New table row <tr> for each row from database
  print "<tr>\n";
  # New <td> element in table row for each field in @row array
  foreach $td (@row) { print "<td>$td</td>\n"; }
  print "</tr>\n";
}
print "</table>";

We now define an array for each row that is retrieved from the database called @row - this will hold all of the columns within each row.

Then, we loop around and store in the @row array the results of a call to the statement header variable to fetch a row as an array from the database. If this returns an undefined value into the @row variable, then the while condition will fail, and the loop will terminate.

A new table row is started using the <tr> tag, and then for every item (column) in the array, the value is returned in the $td variable, and displayed enclosed in a <td> table column tag. The row is then terminated, and when the while statement has terminated (no more rows to read), the table is terminated.

We can finish off the HTML document by closing the body and html tags.

print "</body></html>";

Closing the statement and database connections

When you have finished with a query or other statement, remember to finish the statement handler. This makes the $sth variable undefined.

Also, when you have finished with a connection to the database, use the disconnect method on the database handler variable. By putting if $dbh on the end, we only disconnect if the database handler is actually connected to the database (i.e. is defined).

# Finish results array fetch and database connection
$sth->finish();
$dbh->disconnect if $dbh;

... And that's our script completed!

Testing the script using form fields

You can test the script using pre-defined form fields.

First, ensure the PerlWiz web server is switched off. If you hover your mouse pointer over the button on the toolbar, a tool-tip will display to say whether the web server is ENABLED or DISABLED. If it is ENABLED, click on the button to make it DISABLED.

Next, click on the Params tab for the book.cgi program within PerlWiz, and set the Form Send Type to be Post.

Add four form fields and uncheck everything other than one of the fields so that only one of the fields will be sent through to the Perl program. The example here shows the title parameter checked with a value of poems. Only this value will be sent.

Now try to execute the program by clicking on the Browser Output tab or clicking the button on the toolbar or use the Shift+F9 key combination.

You may get an error message as follows:-

Software error:

DBI connect('database=books','',...) failed: Can't connect to MySQL server on 'localhost' (10061) at C:\Projects\PerlWiz\projects\book.cgi line 10

For help, please send mail to this site's webmaster, giving this error message and the time and date of the error.

If you have checked the MySQL option to automatically detect this, then you will get a dialog box appearing stating: MySQL Server down. Attempt to bring it up?   Click on the Yes button to start the MySQL server running. You should no longer get the error message until you next reboot your PC. You may have configured MySQL to automatically bring the server up when you start up your PC, in which case you should never get this error message.

If you do not have automatic detection switched on, click on the properties toolbar button, select the Language heading, the SQL sub-heading, and the MySQL Tools sub-heading, and click on the Check if Server Up button. A dialog box saying MySQL server down. Attempt to bring it up? should display. Click on Yes to confirm this.

If the screen goes blank, showing [running] in the title bar for too long, you can always try again by clicking on the button in the toolbar and clicking on the button again. It may take a little while for the results to come back on your PC.

Finally, you should get browser output that looks something like this:-

You can re-try this using a combination of form fields by checking and unchecking different form fields in the Params tab, and try the GET method of form field delivery to ensure the script works for this too.

Finally, why not try turning the web server back on, and execute the HTML form (search_form.html) itself, and manually type in parameters (remember that the SOLD check box is checked by default). Then click on the Search button and see if the parameters are passed correctly to the form field. Another test!

Note that you may need to click the Search button a few times due to the way that Internet Explorer handles caching. You may also need to re-execute the HTML form a few times or right-click and choose the Refresh option if you run into difficulties. Generally, it should work fine though.

So there's your first web form linked to a Perl program that searches a database to get back results based on the form.

For more information on how to update records in a database, look in the DBD::MySQL documentation as described earlier via the ActiveState help.

 

(c) Copyright 2003-4 Arctan Computer Ventures Ltd.   All Rights Reserved.
If you have any issues regarding this on-line help, please contact the author by clicking here.
Alternatively, you can leave a voice message on +44(0)7050-618-297 or fax on +44(0)7050-618-298

This Page was last updated: 27 June 2004 05:56