Building a Database-Driven Web Site Using PHP and MySQL

by Kevin Yank of SitePoint.com

Part 2: Getting Started with MySQL

Hi there, and welcome back! Last week, we went through the process of installing and setting up two software programs: PHP and MySQL. This week, we'll be concentrating on the latter by learning how to work with MySQL databases using Structured Query Language (SQL).

An Introduction to Databases

As I explained briefly last week, PHP is a server-side scripting language that lets you insert instructions into your Web pages that your Web server software (be it Apache, Personal Web Server, or whatever) will execute before sending those pages to a browser that requests them. In a brief example, I showed how it was possible to insert the current date into a Web page every time it was requested.

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 is easily accessible from 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 designed to fetch any joke out of the database and display it. Second, to add a joke to your Web site would just be a matter of adding the joke to the database. The PHP code would take care of the rest by automatically displaying the new joke along with the rest when it fetched the list of jokes 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 would 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 "thing" in the database. Returning to our example, our "jokes" table might have columns for the text of the jokes and the dates 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. To see where all this terminology comes from, have a look at what this table actually looks like:

Notice that, in addition to columns for the joke text ("JokeText") and the date of the joke ("JokeDate"), I included a column named "ID". The function of this column 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.

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 using MySQL.

Article Index Introduction and Contents Part 1: Installation Part 2: Getting Started with MySQL Part 3: Getting Started with PHP Part 4: Publishing MySQL Data on the Web   Challenge Solution Buy this book in print format now

Logging onto MySQL

SitePoint.com is a fast growing Web Developer Community. Kevin Yank is the Editor of the SitePoint TechTimes, a fresh, technically oriented newsletter for the serious Webmaster.