A friend of mine was asking me for my recommendation of a good desktop database program to use to keep track of his inventory of cargo containers. I suggested to him that he should use MySQL and write a web page interface to do everything that he needed. He then reminded me that he is a lawyer by trade, and that he doesn’t have any computer programming experience. Then I remembered that he has almost zero computer skills. And his Texas Hold-Em skills are even worse, but I don’t mind taking his money. In his case, he should just use a notepad and a pencil. (As for the question – what is a lawyer doing with cargo containers? – that is a different story.)
If he did decide to broaden his horizons a bit, he could easily write his own software web application for creating and storing almost any kind of data. In this post, I will show you how to create a MySQL database and then the web pages needed to create new addresses, edit the same data and delete the data as well.
Of course, you will need to download and install MySQL. There are a ton of resources on the web for doing this, so let’s assume that you have already this part completed – and that you know how to use MySQL. First, let’s create a MySQL table. In this example, we will create a simple address book, and populate it with a few fake names. Here is the SQL, complete with the fake data to be inserted:
SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE `address` (
`serial` int(4) NOT NULL AUTO_INCREMENT,
`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(2) NOT NULL,
`address_postal_code` varchar(10) NOT NULL,
PRIMARY KEY (`serial`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
insert into `address` values('1','Clark','Kent','344 Clinton St','Apt. #3B','Metropolis','NY','10001'),
('2','Dave','Jones','500 Second Avenue','Suite 100','Atlanta','GA','30303'),
('3','Tom','Watson','123 Golf Course Lane','Suite A','Macon','GA','31066'),
('4','Jack','Nicklaus','400 Laurel Oak Dr','Suite 49','Suwanee','GA','31044'),
('5','Betty','Smith','100 Main Street','Suite 500','Buffalo','NY','14201'),
('6','Bruce','Wayne','1007 Mountain Drive','','Gotham City','NY','10000');
SET FOREIGN_KEY_CHECKS = 1;
|
We will be creating several Perl scripts as follows. To keep it simple, we will use Perl to create all of the pages, even though the “add_entry.pl” script could be a standard HTML page. This will allow you to put all of the scripts in your cgi-bin directory. Here are the scripts that we will be creating and using:
— dashboard.pl -> This is your “home page”, and will list all of the entries in your address database.
— add_entry.pl -> This page will allow you to complete a form containing the new address information.
— add.pl -> This Perl script will save the information from the add.html web page.
— edit.pl -> This Perl script will allow you to edit an entry from your address database.
— edit_save.pl -> This will save the changes made from the edit.pl page.
— delete.pl -> This is step one in deleting a record, it takes you to the delete_confirm.pl page.
— delete_confirm.pl -> This will confirm the deletion of a record, or allow you to cancel the request.
For our home page (called dashboard), we need to create a web page (via a Perl script) that will list all of the addresses in the database. In this example, we aren’t using any logic to restrict the number of entries that are displayed, so this page will just display all of them (adding limits to a web page like this is a bit more complicated, and I might try to cover this in a future post). We will use a Perl script to display the HTML, as we will need to pull data from the database to be displayed in the web page. So, you will need to put the “directory.pl” script (home page) and all of the other scripts in your cgi-bin directory (and don’t forget to make all of your Perl scripts executable – via “chmod 755”). You could create a regular HTML page (.htm) using frames, with the top frame using HTML and then use a Perl script to pull the data for the bottom frame, but let’s just stick with this example for now.
Here is what the opening home page (dashboard.pl) should look like:
In this script, we will also create a link that will allow us to edit the information for a single address entry, as well as a link to delete the entry. We will use the serial database field to point to the record that we want to edit or delete. We will also display a link at the top for creating a new address entry, and this Perl script will be named “add.pl”. But first, here is the “dashboard.pl” script:
#!/usr/bin/perl
#--------------------------------------------------------------------------
# dashboard.pl
#--------------------------------------------------------------------------
# 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;
use CGI qw(:standard);
my $Database = "scripting_mysql";
print header;
print <<HTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Scripting MySQL - Address Book - Dashboard</title>
</head>
<body>
<center>
HTML
# leave the above line to the left of the page
# list my addresses
$dbh = ConnectToMySql($Database);
$query = "select serial, name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code FROM address order by serial";
$sth = $dbh->prepare($query);
$sth->execute();
$dbh->disconnect;
# list all of the addresses in a table
# be sure that the link for add.html is the correct path according to your system
print "<center><font color=blue>My Addresses - <a href=http://192.168.1.2/cgi-bin/scripting_mysql/add_entry.pl>Create New</a></font><p>";
print "<table border=0>";
# print table row headers
print "<tr><td>ID</td><td>First</td><td>Last</td><td>Address</td><td>Address</td><td>City</td><td>ST</td><td>ZIP</td></tr>";
# set your initial row background color
$bgcolor = "#EEEEEE";
while (@data = $sth->fetchrow_array()) {
$serial = $data[0];
$name_first = $data[1];
$name_last = $data[2];
$address_01 = $data[3];
$address_02 = $data[4];
$address_city = $data[5];
$address_state = $data[6];
$address_postal_code = $data[7];
# print rows of data
# for the $serial, we include the link to the edit.pl script - edit.pl?$serial
print "<tr bgcolor=$bgcolor><td><a href=edit.pl?id=$serial>$serial</a></td><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>";
# alternate background colors
if ($bgcolor =~ "#EEEEEE") { $bgcolor = "white"; }
else
{$bgcolor = "#EEEEEE";}
# end while (@data = $sth->fetchrow_array())
}
# print bottom of page
print <<HTML;
<table>
</body>
</html>
HTML
# leave the above line to the left of the page
exit;
# more on what I am doing with the accessSM file may be found at:
# https://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/
#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------
my ($db) = @_;
open(PW, "<..\/accessSM") || die "Can't access login credentials";
my $db= <PW>;
my $host= <PW>;
my $userid= <PW>;
my $passwd= <PW>;
chomp($db);
chomp($host);
chomp($userid);
chomp($passwd);
# or, you may comment out the above nine lines - starting with open(PW...
# and just put in your database connection information below
# and un-comment these lines
# but I don't like putting my connection information in a Perl script
# my $db = "database_name";
# my $host = "host_name";
# my $userid = "user_name";
# my $passwd = "password";
my $connectionInfo="dbi:mysql:$db;$host";
close(PW);
# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;
}
|
The add_entry.pl web page will look like this (with the data for a new entry already entered):
And here is the script to create the add_entry.pl web page:
#!/usr/bin/perl
#--------------------------------------------------------------------------
# add_entry.pl
#--------------------------------------------------------------------------
use CGI qw(:standard);
print header;
print <<HTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Scripting MySQL - Address Book - Add Entry</title>
</head>
<body>
<center><font color=blue>My Addresses - <a href=http://192.168.1.2/cgi-bin/scripting_mysql/dashboard.pl>Dashboard</a></font><p>
<table>
<form method="post" name="address" action="http://192.168.1.2/cgi-bin/scripting_mysql/add.pl">
<table>
<tr><td>Name First</td><td><INPUT TYPE=text NAME="name_first" id=name_first size=30></td></tr>
<tr><td>Name Last</td><td><INPUT TYPE=text NAME="name_last" id=name_last size=30></td></tr>
<tr><td>Address 1</td><td><INPUT TYPE=text NAME="address_01" id=address_01 size=40></td></tr>
<tr><td>Address 2</td><td><INPUT TYPE=text NAME="address_02" id=address_02 size=40></td></tr>
<tr><td>City</td><td><INPUT TYPE=text NAME="address_city" id=address_city size=30></td></tr>
<tr><td>State</td><td><INPUT TYPE=text NAME="address_state" id=address_state size=2></td></tr>
<tr><td>Zip</td><td><INPUT TYPE=text NAME="address_postal_code" id=address_postal_code size=10></td></tr>
<tr><td colspan=2><center><input type="submit" value="Add Address" alt="Add Address"></td></tr>
</form>
</body>
</html>
HTML
exit;
|
The form on this web page calls a Perl script named “add.pl”, which will insert the information to the MySQL database.
Here is the Perl script add.pl:
#!/usr/bin/perl
#--------------------------------------------------------------------------
# add.pl
#--------------------------------------------------------------------------
use DBI;
use DBD::mysql;
use CGI qw(:standard);
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
$buffer =~ tr/+/ /;
$buffer =~ s/\r/ /g;
$buffer =~ s/'/ /g;
$buffer =~ s/\n/ /g;
$buffer =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C",hex($1))/eg;
$buffer =~ s/<!--(.|\n)*-->/ /g;
$buffer =~ tr/\\|[|]|<|!|"|$|{|}|*|#|'|>|||;|%/ /;
@pairs = split(/&/,$buffer);
foreach $pair(@pairs){
($key,$value)=split(/=/,$pair);
$formdata{$key}.="$value";
}
# here are the values from the HTML form
$name_first = $formdata{'name_first'};
$name_last = $formdata{'name_last'};
$address_01 = $formdata{'address_01'};
$address_02 = $formdata{'address_02'};
$address_city = $formdata{'address_city'};
$address_state = $formdata{'address_state'};
$address_postal_code = $formdata{'address_postal_code'};
my $Database = "scripting_mysql";
print header;
print <<HTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Scripting MySQL - Address Book - New</title>
</head>
<body>
<center>
HTML
# leave the above line to the left of the page
# list my addresses
$dbh = ConnectToMySql($Database);
$query = "insert into address (name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code) values (?,?,?,?,?,?,?)";
$sth = $dbh->prepare($query);
$sth->execute("$name_first", "$name_last", "$address_01", "$address_02", "$address_city", "$address_state", "$address_postal_code");
$dbh->disconnect;
print "$name_first $name_last - was added to the database.<p>";
print "Return to the <a href=http://192.168.1.2/cgi-bin/scripting_mysql/dashboard.pl>Dashboard</a>";
# print bottom of page
print <<HTML;
<table>
</body>
</html>
HTML
# leave the above line to the left of the page
exit;
# more on what I am doing with the accessSM file may be found at:
# https://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/
#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------
my ($db) = @_;
open(PW, "<..\/accessSM") || die "Can't access login credentials";
my $db= <PW>;
my $host= <PW>;
my $userid= <PW>;
my $passwd= <PW>;
chomp($db);
chomp($host);
chomp($userid);
chomp($passwd);
# or, you may comment out the above nine lines - starting with open(PW...
# and just put in your database connection information below
# and un-comment these lines
# but I don't like putting my connection information in a Perl script
# my $db = "database_name";
# my $host = "host_name";
# my $userid = "user_name";
# my $passwd = "password";
my $connectionInfo="dbi:mysql:$db;$host";
close(PW);
# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;
}
|
After you add an entry to the database, this is the confirmation web page after a record has been added.
The add.pl page gives you a link to go back to the dashboard, which will then display all of your address database entries, including the one you just entered.
To edit an entry, from the dashboard web page, you simply click on the ID of the entry that you want to modify. For example, when you click on the first entry with a serial number of “1”, you will see this link:
http://192.168.1.2/cgi-bin/scripting_mysql/edit.pl?id=1
|
Clicking on an serial number link will call the Perl script named “edit.pl”. This will create a web page where you can edit the information and save it back to the database. The edit.pl web page looks like this:
Here is the edit.pl script:
#!/usr/bin/perl
#--------------------------------------------------------------------------
# edit.pl
#--------------------------------------------------------------------------
use DBI;
use DBD::mysql;
use CGI qw(:standard);
$query = new CGI;
$id = $query->param("id");
print header;
print <<HTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Scripting MySQL - Address Book - Edit</title>
</head>
<body>
HTML
# grab the information for this id
$dbh = ConnectToMySql($Database);
$query = "select serial, name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code FROM address where serial = '$id'";
$sth = $dbh->prepare($query);
$sth->execute();
$dbh->disconnect;
# list all of the addresses in a table
# be sure that the link for add.html is the correct path according to your system
print "<center><font color=blue>My Addresses - <a href=http://192.168.1.2/cgi-bin/scripting_mysql/dashboard.pl>Dashboard</a></font><p>";
print "<table border=0>";
while (@data = $sth->fetchrow_array()) {
$id = $data[0];
$name_first = $data[1];
$name_last = $data[2];
$address_01 = $data[3];
$address_02 = $data[4];
$address_city = $data[5];
$address_state = $data[6];
$address_postal_code = $data[7];
}
print <<HTML;
<form method="post" name="address" action="http://192.168.1.2/cgi-bin/scripting_mysql/edit_save.pl">
<input type=hidden name=id value="$id">
<tr><td align=right>Name First </td><td><INPUT TYPE=text NAME="name_first" id=name_first size=30 value="$name_first"></td></tr>
<tr><td align=right>Name Last </td><td><INPUT TYPE=text NAME="name_last" id=name_last size=30 value="$name_last"></td"></tr>
<tr><td align=right>Address 1 </td><td><INPUT TYPE=text NAME="address_01" id=address_01 size=40 value="$address_01"></td></tr>
<tr><td align=right>Address 2 </td><td><INPUT TYPE=text NAME="address_02" id=address_02 size=40 value="$address_02"></td></tr>
<tr><td align=right>City </td><td><INPUT TYPE=text NAME="address_city" id=address_city size=30 value="$address_city"></td></tr>
<tr><td align=right>State </td><td><INPUT TYPE=text NAME="address_state" id=address_state size=2 value="$address_state"></td></tr>
<tr><td align=right>Zip </td><td><INPUT TYPE=text NAME="address_postal_code" id=address_postal_code size=10 value="$address_postal_code"</td></tr>
<tr><td colspan=2><center><input type="submit" value="Save Changes" alt="Save Changes"></td></tr>
</form>
</table>
</body>
</html>
HTML
exit;
# more on what I am doing with the accessSM file may be found at:
# https://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/
#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------
my ($db) = @_;
open(PW, "<..\/accessSM") || die "Can't access login credentials";
my $db= <PW>;
my $host= <PW>;
my $userid= <PW>;
my $passwd= <PW>;
chomp($db);
chomp($host);
chomp($userid);
chomp($passwd);
# or, you may comment out the above nine lines - starting with open(PW...
# and just put in your database connection information below
# and un-comment these lines
# but I don't like putting my connection information in a Perl script
# my $db = "database_name";
# my $host = "host_name";
# my $userid = "user_name";
# my $passwd = "password";
my $connectionInfo="dbi:mysql:$db;$host";
close(PW);
# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;
}
|
The edit.pl script will create the web page for you to make your changes. After you have made the changes, the form will call another Perl script named “edit_save.pl” to apply those changes to the database. Here is the edit_save.pl script:
#!/usr/bin/perl
#--------------------------------------------------------------------------
# edit_save.pl
#--------------------------------------------------------------------------
use DBI;
use DBD::mysql;
use CGI qw(:standard);
use LWP::UserAgent;
my $Database = "address";
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
$buffer =~ tr/+/ /;
$buffer =~ s/\r/ /g;
$buffer =~ s/'/ /g;
$buffer =~ s/\n/ /g;
$buffer =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C",hex($1))/eg;
$buffer =~ s/<!--(.|\n)*-->/ /g;
$buffer =~ tr/\\|[|]|<|!|"|$|{|}|*|#|'|>|||;|%/ /;
@pairs = split(/&/,$buffer);
foreach $pair(@pairs){
($key,$value)=split(/=/,$pair);
$formdata{$key}.="$value";
}
$id = $formdata{'id'};
$name_first = $formdata{'name_first'};
$name_last = $formdata{'name_last'};
$address_01 = $formdata{'address_01'};
$address_02 = $formdata{'address_02'};
$address_city = $formdata{'address_city'};
$address_state = $formdata{'address_state'};
$address_postal_code = $formdata{'address_postal_code'};
$dbh = ConnectToMySql($Database);
$query = "update address set name_first = '$name_first', name_last = '$name_last', address_01 = '$address_01', address_02 = '$address_02', address_city = '$address_city', address_state = '$address_state', address_postal_code = '$address_postal_code' where serial = '$id'";
$sth = $dbh->prepare($query);
$sth->execute();
$dbh->disconnect;
print header;
print <<HTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Scripting MySQL - Address Book - Edit Saved</title>
</head>
<body>
<center>
<center><font color=blue>My Addresses - <a href=http://192.168.1.2/cgi-bin/scripting_mysql/dashboard.pl>Dashboard</a></font><p>
The following information was updated:<p>
<table>
<tr><td align=right>Name First </td><td>$name_first</td></tr>
<tr><td align=right>Name Last </td><td>$name_last</td"></tr>
<tr><td align=right>Address 1 </td><td>$address_01</td></tr>
<tr><td align=right>Address 2 </td><td>$address_02</td></tr>
<tr><td align=right>City </td><td>$address_city</td></tr>
<tr><td align=right>State </td><td>$address_state</td></tr>
<tr><td align=right>Zip </td><td>$address_postal_code</td></tr>
</table>
HTML
# leave the above line to the left of the page
exit;
# more on what I am doing with the accessSM file may be found at:
# https://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/
#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------
my ($db) = @_;
open(PW, "<..\/accessSM") || die "Can't access login credentials";
my $db= <PW>;
my $host= <PW>;
my $userid= <PW>;
my $passwd= <PW>;
chomp($db);
chomp($host);
chomp($userid);
chomp($passwd);
# or, you may comment out the above nine lines - starting with open(PW...
# and just put in your database connection information below
# and un-comment these lines
# but I don't like putting my connection information in a Perl script
# my $db = "database_name";
# my $host = "host_name";
# my $userid = "user_name";
# my $passwd = "password";
my $connectionInfo="dbi:mysql:$db;$host";
close(PW);
# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;
}
|
After you have edited your record, the edited results web page will look like this:
You can then click on the Dashboard link to go back to the main dashboard page.
If you want to delete a record, from the dashboard.pl page, you simply click on the red X image on the row that you want to delete. For example, when you click on the X for the serial number of “1”, you will see this link:
http://192.168.1.2/cgi-bin/scripting_mysql/delete.pl?id=1
|
This link takes you to the delete.pl page, where you are asked if you want to confirm the deletion, or you may cancel and go back to the dashboard. When you click on an X to delete a row, you will see a page like this:
And here is the delete.pl script:
#!/usr/bin/perl
#--------------------------------------------------------------------------
# delete.pl
#--------------------------------------------------------------------------
use DBI;
use DBD::mysql;
use CGI qw(:standard);
$query = new CGI;
$id = $query->param("id");
my $Database = "scripting_mysql";
print header;
print <<HTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Scripting MySQL - Address Book - Delete Record</title>
</head>
<body>
<center>
HTML
# grab the information for this id
$dbh = ConnectToMySql($Database);
$query = "select serial, name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code FROM address where serial = '$id'";
$sth = $dbh->prepare($query);
$sth->execute();
$dbh->disconnect;
# list all of the addresses in a table
# be sure that the link for add.html is the correct path according to your system
print "<center><font color=blue>My Addresses - <a href=http://192.168.1.2/cgi-bin/scripting_mysql/dashboard.pl>Dashboard</a></font><p>";
print "Are you sure you want to delete this record?<p>";
print "<table border=0>";
while (@data = $sth->fetchrow_array()) {
$id = $data[0];
$name_first = $data[1];
$name_last = $data[2];
$address_01 = $data[3];
$address_02 = $data[4];
$address_city = $data[5];
$address_state = $data[6];
$address_postal_code = $data[7];
}
print <<HTML;
<form method="post" name="address" action="http://192.168.1.2/cgi-bin/scripting_mysql/delete_confirm.pl?id=$id">
<input type=hidden name=id value="$id">
<tr><td align=right>Name First </td><td>$name_first</td></tr>
<tr><td align=right>Name Last </td><td>$name_last</td"></tr>
<tr><td align=right>Address 1 </td><td>$address_01</td></tr>
<tr><td align=right>Address 2 </td><td>$address_02</td></tr>
<tr><td align=right>City </td><td>$address_city</td></tr>
<tr><td align=right>State </td><td>$address_state</td></tr>
<tr><td align=right>Zip </td><td>$address_postal_code</td></tr>
<tr><td><center><input type="submit" value="Delete Address" alt="Delete Address"></form></td><td><center><form method="post" name="address" action="http://192.168.1.2/cgi-bin/scripting_mysql/dashboard.pl"><input type="submit" value="Cancel" alt="Cancel"></form></td></tr>
</table>
</body>
</html>
HTML
# leave the above line to the left of the page
exit;
# more on what I am doing with the accessSM file may be found at:
# https://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/
#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------
my ($db) = @_;
open(PW, "<..\/accessSM") || die "Can't access login credentials";
my $db= <PW>;
my $host= <PW>;
my $userid= <PW>;
my $passwd= <PW>;
chomp($db);
chomp($host);
chomp($userid);
chomp($passwd);
# or, you may comment out the above nine lines - starting with open(PW...
# and just put in your database connection information below
# and un-comment these lines
# but I don't like putting my connection information in a Perl script
# my $db = "database_name";
# my $host = "host_name";
# my $userid = "user_name";
# my $passwd = "password";
my $connectionInfo="dbi:mysql:$db;$host";
close(PW);
# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;
}
|
If you choose to not delete the record, you are simply taken back to the Dashboard. If you choose to delete the record, then the delete-confirm.pl script is executed, and the record is deleted. Here is the confirmation web page for a deletion:
And here is the delete_confirm.pl script:
#!/usr/bin/perl
#--------------------------------------------------------------------------
# delete_confirm.pl
#--------------------------------------------------------------------------
use DBI;
use DBD::mysql;
use CGI qw(:standard);
$query = new CGI;
$id = $query->param("id");
my $Database = "scripting_mysql";
print header;
print <<HTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Scripting MySQL - Address Book - Delete Record Confirmed</title>
</head>
<body>
<center>
Are you sure you want to delete this record?
HTML
# grab the information for this id
$dbh = ConnectToMySql($Database);
$query = "select serial, name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code FROM address where serial = '$id'";
$sth = $dbh->prepare($query);
$sth->execute();
$dbh->disconnect;
# list all of the addresses in a table
# be sure that the link for add.html is the correct path according to your system
print "<center><font color=blue>My Addresses - <a href=http://192.168.1.2/cgi-bin/scripting_mysql/dashboard.pl>Dashboard</a></font><p>";
print "The following information was deleted.<p>";
print "<table border=0>";
while (@data = $sth->fetchrow_array()) {
$id = $data[0];
$name_first = $data[1];
$name_last = $data[2];
$address_01 = $data[3];
$address_02 = $data[4];
$address_city = $data[5];
$address_state = $data[6];
$address_postal_code = $data[7];
}
print <<HTML;
<tr><td align=right>Name First </td><td>$name_first</td></tr>
<tr><td align=right>Name Last </td><td>$name_last</td"></tr>
<tr><td align=right>Address 1 </td><td>$address_01</td></tr>
<tr><td align=right>Address 2 </td><td>$address_02</td></tr>
<tr><td align=right>City </td><td>$address_city</td></tr>
<tr><td align=right>State </td><td>$address_state</td></tr>
<tr><td align=right>Zip </td><td>$address_postal_code</td></tr>
</table>
</body>
</html>
HTML
# leave the above line to the left of the page
# here we delete the record
$dbh = ConnectToMySql($Database);
$query = "delete from address where serial = '$id'";
$sth = $dbh->prepare($query);
$sth->execute();
$dbh->disconnect;
exit;
# more on what I am doing with the accessSM file may be found at:
# https://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/
#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------
my ($db) = @_;
open(PW, "<..\/accessSM") || die "Can't access login credentials";
my $db= <PW>;
my $host= <PW>;
my $userid= <PW>;
my $passwd= <PW>;
chomp($db);
chomp($host);
chomp($userid);
chomp($passwd);
# or, you may comment out the above nine lines - starting with open(PW...
# and just put in your database connection information below
# and un-comment these lines
# but I don't like putting my connection information in a Perl script
# my $db = "database_name";
# my $host = "host_name";
# my $userid = "user_name";
# my $passwd = "password";
my $connectionInfo="dbi:mysql:$db;$host";
close(PW);
# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;
}
|
One alternative to deleting the record would be to include a separate database field called “active”, set the field value to “yes” for all current records (if you have already imported records) and set the default value for this field to “yes” for any new records. Then, instead of deleting the information, you simply set “active” to “no”. So when you retrieve your list on the dashboard.pl page, you will only look for records where “active” is equal to “yes”. You will still have the “deleted” information available to you in the database. You could even duplicate the dashboard.pl script to have a “delete” page that displays the records that have been deleted.
To use the “active” option, you will need to make the following changes:
You would need to add this line in your create table SQL statement: (I would insert the line after the “serial” field in your create table statement)
`active` varchar(3) NOT NULL DEFAULT 'yes',
|
Then, in your dashboard.pl script, you would simply change the $query to:
$query = "select serial, name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code FROM address where active = 'yes' order by serial";
|
For your add.pl and add_entry.pl scripts. you don’t need to do anything as the “active” field has a default value of “yes” for new records.
For your edit.pl and edit_save.pl scripts, you don’t need to do anything as you aren’t changing the “active” field.
And in your delete_confirm.pl script, instead of deleting the record, simply change the $query = “delete from address where serial = ‘$id'”; to:
$query = "update address set active = 'no' where serial = '$id'";
|
With these scripts, you should be able to install everything and have it running in a few minutes. And, if my lawyer friend gets tired of spending all day at the courthouse, he could always learn a new skill.
info [at] Scripting
Scripting MySQL 