There’s little doubt that the tremendous popularity of PHP is due in large
part to its ability to easily utilize a wide variety of powerful database
systems. Adding a database to a website can provide the means for great
dynamic content, all kinds of user interactivity and a range of flexibility and
manageability that could not easily be accomplished without it. Sounds
great, right? But what exactly is a database, you ask? And this is
the perfect time to ask…. let’s explore!
A database is a collection of data that is stored independently of the manner
in which you collect it or may wish to retrieve it. It is organized for
efficient storage and retrieval, based on the nature of the data, rather than
the collection or retrieval methods. That makes it clear as mud, right?
Let’s break it down with a couple of examples.
Do you have a "little black book" — or any good old fashioned name and
address book? If not, I hope you have at least seen one! An address
book like that is a collection of data doesn’t really fit the definition of
databases as we have come to know them. That is because it is organized
according to the manner in which information will be retrieved. Not
everybody organizes theirs in the same way. Some may choose to alphabetize
by first name, some by last name and some even by nickname. Once chosen,
however, the method of storage firmly dictates the method of retrieval. If
you stored every Barnes under "B", you would have to already know my last name
to find me. Of course you could always begin at the beginning and read
sequentially through until you found me, but that’s not very efficient —
especially in the case of looking for me as "Mr. Barnes" in a book that’s
organized by first name!
To compound the inefficiency, a computer would have to repeat the search from
the beginning for each and every such retrieval. You might remember a few
first and last name combinations as you scanned through the book, but the
computer would not, unless a program was specifically written to do so.
Typically, databases store information in tables. Tables organize data
into rows and columns. Each row represents a single record, such as one
name and address. Each column holds a part of the record, such as a first
name, or a phone number. With such an arrangement records could be rapidly
resorted based on the values found in any column (or combination of columns)
just like you can in a spreadsheet program. Thus you could efficiently
retrieve any record based on any part of the record, regardless of how it was
put into the table. Internally, database systems have technologies to make
this kind of sort/retrieve extremely efficient – but that’s a part of another
story!
Imagine a table that holds lots of names and addresses. Now imagine
another table that holds information about lots of televisions, including serial
numbers, manufacturers and models. Now imagine that each television record
is linked to the name and address record of the house in which the TV can be
found. With these links in place it would be easy to use our sort/retrieve
mechanism to provide a list of houses on Main street that have Sony TVs.
Extracting information from a database in this way is known as querying the
database.
The ability to query the database in this fashion is the root of its power.
Implementing this power on a computer system involves, surprise, surprise — a
language! In our last example, we could create a question, or query, such
as: list houses where the street name is "Main St." and the linked TV
manufacturer is "Sony". Now we have a query that has some structure!
That’s right! Creating a set of structures for queries, and defining a
simple language in which to specify those queries gives us a Structured Query
Language, SQL. Using SQL is surprisingly easy, as you will soon find out.
There are plenty of database systems out there, most of which use SQL.
If you are in a Microsoft Windows environment you will often find Access, the
Microsoft SQL Desktop Engine (MSDE) or their granddaddy, Microsoft SQL server.
When it comes to the web, however, we find MySQL all over the place. With
a price tag of zero (in your currency of choice) its affordability might well
explain this. MySql is available for most Unix/Linux flavors as well as
for Windows. Price and availability have made it my choice for a database
system to use as we continue with our PHP database examples. You will not
have to change very much, however, for each axample to be applied to virtually
any database system. Ah! – Another great advantage of database systems!
