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.

I've said it before

[wbav]

And I'll say it again. The best way to learn php is through the php website. [php.net] Go through all the documentation. You will learn more about what actually works and what doesn't than what a book can tell you. A book is always about a version behind PHP, and so learn it through the website.

That's how I learned php 3 years ago, and well, I'm better with php than most.

Another great book on the topic

[essdodson]

I recently completed an elective course that was taught around the book "PHP and MySQL Web Development" by Luke Welling and Laura Thomson. I suggest giving this book a good look. ISBN : 0672317842

Web Application Database security links

[Anonymous Coward]

www.cgisecurity.com [cgisecurity.com]
www.owasp.org [owasp.org]
www.sqlsecurity.com [sqlsecurity.com]

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

[Skweetis]

What's great is that you don't have to use a crippled database like MySQL with PHP, there's no longstanding history or anything tying the two together. Much more capable databases (Postgres, JET, Oracle) can be used with the same amount of ease.

Amen to that. I would add a plug for ADODB [weblogs.com] or something similar for database abstraction, which makes PHP a bit more like the Perl DBI (no more separate sets of calls for each database type).

Next, while MySQL is great for small projects (and fast), it really is just a port of SQL to dbm files, and not truly relational, so it isn't great for large projects. As you mentioned, Postgres or Oracle fill this niche quite nicely (I don't really like the Oracle model for data types, but that is my personal bias). I could be mistaken here as I haven't used it much, but isn't Jet the file format used for MS Access databases? Access never seemed very robust to me.

Umm... why not just read the docs?

[Hollinger]

I learned all I ever needed to know about PHP from the PHP Manual [php.net]. MySQL also includes a somewhat monolithic html file that provides a quick reference, as long as you know SQL.

A useful little tidbit: If you want a quick way to look up information in the PHP Manual, go to http://www.php.net/whatever-you-re-looking-for. For example, http://www.php.net/mysql [php.net] will take you straight to the reference pages for MySQL.

Book has a missleading title

[Neil Watson]

While I like O'Reilly and have many of their books, this one was disappointing. It should have been called "Building E-Commerce Applications with PHP and MySQL" as most of the book focuses on building an online shopping site.

If that's what you want then it's a good book. If you just want a general overview of the different sites you can design using the php/mysql combination then I think you'll be disappointed. I was.

I *hate* DB programming in PHP!

[Just Some Guy]

In my opinion, PHP just isn't worth the hassle if you're going to be doing a lot of database work. Why? Because no two database interfaces in PHP have the same syntax or featureset! My company was switching a site from InterBase to PostgreSQL and we had to completely re-write the backend routines from scratch:

• The InterBase DBI requires you to fetch rows sequentially: while($row = $result->fetch_row){ print "
  $row->firstname\n";}
  
• The PostgreSQL DBI requires you to fetch rows by index number: $maxNum = $result->rows; for($i = 0; $i < $maxNum; $i++) {$row = $result->fetch_row($i); print "
  $row->firstname\n";}
  
• The InterBase DBI allows you to use case-insensitive hash keys: $row->FOO or $row->foo
  
• The PostgreSQL DBI requires that the hash key case be identical to the database field name: $row->tableOneISStRaNgeLYCapPED
  

If you're starting with a new project and know for a fact, beyond the shadow of a doubt, that you'll never be changing database backends, then PHP isn't too bad. If there's an possibility (however remote) that you'll ever move from, say, MySQL to PostgreSQL, then DO YOUR WORK IN PERL! I can't tell you how much I missed Perl's DBI::DBD modules - I could've completed the transition in an hour or two instead of weeks. I know that there are efforts to provide similar functionality in PHP, but it just isn't to Perl's level yet.

phpclasses.org

[djaxl]

People keep mentioning php.net [php.net]. I have to put my vote in for phpclasses.org [phpclasses.org]. No friendly tutorials here, just the code you need. Functionality ranges from basic stuff like turning recordsets into an HTML table, to more advanced things like data caching.

ADO for PHP

[Kamel Jockey]

As someone who does database coding for PHP nearly everyday, I must say the ADO interface that can be found here [weblogs.com] has been a godsend. It makes it so easy to create database independent code with minimal overhead. Of course, this package is open source :)

Re:I *hate* DB programming in PHP!

[Anonymous Coward]

Take a look at PEAR's DB abstraction layer:

1. PEAR [php.net]

Rasmus' is the best PHP book I've ever read,

[TheTomcat]

I just finished reading Programming PHP [oreilly.com], Rasmus Lerdorf's latest co-authored book.

It's by far the most concise, useful, and down-and-dirty books I've ever read on PHP. Even the usually-useless PHP function reference in this books is a step above the norm.

The book talks about important things like PDF creation, the GD library, and how to extend PHP. Setting up and connecting to a DB is kept to a minimum. Kudos to the man.

S

Database abstraction classes are available

[UsonianAutomatic]

Not sure if these are the efforts you're referring to but they're available for some of the more widely used backends (MySQL, Postgres, DB2, ODBC).

Yes, there are arguments to be made against DB abstraction layers if you're using very specific features on one platform that might not be available on another (e.g. Postgres' foreign keys and subselects vs. Mysql's lack of them (er, last time I used MySQL anyway))

But if you're doing fairly run of the mill SQL stuff, check out the PEAR DB class [php.net] or ADOdb [weblogs.com]. Either one implements a standard set of methods for interacting with databases regardless of the backend.

It's easy

[Anonymous Coward]

That's basically it. Someone who has never programmed before can get started with php really quickly and easily. First you can do a simple , then you can move into the fun conditional world of if and else. In no time, you have a little php application running. Throw in some mysql access, and voila. When you need some more advanced features, they're there for you. Lots of times I've thought "I wonder if PHP can do x..." and then I look, and indeed it can.

Perl often seems quite scary to beginners. PHP doesn't.

Agreed. Another one I used:

[twilight30]

MySQL/PHP4 Database Applications [amazon.co.uk], by Jay Greenspan and Brad Bulger, Hungry Minds, ISBN 0764535374 .

Welling and Thomson's book is a good reference for those who want to get to grips with practical projects straight off the bat. It includes webmail, shopping cart, session control, and web-forum/weblog applications as a matter of course, and begins with a sturdy look at PHP first, moving to MySQL once the basics are covered.

Greenspan and Bulger's text is perhaps more traditionally concerned with constructing databases and the programming that surrounds them. Both books cover the material equally well, though I found some nuisances in the first book.

Re:Book has a missleading title

[neafevoc]

I checked out WROX's Professional PHP4. It had everything I wanted to do... create an email and news clients. It deals how to make an FTP client. It talks about use with MySQL and PostgreSQL. I found it rather helpful (along with php.net's documentation and user notes). Bah, here's an overview [wrox.com].

Re:aminamals

[Anonymous Coward]

platypus

embedded perl

[SCHecklerX]

I prefer using embedded perl to PHP, plus you get all the goodness of mod_perl speed. You can also use any standard perl module in your web pages then. Check it out:

http://perl.apache.org/embperl/ [apache.org]