Training, Open Source Computer Languages

This is page http://www.wellho.net/solutions/perl-acc ... d-cgi.html

Our email: info@wellho.net • Phone: 01225 708225

 
Python and Tcl - public course schedule [here]
Private courses on your site - see [here]
Please ask about maintenance training for Perl, PHP, Lua, etc
 
Accessing a MySQL database via a browser, Perl and CGI

Making a database available via a browser

Here's a "putting all the technology together" demonstration ... showing the elements of an application which allows data to be held in database tables and presented and updated via a browser. Different technologies could be used, but on this occasion I have used:
- MySQL for the database
- Perl for the application program
- CGI to interface it to the web server
- An Apache httpd web server under Linux
- And (this is important) it should work with ANY browser.

Link - demo
Link - pictures and management summary

THE INITIAL DATA

The example was pot together as a demonstration for a clinical history application ... for which you'll probably start off with some already-gathered (or sample) data like this:

Patients:

John Smith SN 1958 1 M
Janet Patel SN 1943 2 F
Mike Mulholland OX 1955 3 M
Fred Dibnah BB 1928 4 M
Doris Jones OX 1937 5 F

Operations:

1 1 Transplant 2002
2 2 Appendix Removal 1982
3 2 Brain Transplant 2015
4 2 Remove Wisdom Teeth 1985
5 5 Transplant 2003
6 5 Laparoscopy 2007
7 5 Laparoscopy 2008
8 4 Remove Wisdom Teeth 2001
9 6 Transplant 2006

DESIGNING AND TESTING YOUR TABLES

We're going to hold the data in a MySQL database - typically, my advise would be to see if your organsation already has MySQL running and the admin there can add an account there so that you don't need to set up and administer your own daemon, but if you *do* need your own setup, you'll need to know some:
Linux Basics
Elements of Linux Admin
and of course
MySQL

For initialisation and testing purposes (and to help check that you have the right database design before you go too far down the road), it's a good idea to place your initial MySQL commands into a file that can be run and re-run to set up (and reset) the data on the server ... and here is my examples of this file:

drop table if exists patients;
drop table if exists operations;
create table patients (
        pid int primary key not NULL auto_increment,
        name text,
        area text,
        born year,
        gender text);
create table operations (
        oid int primary key not NULL auto_increment,
        pid int,
        optype text,
        opyear year);
load data infile "/home/wellho/live_cgi-bin/demo/patients"
        into table patients
        fields terminated by '\t'
        (name, area, born, pid, gender);
load data infile "/home/wellho/live_cgi-bin/demo/operations"
        into table operations
        fields terminated by '\t'
        (oid,pid,optype,opyear);

And once that data is loaded, you'll want to test it through a number of queries - here's such a query . I have again stored this in a file so that it can be re-tested many times over, and cut and pasted into a Perl application in a later stage so that I can use it as a query template.

select patients.name, patients.area, patients.born, operations.optype,
        operations.opyear, operations.opyear - patients.born as age_at_op
        from patients join operations on patients.pid = operations.pid
        order by opyear;

Here is the result I expect when I run that through the MySQL client program:

mysql> source testtabs;
--------------+------+------+---------------------+--------+-----------+
| name | area | born | optype | opyear | age_at_op |
--------------+------+------+---------------------+--------+-----------+
| Janet Patel | SN | 1943 | Appendix Removal | 1982 | 39 |
| Janet Patel | SN | 1943 | Remove Wisdom Teeth | 1985 | 42 |
| Fred Dibnah | BB | 1928 | Remove Wisdom Teeth | 2001 | 73 |
| John Smith | SN | 1958 | Transplant | 2002 | 44 |
| Doris Jones | OX | 1937 | Transplant | 2003 | 66 |
| Doris Jones | OX | 1937 | Laparoscopy | 2007 | 70 |
| Doris Jones | OX | 1937 | Laparoscopy | 2008 | 71 |
| Janet Patel | SN | 1943 | Brain Transplant | 2015 | 72 |
--------------+------+------+---------------------+--------+-----------+
8 rows in set (0.01 sec)

ACCESSING YOUR DATA THROUGH A PROGRAM

So far so good, but that's only got the data storage element worked out and there's no way that regular users will want this basic approach - let's look at that through a Perl program, for which you'll need the BDI and DBD modules too.

Here's the source of the Perl program:

#!/usr/bin/perl

use DBI;

$db_handle = DBI -> connect("DBI:mysql:wellho:127.0.0.1","wellho","abc123");

$getkey = $db_handle -> prepare("select
        patients.name, patients.area, patients.born, operations.optype,
        operations.opyear, operations.opyear - patients.born as age_at_op
        from patients join operations on patients.pid = operations.pid
        order by opyear");

$getkey -> execute ;

while (@row = $getkey->fetchrow) {
        printf ("%-15s %3s %4d %-20s %4d %4d\n",@row);
        }

That mey look short (Perl programs often are!) but there's a lot going on behind the scenes and you'll need to know:
Perl Programming
OO Perl / databse interfacing
to understand all that's going on here.

Results:

-bash-3.2$ perl gem
Janet Patel SN 1943 Appendix Removal 1982 39
Janet Patel SN 1943 Remove Wisdom Teeth 1985 42
Fred Dibnah BB 1928 Remove Wisdom Teeth 2001 73
John Smith SN 1958 Transplant 2002 44
Doris Jones OX 1937 Transplant 2003 66
Doris Jones OX 1937 Laparoscopy 2007 70
Doris Jones OX 1937 Laparoscopy 2008 71
Janet Patel SN 1943 Brain Transplant 2015 72
-bash-3.2$

The significance of this? It's now available to any user to whom you give the program, and not just those people who understand MySQL.

PROVIDING A WEB FRONT END

Going a stage further, you want to make your Perl program visibile to users of a web site / available via their browser, and for this you need to run (or, better in the first instance, to share someone else's) web server.

The (Perl) program runs on an (Apache httpd) web server ... NOT on the individual user's browser. Once again, the code can be quite short:

#!/usr/bin/perl

use DBI;

# Report on the tables about persons and operations
print "content-type: text/html\n\n";
print <<"HEADER";
<html>
<head><title>Patient Record Demonstration</title></head>
<body bgcolor=#FFFFDD text=#228822>
<h1>Patient Report from joined tables</h1>
All about this stuff ...<br>
<pre>
HEADER

# Run the database query
$db_handle = DBI -> connect("DBI:mysql:wellho:127.0.0.1","wellho","abc123");
$getkey = $db_handle -> prepare("select patients.name,
        patients.area, patients.born, operations.optype, operations.opyear,
        operations.opyear - patients.born as age_at_op
        from patients join operations on patients.pid = operations.pid order by opyear");
$getkey -> execute ;

# Get back and output the results
while (@row = $getkey->fetchrow) {
        printf ("%-25s %3s %4d %-20s %4d %4d\n",@row);
        }

# Links to other parts of the application
print <<"FOOTER";
</pre>
<hr>
Copyright, contacts details, etc
</body></html>
FOOTER

But there are a lot of technologies to learn in order to get it right and provide an environment in which to place it correctly:
Configuring and running a web server
HTML basics
HTML forms
Using Perl on the Web

INTERACTION

The final elements in this initial demonstration provide the user with the abiity to interact with the data - this will be both in terms of updating the information / adding to it, and in terms of selecting different reports and analyses. The example we have chosen to complete this initial compornant walkthrough is for inserting new data, but this is just the first of (perhaps) half a dozen elements that will be combined into your complete system.

Here's one of our two form / addition demonstrations, which adds an operation to an existing patient:

#!/usr/bin/perl

use DBI;

# Make up a pulldown menu of all known patients
$db_handle = DBI -> connect("DBI:mysql:wellho:127.0.0.1","wellho","abc123");
$query = "select pid,name from patients";
$qh = $db_handle->prepare($query);
$qh->execute;
while (@row = $qh->fetchrow) {
        $hh .= "<option value=$row[0]>$row[1]</option>\n";
        }

# Send out the header and form
print "content-type: text/html\n\n";
print <<"HEADER";
<html>
<head><title>Add a procedure</title></head>
<body bgcolor=#FFFFDD text=#228822>
<h1>Please enter the operation details</h1>
<form method=POST>
Name <select name=pid>$hh</select><br>
Operation Type <input name=optype><br>
Year Performed <input name=opyear><br>
<input type=submit name=go value=go></form><hr>
HEADER

# Read information from the form
read(STDIN,$buffer,$ENV{CONTENT_LENGTH});
@pairs = split(/&/,$buffer);
foreach (@pairs) {
        ($n,$v) = split(/=/);
        $v =~ tr/+/ / ;
        $v =~ s/%(..)/pack("C",hex($1))/ge;
        $input{$n} = $v;
        }

# If the form was properly submitted, save the data
if ($input{"go"} eq "go") {
$query = "insert into operations (pid,optype,opyear) values (".
                "$input{pid},".
                " \"$input{optype}\",".
                " $input{opyear})";
$db_handle -> do($query);
$action = "Record saved - $query";

# If the form has not been submitted, ask for data
} else {
$action = "Please complete form";
}

# Standard links to the rest of the application
print <<"FOOTER";
<b>$action</b>
<hr>
Jump to - <a href=/cgi-bin/demo/gempage>view data</a><br>
Jump to - <a href=/cgi-bin/demo/addpatient>Add Patient</a><br>
Jump to - <a href=/cgi-bin/demo/addoperation>Add Operation</a><br>
<hr>
Copyright, contacts details, etc
</body></html>
FOOTER

FOLLOWING STAGES

Once you've understood and got the elements above into place, you'll find there's a very great deal more you can add on - some of it easily. You'll certainly want to pause and learn from the design. You'll want to consider, usability, maintainabiity, ease of update, security and much more.

This is also a very good time to note that the technology chosen for this particular demonstration was based on a particular customer's requirements - alternative Open Source technologies such as PHP or Ruby (and Rails) or Python and Zope/Plone or Django could be applied. And of course there's a Microsoft route too if you want to pay for your tools.



See also Perl on the Web - course

Please note that articles in this section of our web site were current and correct to the best of our ability when published, but by the nature of our business may go out of date quite quickly. The quoting of a price, contract term or any other information in this area of our website is NOT an offer to supply now on those terms - please check back via our main web site

Related Material

SQL databases from a browser via Perl and CGI
  [1885] Hiding a MySQL database behind a web page - (2008-11-15)

Perl - Extending Flexibility Using CGI
  [1365] Korn Shell scripts on the web - (2007-09-25)
  [1187] Updating a page strictly every minute (PHP, Perl) - (2007-05-14)
  [641] Simple but rugged form handling demo - (2006-03-10)
  [590] Danny and Donna are getting married - (2006-02-03)
  [426] Robust checking of data entered by users - (2005-08-27)

Interfacing Applications to MySQL Databases
  [4436] Accessing a MySQL database from Python with mysql.connector - (2015-02-21)
  [3455] MySQL, MySQLi, PDO or something else - how best to talk to databases from PHP - (2011-09-24)
  [3447] Needle in a haystack - finding the web server overload - (2011-09-18)
  [3099] Perl - database access - DBD, DBI and DBIx modules - (2010-12-22)
  [3035] How to display information from a database within a web page - (2010-11-07)
  [2790] Joining a MySQL table from within a Python program - (2010-06-02)
  [2745] Connecting Python to sqlite and MySQL databases - (2010-04-28)
  [2381] Checking the database connection manually - (2009-08-28)
  [2263] Mysqldump fails as a cron job - a work around - (2009-06-30)
  [1885] Hiding a MySQL database behind a web page - (2008-11-15)
  [1561] Uploading to a MySQL database through PHP - examples and common questions - (2008-03-02)
  [1518] Downloading data for use in Excel (from PHP / MySQL) - (2008-01-25)
  [1450] Easy selection of multiple SQL conditions from PHP - (2007-11-30)
  [1381] Using a MySQL database to control mod_rewrite via PHP - (2007-10-06)
  [723] Viewing images held in a MySQL database via PHP - (2006-05-17)
  [663] Python to MySQL - (2006-03-31)
  [644] Using a MySQL database from Perl - (2006-03-13)
  [104] mysql_connect or mysql_pconnect in PHP? - (2004-10-30)

resource index - Perl
Solutions centre home page

You'll find shorter technical items at The Horse's Mouth and delegate's questions answered at the Opentalk forum.

At Well House Consultants, we provide training courses on subjects such as Ruby, Lua, Perl, Python, Linux, C, C++, Tcl/Tk, Tomcat, PHP and MySQL. We're asked (and answer) many questions, and answers to those which are of general interest are published in this area of our site.

© WELL HOUSE CONSULTANTS LTD., 2019: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01225 708225 • FAX: 01225 793803 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/solutions/perl-acc ... d-cgi.html • PAGE BUILT: Wed Mar 28 07:47:11 2012 • BUILD SYSTEM: wizard