But First, a Database…

Laying the Groundwork for Database Driven Web Sites

by Sheri German

When the great choreographer George Balanchine first came to this country in the 1930's, his sponsors were eager for him to establish a great American ballet company. Being a wise man, he counseled, "But first, a school…" He knew that he needed a foundation for the company members who would represent the neoclassical style of his now famous New York City Ballet.

Now what can this possibly have to do with databases and dynamic Web sites? Probably not much more than the spinach and Eunuchs analogy of the last Journal article I wrote on PHP and MySQL, but at least there is more artistry involved here. If you can understand that creating a database driven Web site is not an "out-of-box" experience, that even WYSIWYG software tools such as Dreamweaver MX and GoLive need a foundation, then you're on your way to taking the journey towards creating your first dynamic Web page.

In the last Pi Journal article, we learned how to set up Mac OS X so that it can test and run PHP scripts and MySQL databases. There are two more hurdles to cross before we choreograph our first script/database connection, however. First, a database: in this article, we'll delve into the art of creating databases. We will use the free, UNIX (and, of course, Mac OS X) compatible database MySQL in conjunction with phpMyAdmin, a GUI tool.

The second hurdle is to understand HTML forms. Don't all arts involve creating form of some kind? So do our database driven Web sites benefit from pouring our content into forms. If you don't know how to create forms in a Web page, you should also read my other article in this Journal, which is about creating forms in Adobe GoLive. The knowledge here will be applicable to any editor you choose to use, however, so don't be deterred if you don't use GoLive, or are even a hand coding warrior.

Declaring our Intentions

Before designing a database, we should think about how it will be used. Let's pretend we are the administrators of a pet rescue facility. Every day, there are new arrivals at the center, and current residents (we hope) are adopted. Our newly constructed Web site offers a search page for visitors so that they can look for possible pets. There are dogs and cats of various breeds and ages. Each animal has its picture taken with a digital camera, and that picture is displayed as well.

We also need an administration section of the Web site where the records for the animals can be updated right in the browser. Adopted pets can be removed; new arrivals can be entered. This is done through a simple form with an "Update Record" submit button that will later be facilitated with PHP scripting.

But first, a database…in our case, specifically, we'll use a MySQL database. Our database will contain 8 fields: an ID number for each animal, its species, its breed, its name (if known), its age (if known), its personality, the date the animal entered the facility, and the file name of the picture of the animal.

Data Types

It's a good idea to understand a little about data types before we start. What kind of data are we going to store in our fields? There are three categories of data types in MySQL. Those are number, date and time, and string (for, in a word, words) data types. We are going to use the following types for our pet database.

String Types

We will use VARCHAR for species, breed, name, and image name. We will use TEXT for the personality description.

VARCHAR is any variable length string between 1 and 255 characters. You must define a length.

TEXT is a field for which the maximum number of characters is 65535. You do not need to define the length. We might have a lot to say about a particular animal's personality, so the TEXT data type will fit the bill here.

Number Types

We will use INT for the ID for each animal, and we'll use TINYINT for the age.

INT stands for integer. This is a normal sized number that can be signed or unsigned. A signed number can include negative numbers while an unsigned number includes only positive numbers. The maximum number, if signed, is &endash; 2147483648 to 2147483647. An unsigned number can max out at 4294967295.

TINYINT is a very small integer that can range from &endash;128 to 127, if signed. It can go to 255 if unsigned. Since most pets don't live past their 20's, the TINYINT will work well.

Date and Time

Another useful data type is the DATE, which is entered as yyyy-mm-dd and the DATETIME type which is entered the same, but with the addition of time entered as hh-mm-ss. Since it would be nice to know when the animals have arrived at the center, we have a field for DATETIME.

Using phpMyAdmin

Fire up phpMyAdmin in your browser. You should see the welcome screen and a text field where you can enter the name of a database. Let's enter in the name rescued_pets, then select the "create" button.

If all goes well, you should see a new page with the message "Database rescued_pets running on localhost." No tables are in the database yet, though. You can choose to run an SQL query in the text area field to create one and all of its fields. Alternatively, you can let phpMyAdmin make it easier by using the "Create new table on database rescued_pets:" text field. I vote for that, so lets do it. Enter in the name "adoption_list" (without the quotes) and enter 8 as the number of fields. Click the "Go" button.

The next screen is a thing of beauty. Rather than type in data types and other attributes in the SQL statement (and making typos, and forgetting quotes and semicolons at the end of statements), customizing our fields is as easy as using some drop down menus and text fields. Enter the following attributes and fields:

Remember that an unsigned number is only positive. Know any negative number ages for pets? Also note that we allow the age field to be null in case the age of the animal cannot be determined. The first field, the ID, will be the primary key that identifies each animal. We will allow MySQL to auto increment, or automatically assign this ID each time we make a new entry.

If you want, you can add a description in the "Table Comments" field. Set the "Table Type" to MyISAM. Now click the "Save" button and let phpMyAdmin do all of the work. The next screen you see displays the raw SQL statement you would have had to type, and below that, a list of your fields and attributes in a more readable format. You'll notice also that you can select the checkbox for each field and drop (delete) it or change it.

We're most interested in adding some actual data to our table, though. Right now, it is an empty structure. Click on the "Insert" link. Go ahead and play with the actual data. I entered the data "cat," "Himalayan," "Sherpa," "6," "sweet disposition," enter date as automatically added (just like the ID), and "sherpa.jpg" for the image name. Enter a few records, and then click the browse link to view what you've got.

Let the Dance Begin

You now have your database, all dressed up and ready to use PHP scripts in a database driven Web page. MySQL is a deep database program, and the SQL language upon which it is based is full of features. You'll want to go beyond the basics as you get more involved with database design. Check out the wonderful and free resource at http://www.sqlcourse.com. Here you'll encounter a logical series of lessons, each with exercises you can practice right in your browser, with the provided online interpreter. You'll want to learn how to join relational databases, index fields for faster queries, and other more advanced topics that we didn't cover here. Also, don't forget the official MySQL Web site at http://www.mysql.org. Finally, when you want to get help with Mac OS X specific MySQL issues, the best place on the Net is Marc Liyanage's site at http://www.entropy.ch/software/macosx/mysql.

With our foundations firmly in place, we can contemplate the upcoming beauty of the dance between our database and the PHP scripting language. Tune into the next Journal to join in the performance, where we'll finally be able to put our techniques into practice.