Build Your Own Database Driven Website Using PHP & MySQL: Pt. 2

Now that's all well and good, but things really get interesting when a database is added to the mix. A database server (in our case, MySQL) is a program that can store large amounts of information in an organized format that's easily accessible through scripting languages like PHP. For example, you could tell PHP to look in the database for a list of jokes that you'd like to appear on your Web site.

In this example, the jokes would be stored entirely in the database. The advantage of this would be twofold. First, instead of having to write an HTML file for each of your jokes, you could write a single PHP file that was designed to fetch any joke out of the database and display it. Second, to add a joke to your Web site would be a simple matter of inserting the joke into the database. The PHP code would take care of the rest, automatically displaying the new joke along with the others when it fetched the list from the database.

Let's run with this example as we look at how data is stored in a database. A database is composed of one or more tables, each of which contains a list of things. For our joke database, we'd probably start with a table called "jokes" which would contain a list of jokes. Each table in a database has one or more columns, or fields. Each column holds a certain piece of information about each item in the table. In our example, our "jokes" table might have columns for the text of the jokes, and the dates on which the jokes were added to the database. Each joke that we stored in this table would then be said to be a row in the table. These rows and columns form a table that looks like this:

Notice that, in addition to columns for the joke text ("JokeText") and the date of the joke ("JokeDate"), I included a column named "ID". As a matter of good design, a database table should always provide a way to uniquely identify each of its rows. Since it's possible that a single joke could be entered more than once on the same date, the JokeText and JokeDate columns can't be relied upon to tell all the jokes apart. The function of the ID column, therefore, is to assign a unique number to each joke, so we have an easy way to refer to them, and to keep track of which joke is which. Such database design issues will be covered with greater depth in Chapter 5.

So, to review, the above is a three-column table with two rows (or entries). Each row in the table contains a joke's ID, its text, and the date of the joke. With this basic terminology under our belts, we're ready to get started with MySQL.