Preface - PHP & MySQL: Novice to Ninja, 5th Edition (2012)

PHP & MySQL: Novice to Ninja, 5th Edition (2012)


PHP and MySQL have changed. Back in 2001, when I wrote the first edition of this book (it was called Build Your Own Database Driven Web Site with PHP & MySQL back then), readers were astonished to discover that you could create a site full of web pages without having to write a separate HTML file for each page. PHP stood out from the crowd of programming languages, mainly because it was easy enough for almost anyone to learn and free to download and install. The MySQL database, likewise, provided a simple and free solution to a problem that, up until that point, had been solvable only by expert programmers with corporate budgets. Back then, PHP and MySQL were special—heck, they were downright miraculous! But over the years, they have gained plenty of fast-moving competition. In an age when anyone with a free WordPress account can set up a full-featured blog in 30 seconds flat, it’s no longer enough for a programming language like PHP to be easy to learn; nor is it enough for a database like MySQL to be free. Indeed, as you sit down to read this book, you probably have ambitions that extend beyond what you can throw together using the free point-and-click tools of the Web. You might even be thinking of building an exciting new point-and-click tool of your own. WordPress, after all, is built using PHP and MySQL, so why limit your vision to anything less? To keep up with the competition, and with the needs of more demanding projects, PHP and MySQL have had to evolve. PHP is now a far more intricate and powerful language than it was back in 2001, and MySQL is a vastly more complex and capable database. Learning PHP and MySQL today opens up a lot of doors that would have remained closed to the PHP and MySQL experts of 2001. That’s the good news. The bad news is that, in the same way that a butter knife is easier to figure out than a Swiss Army knife (and less likely to cause self-injury!), all these dazzling new features and improvements have indisputably made PHP and MySQL more difficult for beginners to learn. Worse yet, PHP has completely abandoned several of the beginner-friendly features that gave it a competitive advantage in 2001, because they turned out to be oversimplifications, or could lead inexperienced programmers into building websites with gaping security holes. This is a problem if you’re the author of a beginner’s book about PHP and MySQL. PHP and MySQL have changed, and those changes have made writing this book a lot more difficult. But they have also made this book a lot more important. The more twisty the path, the more valuable the map, right? In this book, I’ll provide you with a practical look at what’s involved in building a database driven website using PHP and MySQL. If your web host provides PHP and MySQL support, you’re in great shape. If not, I’ll show you how to install them on Windows, Mac OS X, and Linux computers, so don’t sweat it. This book is your map to the twisty path that every beginner must navigate to learn PHP and MySQL today. Grab your favorite walking stick; let’s go hiking!

Who Should Read This Book

This book is aimed at intermediate and advanced web designers looking to make the leap into server-side programming. You’ll be expected to be comfortable with simple HTML, as I’ll make use of it without much in the way of explanation. No knowledge of Cascading Style Sheets (CSS) or JavaScript is assumed or required, but if you do know JavaScript, you’ll find it will make learning PHP a breeze, since these languages are quite similar. By the end of this book, you can expect to have a grasp of what’s involved in building a database driven website. If you follow the examples, you’ll also learn the basics of PHP (a server-side scripting language that gives you easy access to a database, and a lot more) and Structured Query Language (SQL—the standard language for interacting with relational databases) as supported by MySQL, the most popular free database engine available today. Most importantly, you’ll come away with everything you need to start on your very own database driven site!

What’s in This Book

This book comprises the following 12 chapters. Read them in order from beginning to end to gain a complete understanding of the subject, or skip around if you only need a refresher on a particular topic.

Chapter 1: Installation

Before you can start building your database driven website, you must first ensure that you have the right tools for the job. In this first chapter, I’ll tell you where to obtain the two essential components you’ll need: the PHP scripting language and the MySQL database management system. I’ll step you through the setup procedures on Windows, Linux, and Mac OS X, and show you how to test that PHP is operational on your web server.

Chapter 2: Introducing MySQL

Although I’m sure you’ll be anxious to start building dynamic web pages, I’ll begin with an introduction to databases in general, and the MySQL relational database management system in particular. If you have never worked with a relational database before, this should definitely be an enlightening chapter that will whet your appetite for what’s to come! In the process, you’ll build up a simple database to be used in later chapters.

Chapter 3: Introducing PHP

Here’s where the fun really starts. In this chapter, I’ll introduce you to the PHP scripting language, which you can use to build dynamic web pages that present up-to-the-moment information to your visitors. Readers with previous programming experience will probably only need a quick skim of this chapter, as I explain the essentials of the language from the ground up. This is a must-read chapter for beginners, however, as the rest of this book relies heavily on the basic concepts presented here.

Chapter 4: Publishing MySQL Data on the Web

In this chapter you’ll bring together PHP and MySQL, which you’ll have seen separately in the previous chapters, to create some of your first database driven web pages. You’ll explore the basic techniques of using PHP to retrieve information from a database and display it on the Web in real time. I’ll also show you how to use PHP to create web-based forms for adding new entries to, and modifying existing information in, a MySQL database on the fly.

Chapter 5: Relational Database Design

Although you’ll have worked with a very simple sample database in the previous chapters, most database driven websites require the storage of more complex forms of data than you’ll have dealt with at this point. Far too many database driven website designs are abandoned midstream or are forced to start again from the beginning, because of mistakes made early on during the design of the database structure. In this critical chapter you’ll learn the essential principles of good database design, emphasizing the importance of data normalization. If you’re unsure what that means, then this is definitely an important chapter for you to read!

Chapter 6: Structured PHP Programming

Techniques to better structure your code are useful in all but the simplest of PHP projects. The PHP language offers many facilities to help you do this, and in this chapter, I’ll cover some of the simple techniques that exist to keep your code manageable and maintainable. You’ll learn to use include files to avoid having to write the same code more than once when it’s needed by many pages of your site, and I’ll show you how to write your own functions to extend the built-in capabilities of PHP and to streamline the code that appears within your scripts.

Chapter 7: A Content Management System

In many ways the climax of the book, this chapter is the big payoff for all you frustrated site builders who are tired of updating hundreds of pages whenever you need to make a change to a site’s design. I’ll walk you through the code for a basic content management system that allows you to manage a database of jokes, their categories, and their authors. A system like this can be used to manage simple content on your website; just a few modifications, and you’ll have a site administration system that will have your content providers submitting content for publication on your site in no time—all without having to know a shred of HTML!

Chapter 8: Content Formatting with Regular Expressions

Just because you’re implementing a nice, easy tool to allow site administrators to add content to your site without their knowing HTML, that content can still be jazzed up, instead of settling for just plain, unformatted text. In this chapter, I’ll show you some neat tweaks you can make to the page that displays the contents of your database—tweaks that allow it to incorporate simple formatting such as bold or italicized text, among other options.

Chapter 9: Cookies, Sessions, and Access Control

What are sessions, and how are they related to cookies, a long-suffering technology for preserving stored data on the Web? What makes persistent data so important in current ecommerce systems and other web applications? This chapter answers all those questions by explaining how PHP supports both cookies and sessions, and explores the link between the two. You’ll then put these pieces together to build a simple shopping cart system, as well as an access control system for your website.

Chapter 10: MySQL Administration

While MySQL is a good, simple database solution for those without the need for many frills, it does have some complexities of its own that you’ll need to understand if you’re going to rely on a MySQL database to store your content. In this section, I’ll teach you how to perform backups of, and manage access to, your MySQL database. In addition to a couple of inside tricks (like what to do if you forget your MySQL password), I’ll explain how to speed up your database when it gets slow, and how to link together the data in your database in useful ways.

Chapter 11: Advanced SQL Queries

In Chapter 5 we saw what was involved in modeling complex relationships between pieces of information in a relational database like MySQL. Although the theory was quite sound, putting these concepts into practice requires that you learn a few more tricks of Structured Query Language. In this chapter, I’ll cover some of the more advanced features of this language to help you juggle complex data like a pro.

Chapter 12: Binary Data

Some of the most interesting applications of database driven web design include some juggling of binary files. Online file storage services are prime examples, but even a system as simple as a personal photo gallery can benefit from storing binary files (that is, pictures) in a database for retrieval and management on the fly. In this chapter, I’ll demonstrate how to speed up your website by creating static copies of dynamic pages at regular intervals—using PHP, of course! With these basic file-juggling skills in hand, you’ll go on to develop a simple online file storage and viewing system, and learn the ins and outs of working with binary data in MySQL.

Where to Find Help

PHP and MySQL are moving targets, so chances are good that, by the time you read this, some minor detail or other of these technologies has changed from what’s described in this book. Thankfully, SitePoint has a thriving community of PHP developers ready and waiting to help you out if you run into trouble, and we also maintain a list of known errata for this book you can consult for the latest updates.

The SitePoint Forums

The SitePoint Forums are discussion forums where you can ask questions about anything related to web development. You may, of course, answer questions, too. That’s how a discussion forum site works—some people ask, some people answer and most people do a bit of both. Sharing your knowledge benefits others and strengthens the community. A lot of fun and experienced web designers and developers hang out there. It’s a good way to learn new stuff, have questions answered in a hurry, and just have fun. The SitePoint Forums include separate forums for PHP and MySQL:

· PHP:

· Databases & MySQL:

The Book’s Website

Located at, the website that supports this book will give you access to the following facilities:

The Code Archive

As you progress through this book, you’ll note a number of references to the code archive. This is a downloadable ZIP archive that contains each and every line of example source code that’s printed in this book. If you want to cheat (or save yourself from carpal tunnel syndrome), go ahead and download the archive.

Updates and Errata

No book is perfect, and we expect that watchful readers will be able to spot at least one or two mistakes before the end of this one. The Errata page on the book’s website will always have the latest information about known typographical and code errors.

The SitePoint Newsletters

In addition to books like this one, SitePoint publishes free email newsletters such as the SitePoint newsletter, PHPMaster, CloudSpring, RubySource, DesignFestival, and BuildMobile. In them you’ll read about the latest news, product releases, trends, tips, and techniques for all aspects of web development. Sign up to one or more of these newsletters at

Your Feedback

If you’re unable to find an answer through the forums, or if you wish to contact us for any other reason, the best place to write is We have a well-staffed email support system set up to track your inquiries, and if our support team members are unable to answer your question, they’ll send it straight to us. Suggestions for improvements, as well as notices of any mistakes you may find, are especially welcome.

Conventions Used in This Book

You’ll notice that we’ve used certain typographic and layout styles throughout this book to signify different types of information. Look out for the following items.

Code Samples

Code in this book will be displayed using a fixed-width font, like so:

<h1>A Perfect Summer's Day</h1>

<p>It was a lovely day for a walk in the park. The birds

were singing and the kids were all back at school.</p>

If the code is to be found in the book’s code archive, the name of the file will appear at the top of the program listing, like this:


.footer {

background-color: #CCC;

border-top: 1px solid #333;


If only part of the file is displayed, this is indicated by the word excerpt:

example.css (excerpt)

border-top: 1px solid #333;

If additional code is to be inserted into an existing example, the new code will be displayed in bold:

function animate() {

new_variable = "Hello";


Also, where existing code is required for context, rather than repeat all the code, a … will be displayed:

function animate() {

return new_variable;


Some lines of code are intended to be entered on one line, but we’ve had to wrap them because of page constraints. A ↵ indicates a line break that exists for formatting purposes only, and should be ignored."


Tips, Notes, and Warnings

Tip: Hey, You!

Tips will give you helpful little pointers.

Note: Ahem, Excuse Me …

Notes are useful asides that are related—but not critical—to the topic at hand. Think of them as extra tidbits of information.

Important: Make Sure You Always …

… pay attention to these important points.

Warning: Watch Out!

Warnings will highlight any gotchas that are likely to trip you up along the way.