Practical PHP and MySQL Web Site Databases: A Simplified Approach (2013)

Introduction

The Teaching Method

I am a web site designer rather than a programmer. My choice of a book is based on how much practical application it contains, not on how much a book concentrates on the syntax of a language as an end in itself. This book follows my preference; therefore, Practical PHP and MySQL Web Site Databases - A Simplified Approach uses a different way of teaching web site database design than the majority of manuals. The usual layout starts with several lessons on PHP followed by snippets of command-line code, and it may eventually conclude with a project or two. This book abandons that approach. The primary focus is on fully worked, practical MySQL database projects built into real-world web pages.

In this book, practical databases and interactive web pages are presented as early as possible; in fact, you will create a database and a table in the first chapter. In the second chapter, you will embed a database into an interactive (dynamic) web page and test it. Each subsequent chapter will introduce you to increasingly sophisticated and useful database-driven web site pages.

To a busy web designer who is unfamiliar with PHP and databases, the requirements in order of importance are as follows:

· How to embed PHP and interactive databases into real-world web pages. This is the primary theme throughout the book.

· How to create a free environment for testing database-driven web pages.

· How to create a user-friendly interface so that an administrator with minimum computer skills can monitor the database.

· To understand how PHP, HTML, and MySQL work together to create and maintain a database and its data.

Instead of presenting PHP, SQL, and MYSQL as completely separate topics, these are explained in context as the projects unfold. However, when you eventually become proficient in these languages, a quick PHP/MySQL reference would be helpful; therefore, you will find this in the Appendix.

Because databases can only be viewed and tested on a server, the first part of Chapter 1 has instructions for using a free server that can be downloaded and installed on the reader’s computer. This ensures that readers will have a safe development platform for learning and testing as they explore the book’s practical projects.

A study of the theory and syntax can deter learners and prolong the time until they get their hands on a practical application. The history and development of the car and a study of the internal combustion engine will not help a would-be driver. However, jumping into a car and driving it will produce quicker results, and learners are enthused when they achieve something. This book jumps into the database driving seat right from the beginning. Essential PHP and MySQL techniques are presented in context within each tutorial, where they are most relevant.

Some database text books advocate using a framework; they suggest that this facilitates the development of a database-driven web site. I find frameworks utterly confusing, even though I have experience programming databases using raw code. If you are a beginner, I suggest you steer clear of frameworks until you have gained some more experience and understand the fundamentals of how a database-driven website works.

Who Is This Book For?

The book assumes that the reader is thoroughly familiar with HTML and CSS. However, concerning MySQL, PHP, and phpMyAdmin, the book starts from an absolute beginner’s point of view. As the chapters unfold, they progress towards intermediate level. Because command-line programming would not be welcomed by the modern generation of readers, the book concentrates on mouse-operated Graphical User Interfaces (GUIs) and PHP files for creating and managing databases.

You do not need to acquire an extensive knowledge of PHP to create interactive databases. I introduce all the PHP you will need in the appropriate place within each project. Each piece of PHP code is explained fully in plain English. The step-by-step, fully-worked examples will show you what MySQL and PHP can do and how to do it. Practical PHP and MySQL Web Site Databases - A Simplified Approach is for web designers who wish to begin developing database-driven web sites. Like the author, they may have struggled with the current manuals and despaired. They may also have been frustrated by the limitations of paint-by-numbers content management systems such as Joomla and Wordpress.

With this in mind, Practical PHP and MySQL Web Site Databases - A Simplified Approach uses a highly motivational, step-by-step approach. The author recognizes fully that a sense of achievement encourages the reader to look forward eagerly to the next step. For readers who have little or no knowledge of PHP, the book will teach enough PHP to complete all the projects in the book. Web developers who are ready to move beyond the MySQL basics, or who have not kept up to date with their MySQL and PHP, will also benefit from Practical PHP and MySQL Web Site Databases - A Simplified Approach.

College and university IT teachers will find that the book provides an excellent set text; the projects can form a basis for students to adapt for their course work.

The “Quick and Easy-to-Learn” Myth

Manuals frequently state that PHP and MySQL databases are easily and quickly learned. This discourages beginners, because when they are confronted with the inevitable difficulties (and error messages), they begin to think that they will never grasp even the basic principles.

Beginners should not be discouraged if they remember the following fact: authors claiming that PHP and MySQL are easily and quickly learned are not being deceptive; they have probably been using PHP and MySQL for more than a decade and have forgotten the difficulties they encountered when they first began.

If you accept that some time and effort is required to learn PHP and MySQL, then as time passes, it will become increasingly apparent that you are learning something very worthwhile. The task will become progressively easier, so have patience and persevere. You will then begin to enjoy mastering this valuable new discipline.

The Origin of This Book

I was asked to enhance one of my client’s web sites by adding a membership database and a members’ registration form. Although I have designed, developed, and maintained web sites for many years, I had no knowledge of MySQL databases. I bought and borrowed a boatload of books and searched the Internet for tutorials. I was very disappointed with the majority of the books.

Most of the MySQL manuals tended to demonstrate the author’s deep and extensive knowledge of PHP and MySQL instead of teaching how to embed MySQL databases into web pages. In contrast , this book uses fully worked examples to demonstrate how to integrate databases into a web site.

PHP/MySQL manuals can have up to 800 pages, which would deter any beginner. I once bought such a manual. It contained hardly any practical worked examples. This meant that I could only use the book to stand on when changing a light bulb. (The manual was almost 2 inches thick.)

The many MySQL database manuals that I own (or borrowed) were unnecessarily complicated. The authors had become used to using neat tricks and shortcuts that were second nature to them. These cluttered the code, making it difficult for beginners to discern the basic structure.

Practical PHP and MySQL Web Site Databases - A Simplified Approach avoids this mistake. A few useful tricks are introduced gradually and are fully explained in plain English. I based the book on a quote from the composer Brahms, who said:

It is easy to compose but wonderfully hard to let the superfluous notes fall under the table.

MySQL manuals are nearly always written assuming that the web designer will administer the databases. Smaller e-commerce web sites, clubs, and societies cannot afford to do this and would prefer that their membership secretary was able to administer the database using a user-friendly interface. Of course, the web designer should always be available for major administrative jobs, such as adding a new column or a table. The majority of the databases created in this book can be administered by both an unskilled membership secretary and the web designer.

Some manuals provide instructions using only MS-DOS style command-lines. Having used a GUI (mouse-operated system) from the start of their computing experience, anyone under the age of 40 would not know what an MS-DOS command line was. Some manuals published in 2012 still use command-line listings. What would a beginner make of the sort of code shown in Figure 1?

Figure 1. An example of the command-line code that would frighten most beginners

Sometimes I had as many as seven MySQL/PHP manuals open at the same time to piece together enough information to complete a simple task. In parallel, I ran Internet searches to supplement the most obscure manuals; sadly, some forums tended to deal more with paint-by-numbers (CMS) web sites rather than HTML web sites. Only two of the seven manuals took the trouble to embed its databases into real-world web pages.

Eventually, I concluded that I must write my own manual based on what I could learn by concatenating snippets of information from multiple resources. I also based the manual on my own trial-and-error approach as a raw beginner. This automatically ensured that the manual’s content was presented in simple, logical, and progressive steps without suddenly introducing unexplained items.

My home-grown manual was so useful that I decided that it should be shared with other web site designers. This book is the result of that decision.

Computer software and database techniques are constantly improving and being updated. Because of this, most of the available manuals and Internet tutorials were obsolescent, so I had to research the latest versions of the scripts, tools, and the available software. This ensured that my content and illustrations would remain relevant for as long as possible.

To follow the tutorials in this book requires an absolute minimum of software. Some manuals ask readers to download and learn a new piece of software before they can proceed to each new chapter. I came across one book that required readers to download MySQL, Apache, PHP, phpMyAdmin, Prototype 1.5, Scriptaculous, Zend Framework, Smarty Template Engine, FCK editor, Jquery, and Ajax. In this book, in addition to a code editor, I have limited the software to one item as described next.

What Equipment Is Required

The book assumes that, as a web designer, you will already have an HTML editor such as Dreamweaver, MS Expression Web (now free), Kompozer (free), or NotePad++ (free). I used MS Expression Web because it was about one third of the price of Dreamweaver and it used an interface similar to MS Word. I was considering an update to my Expression Web, but it would cost about £199, which deterred me. Then, suddenly, Microsoft decided to discontinue the development and maintenance of Expression Web and offered it free of charge. Naturally, I was delighted and promptly downloaded the latest version of Expression Web Version 4; I can thoroughly recommend it.

In addition you will need:

· A notebook for recording the passwords and file names for your databases and table entries. DON’T RELY ON MEMORY; WRITE EVERYTHING DOWN

You will need to download:

· The sample code from the book’s page, available at www.apress.com.

· XAMPP, a free, all-in-one package for testing your work.

· The latest browsers (all free): Internet Explorer, Mozilla Firefox, Safari, Chrome, and Opera.

The Conventions Used in This Book

Care has been taken to relate every listing to its screenshot. For instance, Figure 3-6 will be described by Listing 3-6. If two listings are needed, such as the HTML code and the CSS, both will relate to the screenshot by using Listing 3-6a and Listing 3-6b. If a screenshot such as Figure 4-6 does not need a listing, the next screenshot and listing will use Figure 4-7 and Listing 4-7.

Special tips, notes, and warnings are shown in the following format:

Note Security is very important when dealing with databases, especially if they contain personal data. The technique for making your work secure is woven into each step of the instructions.

All code listings use HTML5 and PHP. The meta description and meta keywords have been omitted from each <head></head> section to save space.

Code listings are shown as follows:

Code shown in bold type indicates either a new feature or a change from a previous version of the code.

Code lines are sometimes numbered to help with the explanations as follows:

if (empty($errors)) { // If no problems occured, register the user in the database #1

The line numbers are for explanation only and must not be included in your own code.

Interactive vs. Dynamic

Most manuals use the term “dynamic” web pages when referring to interactive pages. The words dynamic and interactive both describe pages that provide a live link between a user and a database. For instance, a user can register for membership and view his/her account details. A membership secretary can view a table of members, but the table is hidden from ordinary members. Because the word dynamic can have so many connotations and meanings, I have chosen to use the more precise term interactive in this book.