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