Chapter 9: An Introduction to Databases and SQL

You've spent the last couple of chapters seeing how your PHP scripts can use external files to store and retrieve data. Although direct file access does a great job in many circumstances, it's pretty limited as data storage solutions go—what you see is what you get, no more, no less. Any sifting, sorting, or other work that has to be done on the data ultimately falls to you and your scripts, and the more data there is to handle, the longer it takes the script to deal with it. This might not present much of a problem when you're dealing with a few kilobytes of data, but who's to say when those kilobytes will grow into megabytes (or even gigabytes) of data. If you're actively setting out to build a successful, popular, data-driven Web site, you really have to allow for that volume possibility.


Just think—if a large commercial site such as Amazon accessed all its data directly from plain text files, it could take you months just to track down a single book, let alone place an order!


Databases are specifically designed to get around this problem. With their own special capabilities of organization and immaculate record keeping, you can almost imagine them as the super-hero staffed lending libraries of the IT world. No more searching for hours through shelves of musty tomes; just a word at the front desk, a blur of blue and red, and the last remaining copy of Love in the Time of Cholera appears—as if by magic—on the desk in front of you.


This is the first in a series of three chapters in which you'll explore databases and learn how you can use them to start creating powerful, efficient (and potentially very large) PHP applications. You'll examine the general advantages of using databases rather than files to store your data. You'll also learn about some of the popular databases that you're likely to come across, and how they differ.


Relational databases will be explained, and you'll explore related concepts such as normalization and indexing. You'll see how to set up MySQL, a database system that's freely available and widely used with PHP, and learn some PHP functions for connecting to a database in MySQL as well as how to use MySQL to retrieve and modify the contents of a database.


There's a lot of ground to cover in this chapter, so you may find it's a little heavier on theory than most. It won't all be brain strain, but the practical element involves getting a database up and running on your test machine, and that will be a lot easier if you already understand what's needed. For that reason, PHP itself takes something of a back seat until the last few sections, when you hook PHP up to your working database in preparation for the next two chapters. If you're someone who's already familiar with databases and how they work, you may want to skip straight on to that—otherwise, it all starts right here!