Web Database Applications with PHP & MySQL

Brian Donovan contributes this review of Web Database Applications with PHP & MySQL, the most recent of several books geared toward helping people use the common Linux, Apache, MySQL and PHP combination to produce database-backed websites. Read on for the review. Web Database Applications with PHP & MySQL author Hugh E. Williams and David Lane pages 563 publisher O'Reilly rating 9 reviewer Brian Donovan ISBN 0596000413 summary A comprehensive, tutorial-style roadmap for building data-driven web applications with PHP and MySQL.

PHP's speed of execution, gentle learning curve, and ease of development have contributed to its popularity, especially when teamed with MySQL, as a tool for building dynamic sites. Williams and Lane have written a thorough step-by-step guide to building web database applications with PHP and MySQL.

The Meat of the Book

Part I (Chpts 1-3) of Web Database Applications with PHP & MySQL (Web DB Apps) introduces the "Hugh and Dave's Online Wines" case study that's used to highlight the points made throughout the text and treats readers to the fundamentals of PHP, MySQL, and SQL - appropriate since the book assumes only some prior programming experience (not necessarily in PHP) and a general familiarity with HTML.

Chapters 4-9 (Part II) deal with the aspects of web application logic common to practically all data-driven sites : querying and writing to databases, maintaining state, and security. Chapter 4, "Querying Web Databases", includes a good explanation (Ex. 4-1) of the mechanics of connecting to and querying a MySQL db via PHP - numbered blocks of the example script correspond to sections in the accompanying text detailing what's happening at each point in the process (connect, query, retrieve results, process results, and close connection- unless you're using persistent db connections).

Chapter 5, "User-Driven Querying", explains how to pass data to PHP scripts using HTTP GET and POST. Although readers are initially shown parameters and parameter values being passed directly (as they are when register_globals is turned on in php.ini), the authors later explain why the same param:value pairs should instead be accessed through the global associative arrays $HTTP_GET_VARS and $HTTP_POST_VARS (the book was completed before the switch to $_GET and $_POST respectively with PHP 4.2.0) for security reasons. What the authors refer to as "combined scripts" (where the same script performs different functions depending on which, if any, variables in the GET or POST arrays, have been set, for example) are introduced and the reader is walked through the oft-used "next and previous links for query results" scenario.

In Chapter 6, "Writing to Web Databases", in addition to inserts, updates, and deletes, the authors explain one solution to the reload problem - i.e. where reloading a results page after some operation that alters the contents of the database has been performed (or even accessing a bookmarked url if HTTP GET was used to initiate the action) can potentially result in the operation being silently repeated or, if HTTP POST was used, the user being confronted with a big ugly "would you like to repost the data?" dialog. Locking (mostly how to make the best use of table-level locking) is also discussed in all of its glory. Chapter 7 deals with the validation of user input. The authors recommend and give an example implementation of dual server and client side validation (with JavaScript). Chapter 8 covers sessions (with and without cookies).

The chapter on security (Chapter 9, "Authentication and Security") mostly concerns user authentication. HTTP Authentication, managed HTTP Authentication (using PHP to validate encoded credentials from the HTTP Authorized header field), and handling your own authentication are considered, along with the security concerns inherent in stateful web apps - i.e., third party sites maliciously tricking browsers into coughing up cookies with login or session information for your site, session hijacking by feeding random session ids to the scripts until one corresponds to an existing session, etc. SSL is explained briefly.

The third and final section of Web DB Apps (Chpts 10-13) consists of a detailed examination of the guts of the wine store case study. Readers who find the commingling of application logic and html in the snippets of the wine store application discussed in the book distasteful will be gratified to know that, since publication, the authors have released a modified version of the "Hugh and Dave's Online Wines" code that uses the Xtemplate class (http://sourceforge.net/projects/xtpl/) to separate code from markup. Both versions are available in their entirety for download from the book website.

The five appendices, in turn, cover the installation and configuration of PHP, MySQL, and Apache on a Linux system, the architecture and workings of the Internet and Web, designing relational databases using entity-relationship modeling, how to define your own session handler prototypes and store session data in a database instead of files (the default), and provide an annotated list of PHP and MySQL resources (books, web sites, etc.).

The Good and the Bad

While it's clear that Web Database Applications with PHP & MySQL was written with the goal in mind of providing novice coders with a solid foundation for continued growth (or filling the niche of "handy reference" on the shelf of intermediate/advanced developers), the book manages to be comprehensive without patronizing the reader. I admit that I wouldn't have felt cheated if the authors had skipped the obligatory coverage of the history of the Internet, TCP/IP, and HTTP (Appendix B) in favor of, for instance, a discussion of web caching with an eye towards building cache-friendly apps, an important subject that all too gets short shrift from authors of web dev books. Also, some readers may be disappointed to find that the chapter on security doesn't relate to battening down your site against script kiddies and exploits, but that's really the sort of information that you should be getting from sites like PHP Advisory and Securiteam anyway.

For seasoned developers, this could be the book that you wish you'd had when you started out building web database apps and data-driven sites. Keeping a copy around for reference, especially if you frequently jump back and forth between projects in different languages/environments, also might be helpful - for those occasions when you need of a quick refresher in PHP/MySQL dev. Moreover, if you find yourself in the position of having to mentor junior developers (or helping non-coder friends) tasked with building or maintaining PHP/MySQL-based sites or apps, then lending them your copy or recommending that they buy their own could save you quite a bit of time and frustration.

Table of Contents

• Preface
• Part I
• • Chapter 1. Database Applications and the Web
  • Chapter 2. PHP
  • Chapter 3. MySQL and SQL
• Part II
• • Chapter 4. Querying Web Databases
  • Chapter 5. User-Driven Querying
  • Chapter 6. Writing to Web Databases
  • Chapter 7. Validation on the Server and Client
  • Chapter 8. Sessions
  • Chapter 9. Authentication and Security
• Part III
• • Chapter 10. Winestore Customer Management
  • Chapter 11. The Winestore Shopping Cart
  • Chapter 12. Ordering and Shipping at the Winestore
  • Chapter 13. Related Topics
• Appendix A. Installation Guide
• Appendix B. Internet and Web Protocols
• Appendix C. Modeling and Designing Relational Databases
• Appendix D. Managing Sessions in the Database Tier
• Appendix E. Resources
• Index

You can purchase Web Database Applications with PHP & MySQL from bn.com. Slashdot welcomes readers' book reviews -- to submit yours, read the book review guidelines, then hit the submission page.

Comments

don't need a book

[matt4077]

the online documentation at php.net and mysql.com is excellent, so beginner books are unneccessary.

Re:It's amazing how far this language has come

[LetterJ]

PHP didn't start out as a Perl script it was a series of little C programs.

I've read this one too....

[Grape Smuggler]

I've been entrenched with JSP heavily for the last couple of years, so I haven't familiarised myself with PHP yet. I needed a book that would show me a larger picture than I got in a web developer's job, and showed me how to put it together with more modern techniques.
This book is a perfect example of why I choose O'Reilly whenever in doubt. Chapter 1 is an overview of how web applications are put together. Chapter 2 goes through all the basic PHP syntax (stuff that would take other books several fluffy dry chapters to process). Chapter 3 gets you through all the mySQL and sql basics so you feel comfortable with that, too.
There's no 4 page tutotial entitled: "Using a text editor: Wordpad" There's also no kitch "Employee Database" example cop-out. Instead, Hugh and Dave give a realistic and usable storefront application. This book alone is probably enough for the independent web developer to get a functional site up for her/himself or a client.

Re:LAMP systems

[LetterJ]

And WAMP (Windows, Apache, MySQL and PHP) is a lot more popular than most Linux users will admit. It's a great solution for small intranets. With the improvements in Apache 2 to make it more stable on Windows, it's works well when OS isn't a variable in the equation, but a constant.

Re:Using PHP and MySQL for a website...

[AllMightyPaul]

Slashdot uses MySQL. I point you to their FAQ: What kind of hardware does Slashdot run on?

But MySQL doesn't do UTF-8...

[andersen]

Too bad MySQL doesn't do UTF-8. That is a major problem for me in adopting it. Anybody know if that is getting fixed sometime soon?

Re:I *hate* DB programming in PHP!

[the_radix]

Actually, I just migrated a database-driven website from MySQL to Oracle without a hitch, all using php.

A database abstraction layer was used which made all the difference. I used dal, which is a nice object-oriented layer that only involves changing a single line of code to change different databases.

Personally, it sounds to me like your company's problem was bad design (not allowing for expansion) rather than php.

question - not trolling

[Ender Ryan]

I am curious, what does PHP offer that Perl does not? I'm not trying to troll, or start a flame war, I just honestly don't see what PHP gives me.

One thing I see people say is that it can be imbedded into your html, but you can do that with Apache::ASP, and a bunch of others, I wrote one myself for my current job.

Plus, with mod_perl, you can write your own handlers in Perl, which is really great, not sure if you can do that with PHP...

But the number one advantage with Perl, IMHO, is the CPAN.

I just don't see what PHP offers that Perl doesn't already do better.

Now, please folks, I'm interested in hearing what PHP actually offers, I'm not looking for "Perl looks like line noise" or "PHP is Perl for girlies" type comments.

Re:question - not trolling

[FamousLongAgo]

For me, the main advantage of PHP is precisely that it has a subset of the features Perl offers. For writing web applications, it is often very handy to have a simpler syntax and fewer builtins - it makes it easier to manage code, removes the temptation to obfuscate, and makes it a lot easier to teach others how to code the web app ( a consideration for me, working with lots of student assistants). For most web applications, you need to basically do just the following:

• Keep track of users and sessions
• Print the results of database queries
• Update a database
• Use conditional logic to select what kind of HTML to display

For that stuff, Perl is overfeatured. Using PHP is simpler, you can embed it in your HTML with the syntax, and it makes for faster development.
For serious text processing, you can always exec perl programs, or pipe output/input to Perl daemons.

All that being said, mod_perl is wonderful, too, especially if you know the language already, or have a really complex web app.

Right tool, right job, etc.

Re:I *hate* DB programming in PHP!

[mborland]

I *hate* DB programming in PHP!

Me too. I uninstalled PHP a while back. The database-specific functions exemplify what is both good and bad about PHP.

BAD: Nothing is really object-oriented. Code is completely un-portable. PHP code is useless in other environments like binary or batch development (unlike Java, VB, C++, etc...and no whining about how someone's made a PHP->binary compiler...it ain't the same.)

GOOD: Nothing is really object oriented. Each of the modules (one hopes) is stripped down and about as fast as possible. In the case of database access, you're opening native connections directly the database instead of wading through ADO/ODBC/JDBC or any of the other things that abstracts your access. Another example, mailing functions...works DAMN well, and is as simple as can be (ASP, Java obviously can do it...but) from both a programming and functional standpoint.

So I suppose I'd use PHP if I had a moderately small site that I wanted to use for a fairly specific purpose, one that once I'd built I wouldn't have the time to port to something else anyway. However, if the site was something that would be maintained by a large number of people, over a long period of time, and have to integrate with a variety of databases and such, I sadly wouldn't consider PHP for a second.

Re:Why Not Java?

[Hiro Antagonist]

I'm assuming that by "Java", you mean JSP; anyone who uses an actual *Java* application to implement a *webapp* needs to be shot. Repeatedly.

That being said, there are a few reasons, actually. I do some development work in Java, but I'm also pretty well-versed in a variety of other languages; including C, C++, Perl, Assembly (x86 and good 'ole 68k), and PHP (and shell scripting, of course). Compared to PHP, JSP is a *pig*. It eats up a much larger chunk of memory, more CPU time, and is IMHO a terrible platform for developing small web-based applications. JSP+EJB does have its uses, but not in the arena of small webapps.

This is where PHP really shines; it's very fast, has a small footprint, and is *much* easier to use and debug than JSP is.

So, for writing huge, enterprise-level apps, JSP+EJB is the way to go. For writing smaller apps; things that need to be written quickly and securely, I'll stick with PHP.

"Gentle learning curve"?

[Tim Ward]

Why oh why can't people get this right??

A "steep learning curve" is one where you go up, and get to the top, quickly, ie the thing is easy to learn quickly.

A "gentle learning curve" is one that you climb up slowly over a long time, ie the thing is a right pain to learn and takes ages.

So why do people who appear to know English get these the wrong way round? Is it perhaps that they're not illiterate, they're just innumerate and haven't a clue what a graph is?

Stupid pedantic correction

[noda132]

As it says on the link in the review, $_GET, $_POST & co. were implemented in PHP 4.1.0 and not 4.2.0. Just in case anyone wants to compile a history ;).

As for the book... personally I wouldn't have found it useful (based on this review) at any stage of my PHP education. The online manual is simply too good. I really don't see what else is needed - good coding practice can be figured out with common sense - a mix and match from Perl, C, and (more and more these days, especially with Zend 2) Java.

The function reference is superb - not to mention the user-contributed notes, which as often as not have the answer to your specific question. My Galeon is, not surprisingly, outfitted with a Smart Bookmark to the function reference. Beats a book any day.