Chapter 2: Getting Started with MySQL - Build Your Own DatabaseDriven Website Using PHP amp;amp; MySQL [Electronic resources] نسخه متنی

Chapter 2: Getting Started with MySQL

"Installation", we installed and
set up two software programs: PHP and MySQL. In this chapter, we'll learn
how to work with MySQL databases using Structured Query Language (SQL).

An Introduction to Databases

As I've already explained,
PHP is a server-side scripting language that lets you insert into your Web
pages instructions that your Web server software (be it Apache, IIS, or whatever)
will execute before it sends those pages to browsers that request 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'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 Website.

In this example, the jokes would be stored entirely in the database.
The advantages of this approach 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, adding a joke to your Website 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 that 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 "Structure of a typical database tabletables
structural overview".

Structure of a typical database table

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
identify uniquely 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 in greater depth in "Relational Database Design".

So, to review, the above is a three-column table with two rows, or entries.
Each row in the table contains three fields, one for each column in the table:
the 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.