Websites, CMS’s, MySQL, PHP and … Perl?

One of the difficulties with maintaining a personal project site, such as this one (http://joshashby.com) is updating the content. Once upon a time, when my site was less than a year old and on the freeweb network, I manually opened my pages, added the content I wanted, and saved/closed the file. This worked fine for me, except for the fact that I could not modify, and update my site from any computer. Enter byethost and WordPress. Byethost provides me with cheap (free really) hosting for my site, and WordPress (blogging software that is also a CMS[content management system]) allows me to edit and update my content from anywhere in the world as long as I have internet. this also means that I no longer have to open the files, as WordPress does this for me with PHP and MySQL. This is all fine and dandy, until I want to change the way WordPress does something; Iknow very little PHP, just enough to know whats going on, but not enough to write a full program such as a CMS, and the same goes for Perl. The difference that I see when I look at Perl and PHP is that PHP just isn’t my kind of programming language, it appears messy and non logical to me, where as Perl is the opposite (probably because it has similar logic to python I’ve decided). As a result I decided to take the week to write a simple Perl script that would connect to a MySQL database, and be able to retrieve and write data to and from the database; in simple terms, make a comment script with Perl.

In this How To/post I’ll walk through my experience write my first web program in Perl, and interfacing with a MySQL database. Please note that I am in no way a professional Perl programmer, and that there are more than one way to do what will be achieved in this How To, and some of the methods used in this How To may not be the best option.

For those who don’t know what MySQL is, it is a database server that is often used on websites to store data (such as this post is stored in a MySQL database) which PHP, or another web language, reads and displays for you to read; and for those who don’t know what Perl and PHP are, they’re programming languages that are commonly used on websites to provide more content and interaction to the users. (This info is a quick, very summed up version of these. All three are very powerful and you should probably know what they are before you start using them).

Note: This script was developed on my bedroom server, which has Perl installed, my byethost account, being free, doesn’t provide Perl and as a result I will not have examples for this How To, sorry. Also, please note that i assume that you know where to put these files, and have a web server with Perl turned on. if you do not have this stuff please take a look at the Xampp project for a development web server.

In this script, we’ll open a MySQL database in Perl, read the data and display it on a page with some formating, and if a user inputs data into the comment field the script will write that data to the database, and then re-read it, providing a nice current record of the comments. We’ll also create a time stamp for use in the database, and develop a way to make sure comments are duplicated with an id system.

Lets get started.

First we must make a MySQL database that the Perl script will connect to. I named my database “perl” and made a table inside of it called “pl_db”. pl_db has 5 fields in the table, in this order: id, date, name, email, post. In each of these fields will be stored the comments ID #, the date it was commented, the name of the person that wrote it, their email address, and their comment. (FIXME: Add the MySQL commands for this)

Now that we have a MySQL database, and a table with the proper fields created, lets write our Perl script.

The first thing we need to do when writing a Perl script is declare where the Perl compiler lives.

#!/usr/bin/perl

This just tells the computer/server that this is a Perl script, and where it should look to compile and run the script. Next we must tell perl that we are working with a web page:

print "Content-type: text/html nn";

Before we get to far into the details of this script, we should learn some basic Perl commands. The “print” function will allow use to print out a string or variable to the webpage that will be created.

#!/usr/bin/perl
print "Content-type: text/html nn";
print "hello, World!";

This demonstrates the print function, if all goes well the above code will produce “hello, World!” in your browser when you navigate to the page.

Variables, and arrays are also two parts of Perl that we’ll be using in this script, so lets take a look at them. a variable in Perl is declared by placing a “$” in front of your variable name, such as $date creates a variable called data. arrays are similar but instead of a “$” we use “@” like so @datestamp. Variables store one string of data, where arrays can be said as to hold variables in a referable spot. If we set $date equal to 10, and then call upon $date later on in the program, the number 10 will be used. if we set @datestamp equal to 10 24 2009, and call upon $datestamp[0] we will have 10 as the variable. we can also convert from variables to arrays and from arrays to variables, but for this script will only convert an array to a variable. to do this we use

join("how you want the array items to be separated", @your_array);

Nows lets combine what we just learned into a program.

#!/usr/bin/perl
print "Content-type: text/html nn";
$hello = "hello, World!";
@helloworld = ("Hello", ",", "World", "!");
print $hello;
print @helloworld;
$array_var = join(" ", @helloworld);
print $array_var

If all goes well you should have “hello, World!Hello,World!Hello , World !” printed out on your browser screen. we can take and format this even farther by printing out

 <br> 

Tags.

Now we can get started on working with Perl and MySQL. First we must tell Perl that we want to use the DBI, and DBD::mysql modules. These modules will allow us to interface with the MySQL database. We declare this with the “use” function in Perl.

use DBI;
use DBD::mysql;

Now that we have established some very basic knowledge of Perl, we can get started with learning about connecting with the MySQL database we made earlier. The first thing we’ll learn to do is connect to, and read the MySQL database, printing out the data. Again we need to tell Perl to use the database modules, and we’ll have to declare a few variables that we’ll use to make our life easier.

#!/usr/bin/perl
use DBI;
use DBD::mysql;

print "Content-type: text/html nn";

#MySQL database settings
$platform = "mysql";
$database = "databasename";
$host = "localhost";
$port = "3306";
$tablename = "tablename";
$user = "user";
$pw = "password";
$dsn = "dbi:$platform:$database:$host:$port";

#when called this will connect to the MySQL database
$connect = DBI->connect($dsn, $user, $pw) or die "Couldn't connect to database!" . DBI->errstr;

#sets up the data, and column names that will be used later
$query = "SELECT * FROM $tablename ORDER BY id desc";#this is a sql query, it will grab the data from $tablename and order it by id
$query_handle = $connect->prepare($query);
$query_handle->execute();#execute the query
$query_handle->bind_columns(undef, $id, $date, $name, $email, $post);#binds the variables to the columns that are returned by the query

while($query_handle->fetch()) {#get the data in the database and print it out
print "On $date, $name Said<br> $post <br>";#will print out something like "On 2009-10-24, Joshua Ashby said:"
}

$query_handle->finish(); $connect->disconnect();#finish the first query and close the database connection

It should be noted that from here on out, I will not go as deeply into how to code works from here on out, instead this will be done with comments in the code, if you have not caught on yet, comments are marked by a “#” in Perl.

The above code connects to the MySQL database (please change the variables, $database $tablename $user $pw and any other variables that do not fit your configuration) then reads the table data, and prints it out in a nicely formated way. if you would like to change the formating, change the

print "On $date, $name Said<br> $post <br>";

line to fit your need.

Now that we have to ability to connect to, and read from our MySQL database, we can move on to writing data to the database, and then reading our new data. Writing data to the database is fairly straight forward, and implemented in the same way that reading is.

By writing, we want to write the id which can not be the same, the date, a name, an email, and a comment. The name, email and comment are easy, simply declare them as variables, for the date stamp and the id we must do a little bit of Perl magic.

#!/usr/bin/perl
use DBI;
use DBD::mysql;

print "Content-type: text/html nn";

#MySQL database settings
$platform = "mysql";
$database = "databasename";
$host = "localhost";
$port = "3306";
$tablename = "tablename";
$user = "user";
$pw = "password";
$dsn = "dbi:$platform:$database:$host:$port";

#when called this will connect to the MySQL database
$connect = DBI->connect($dsn, $user, $pw) or die "Couldn't connect to database!" . DBI->errstr;

#set up your variables here:
$f_id = "somenumber";
$n_date = "dateinYYMMDDformat";
$f_name = "bob";
$f_email = "bob@example.com";
$f_post = "this is a test comment";

#sets up the data that we'll write to the database
$queryw = "INSERT INTO $tablename VALUES ('$f_id', '$n_date', '$f_name', '$f_email', '$f_post')";#this is a sql query, it will store the data we give it in the database
$query_handlew = $connect->prepare($queryw);#connect to our database, and get ready to send the query
$query_handlew->execute();#execute the query
$query_handlew->finish();#finish the query

#sets up the data, and column names that will be used later
$query = "SELECT * FROM $tablename ORDER BY id desc";#this is a sql query, it will grab the data from $tablename and order it by id
$query_handle = $connect->prepare($query);#connect to our database, and get ready to send the query
$query_handle->execute();#execute the query
$query_handle->bind_columns(undef, $id, $date, $name, $email, $post);#binds the variables to the columns that are returned by the query

while($query_handle->fetch()) {#get the data in the database and print it out
print "On $date, $name Said<br> $post <br>";#will print out something like "On 2009-10-24, Joshua Ashby said:"
}

$query_handle->finish(); $connect->disconnect();#finish the first query and close the database connection

Simply doing the above code will generate a comment with a “static” date and id number, which is not what we want in our end result, there for we must use the Time::localtime module, and do a little bit of Perl coding.

use Time::localtime;
... some bits of code...
$years = localtime->year();#this builds the time stamp for the db, in YYMMDD format
$year = 1900 + $years;
$day = localtime->mday();
$months = localtime->mon();
$month = 1 + $months;
@f_date = ($year, $month, $day);
$n_date = join("-", @f_date);
...some more bits of code...
$queryn = "SELECT MAX(id) FROM $tablename";#connect to the db and get the max id number
$query_handlen = $connect->prepare($queryn);
$query_handlen->execute();
$query_handlen->bind_columns($n_id);
while($query_handlen->fetch()) {#get the max id number and add 1 to it, this will be for the next data entry
$f_id = $n_id + 1;
}

The above code will generate a date stamp from the servers local code in YYMMDD format, and store it in $n_date, and then get the max id number that is in the database, add one to that id number, and then store the resulting number in $f_id. Lets put the code together now:

#!/usr/bin/perl
use DBI;
use DBD::mysql;
use Time::localtime;

print "Content-type: text/html nn";

#MySQL database settings
$platform = "mysql";
$database = "databasename";
$host = "localhost";
$port = "3306";
$tablename = "tablename";
$user = "user";
$pw = "password";
$dsn = "dbi:$platform:$database:$host:$port";

#when called this will connect to the MySQL database
$connect = DBI->connect($dsn, $user, $pw) or die "Couldn't connect to database!" . DBI->errstr;

#set up your variables here:
$f_name = "bob";
$f_email = "bob@example.com";
$f_post = "this is a test comment";

$years = localtime->year();#this builds the time stamp for the db, in YYMMDD format
$year = 1900 + $years;
$day = localtime->mday();
$months = localtime->mon();
$month = 1 + $months;
@f_date = ($year, $month, $day);
$n_date = join("-", @f_date);

$queryn = "SELECT MAX(id) FROM $tablename";#connect to the db and get the max id number
$query_handlen = $connect->prepare($queryn);
$query_handlen->execute();
$query_handlen->bind_columns($n_id);
while($query_handlen->fetch()) {#get the max id number and add 1 to it, this will be for the next data entry
$f_id = $n_id + 1;
}

#sets up the data that we'll write to the database
$queryw = "INSERT INTO $tablename VALUES ('$f_id', '$n_date', '$f_name', '$f_email', '$f_post')";#this is a sql query, it will store the data we give it in the database
$query_handlew = $connect->prepare($queryw);#connect to our database, and get ready to send the query
$query_handlew->execute();#execute the query
$query_handlew->finish();#finish the query

#sets up the data, and column names that will be used later
$query = "SELECT * FROM $tablename ORDER BY id desc";#this is a sql query, it will grab the data from $tablename and order it by id
$query_handle = $connect->prepare($query);#connect to our database, and get ready to send the query
$query_handle->execute();#execute the query
$query_handle->bind_columns(undef, $id, $date, $name, $email, $post);#binds the variables to the columns that are returned by the query

while($query_handle->fetch()) {#get the data in the database and print it out
print "On $date, $name Said<br> $post <br>";#will print out something like "On 2009-10-24, Joshua Ashby said:"
}

$query_handle->finish(); $connect->disconnect();#finish the first query and close the database connection

The above code will now connect to the database, calculate the id number, and date stamp and write our comment to the database. it will then proceed to read the database and print out the data.

All thats left is to get data from a user form, and submit that to the database. To do this we’ll use the CGI module.

use CGI;
...some bits of code...
$form=new CGI;
$f_name=CGI::escapeHTML($form->param("f_name"));
$f_email=CGI::escapeHTML($form->param("f_email"));
$f_post=CGI::escapeHTML($form->param("f_post"));
...more code...
if($f_name && $f_email) {
$queryn = "SELECT MAX(id) FROM $tablename";
$query_handlen = $connect->prepare($queryn);
$query_handlen->execute();
$query_handlen->bind_columns($n_id);

while($query_handlen->fetch()) {
$f_id = $n_id + 1;
}

$query_handlen->finish();
$years = localtime->year();
$year = 1900 + $years;
$day = localtime->mday();
$months = localtime->mon();
$month = 1 + $months;
@f_date = ($year, $month, $day);
$n_date = join("-", @f_date);

$queryw = "INSERT INTO $tablename VALUES ('$f_id', '$n_date', '$f_name', '$f_email', '$f_post')";
$query_handlew = $connect->prepare($queryw);
$query_handlew->execute();
$query_handlew->finish();
$queryr = "SELECT * FROM $tablename ORDER BY id desc";

$query_handler = $connect->prepare($query);
$query_handler->execute();
$query_handler->bind_columns(undef, $id, $date, $name, $email, $post);

while($query_handler->fetch()) {
print "$date .::. $name <br> $post <br>";
}

$query_handler->finish(); $connect->disconnect();

} else {

while($query_handle->fetch()) {
print "$date .::. $name <br> $post <br>";
}

}

$query_handle->finish(); $connect->disconnect();
...and more code...
<p>Todays Date (YYMMDD): $n_date <br>
Add entry:
<form action=mysql-10-21.pl method=get>
<table border=0 cellpadding=0 cellspacing=0>
<tr><td>Name:</td><td> <input type=text size=30 name=f_name></td></tr>
<tr><td>Email:</td><td> <input type=text size=30 name=f_email></td></tr>
<tr><td>Comment:</td><td> <textarea type=text rows=3 cols=30 name=f_post></textarea></td></tr>
<tr><td></td><td><input type=submit border=0 value="Comment"></td></tr>
</table>
</p>
<p style="text-align: left;">This is a basic example of what we'll be doing in our code. Connect to database -> if theres user data -> submit it and read the database OR if there is no user data -> read database -> print out the data.</p>
1
#!/usr/bin/perl
use DBI;
use DBD::mysql;
use CGI;
use Time::localtime;

$form=new CGI;
$f_name=CGI::escapeHTML($form->param("f_name"));
$f_email=CGI::escapeHTML($form->param("f_email"));
$f_post=CGI::escapeHTML($form->param("f_post"));

print "Content-type: text/html nn";

#MySQL database settings
$platform = "mysql";
$database = "databasename";
$host = "localhost";
$port = "3306";
$tablename = "tablename";
$user = "user";
$pw = "password";
$dsn = "dbi:$platform:$database:$host:$port";

$connect = DBI->connect($dsn, $user, $pw) or die "Couldn't connect to database!" . DBI->errstr;

$query = "SELECT * FROM $tablename ORDER BY id desc";#get the data and bind the columns to variables
$query_handle = $connect->prepare($query);
$query_handle->execute();
$query_handle->bind_columns(undef, $id, $date, $name, $email, $post);

print <<"A";#prints out until "A"

<html>
<head>
  <meta content="text/html; charset=ISO-8859-1"
 http-equiv="content-type">
  <title>Perl and MySQL Comments</title>
</head>
<body>
Comments:<br>

A

if($f_name && $f_email) {#if there is a name and email entered do this

$queryn = "SELECT MAX(id) FROM $tablename";#get the max id number from the database and bind the column to $n_id
$query_handlen = $connect->prepare($queryn);
$query_handlen->execute();
$query_handlen->bind_columns($n_id);

while($query_handlen->fetch()) {
$f_id = $n_id + 1;#get the highest id number, and add 1 to it
}

$query_handlen->finish();

#generate a date stamp
$years = localtime->year();
$year = 1900 + $years;
$day = localtime->mday();
$months = localtime->mon();
$month = 1 + $months;
@f_date = ($year, $month, $day);
$n_date = join("-", @f_date);

$queryw = "INSERT INTO $tablename VALUES ('$f_id', '$n_date', '$f_name', '$f_email', '$f_post')";#insert our data into the database
$query_handlew = $connect->prepare($queryw);
$query_handlew->execute();
$query_handlew->finish();

$queryr = "SELECT * FROM $tablename ORDER BY id desc";#get all the data and bind the columns to variables
$query_handler = $connect->prepare($query);
$query_handler->execute();
$query_handler->bind_columns(undef, $id, $date, $name, $email, $post);

while($query_handler->fetch()) {
print "$date .::. $name <br> $post <br>";#retrieve each line of data, and print it out
}

$query_handler->finish(); $connect->disconnect();#finish and disconnect from the database

} else {#if there is no use data, do this

while($query_handle->fetch()) {
print "$date .::. $name <br> $post <br>";#retrieve each line of data, and print it out
}

}

$query_handle->finish(); $connect->disconnect();#finishes the query, and closes the connection with the database

#generates the date stamp
$years = localtime->year();
$year = 1900 + $years;
$day = localtime->mday();
$months = localtime->mon();
$month = 1 + $months;
@f_date = ($year, $month, $day);
$n_date = join("-", @f_date);

print <<"B";

<p>Todays Date (YYMMDD): $n_date <br>
Add entry:
<form action=mysql-10-21.pl method=get>
<table border=0 cellpadding=0 cellspacing=0>
<tr><td>Name:</td><td> <input type=text size=30 name=f_name></td></tr>
<tr><td>Email:</td><td> <input type=text size=30 name=f_email></td></tr>
<tr><td>Comment:</td><td> <textarea type=text rows=3 cols=30 name=f_post></textarea></td></tr>
<tr><td></td><td><input type=submit border=0 value="Comment"></td></tr>
</table>
</p>
</form>
</body>
</html>

B

Thats it! your all done. our code will now enter a comment in if there is data for one, or just read off what the database already has in it.

Congratulations, you’ve made it this far (which is quite far in one how to). Time for you to go off and do great things with Perl and MySQL.

The source code for this How To will be posted in the downloads section of this site as soon as I get around to making a file for it, in the mean time, the code also lives at github.

If there is anything wrong, or anything that you think should be changed or added/removed from this How To, feel free to send me an email, or comment on this page. I am by no means a professional Perl programmer and would appreciate the advice, Thanks!