I got a request for some additional book advice this morning. I wrote so much in return I figured I would blog it:

I haven't read the O'Reilly book (Web Database Applications with PHP & MYSQL), but PHP and MYSQL Web Development (second edition) is not well organized and it's section on database programming, if followed, will have you writing sites that are vulnerable to SQL injection attack.

Advanced PHP Programming is one of the best books on PHP I have ever read. It covers database access properly and then covers a wide range of important topics.

Any book on SQL should do for the basics of MySQL programming. It's important to know that most MySQL installations lack transactions (the ability to bundle a bunch of updates into a unit and roll them back if one fails or the connection is lost). And it also lacks stored procedures (the ability to write functions that are installed in the server and can be invoked just like SELECT, INSERT, UPDATE or DELETE statements.) However, it should be fine for what you want to do. I mention the limitations only because you will run into trouble if your design depends on them and they are not there.

The biggest things to keep in mind when you are writing your application are:

• To separate the database access layer from the front end HTML layer.
• To check the IDs coming back from the user against their user ID in the session to ensure that the user is seeing only what they should be able to see. This check should be done at the database access layers. You should pass in the active users ID with every database request.
• To use PEAR DB for the database access to keep your database access vendor neutral.
• To use the '?' notation in your SQL statements to allow the database to cache your queries properly and to avoid injection attack.
• To use Smarty to create a 'code-behind' structure to your HTML pages that will separate the business logic access from the HTML formatting.
• Avoid PHP globals.
• Avoid keeping UI state (e.g. the transient form variables in a multi-page form) in the session variable. Use hidden form fields instead.

That should about do it for the basics. If you do all that you should have a high quality site.