Developing Web Applications with Perl, memcached, MySQL® and Apache

Chapter 7. Simple Database Application

Now that the DBI API has been discussed in detail, you probably would like to see a practical example using what you've learned and see a database-driven Perl program in action. The purpose of this chapter is to give you a simple example of using Perl to write a MySQL database-driven application using the DBI Perl module and its API, without web functionality so the focus is solely on Perl and MySQL. The application will be a simple command-line interface contact list. This will be a fully functional Perl program with a simple menu for selecting different operations, which prompts the user for various decision making as well as data inputs.

Planning Application Functionality

The first thing to do in writing any application is to think about what functionality you want it to have—inputs and expected outputs. What primary operations does it need to be able to do?

With a contacts application, you would probably want to do the following operations:

• Add contacts (INSERT)

• Update contacts (UPDATE)

• Delete contacts (DELETE)

• Edit a contact (calls UPDATE or INSERT)

• List contacts (SELECT)

• Display menu of choices

• Allow lookup of contacts (selective list)

Schema Design

For a program to store contacts and support all of these operations, you will want a simple table containing various contact attributes:

CREATE TABLE $table ( contact_id int(4) not null auto_increment, first_name varchar(32) not null default '', last_name varchar(32) not null default '', age int(4) not ...