Learning PHP Data Objects A Beginner's Guide to PHP Data Objects, Database Connection Abstraction Library for PHP 5 Dennis Popel BIRMINGHAM - MUMBAI Learning PHP Data Objects Copyright © 2007 Packt Publishing All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews. Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, Packt Publishing, nor its dealers or distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book. Packt Publishing has endeavored to provide trademark information about all the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information. First published: September 2007 Production Reference: 2310807 Published by Packt Publishing Ltd. 32 Lincoln Road Olton Birmingham, B27 6PA, UK. ISBN 978-1-847192-66-0 www.packtpub.com Cover Image by Derek Heasley ([email protected]) [[[ FFFMMM---222 ]]] Credits Author Editorial Manager Dennis Popel Dipali Chittar Reviewers Project Coordinator Dinangkur Kundu Zenab Ismail Kapasi Tahmid Munaz Indexer Acquisition Editors Bhushan Pangaonkar Nanda Padmanabhan Viraj Joshi Proofreaders Martin Brooks Development Editor Chris Smith Rashmi Phadnis Production Coordinators Technical Editor Shantanu Zagade Swapna.V.Verlekar Manjiri Nadkarni Project Manager Cover Designer Abhijeet Deobhakta Shantanu Zagade [ FM-3 ] About the Author Dennis Popel is an experienced PHP/PHP 5 developer currently working for an Australian web development company, Motive Media (www.motivemedia.com.au). Serving Sun Microsystems Australia, Luna Park Sydney, Alsco Holdings, and Pine Solutions, among others, Dennis leads company development of proprietary, web-based, software solutions. In his spare time, he runs the www.onphp5.com blog and works on an online RSS aggregator newzmix.com. Dennis Popel has been developing with PHP for more than 5 years and is experienced in such fields as object-oriented design and MVC. Previously he has worked at Rapid Intelligence, another Australian-based web company, publisher of such popular titles as NationMaster.com, FactBites.com, and Qwika.com. In the past, Dennis was developing proprietary Java applications. This book is devoted to all the people who introduced and guided me in this wonderful world of information technology. [[[ FFFMMM---444 ]]] About the Reviewers Dinangkur Kundu completed his bachelor's degree in Information Technology from Central Queensland University, Australia. He has been working as a software engineer and network admin—designing, developing, and configuring. He has worked with a variety of 2nd, 3rd, and 4th generation languages. He has worked with flat files, indexed files, hierarchical databases, network databases, and relational databases, several Sun and HP servers to configure small and medium range office networks providing Internet service, Mail service, file share service, network- based printing service, backup service, and implementing several network-based applications. Currently, he works as Chief Technical Officer at Quantumcloud, developing and customizing LAMP- and WAMP-based web services. He enjoys producing high-quality software, web-based solutions, and designing secure network. I would like to thank my family for supporting and inspiring my ongoing passion for software development and the resultant challenges of life near the bleeding edge. I would also like to thank Mr. Jamil and Mr. Hasin, my close professional mentors and who to this day remain close friends. You can contact me at [email protected]. [ FM-5 ] Tahmid Munaz is currently working in Relisource Technologies (www.relisource.com) as an SQA Engineer. He is also a volunteer in an association called SQABD (SQA Bangladesh—www.sqabd.com) as a Community Relations Manager. He has experience in conducting QA and Testing training and mentoring freshers for Testing and QA Career paths and Consulting. He loves to keep in touch with other Technical Communities like—JPGroup, Dot_net_community, PHPExpert, and PHPResource. He is addicted to reading blogs and writing when he gets time. You can visit Tahmid's blog at http://tahmidmunaz.blogspot.com I would like to thank Hasin, the author of "Wordpress Complete", who always inspired me. Thanks to my friend Mizan, the author of "MediaWiki Administrators' Tutorial Guide", who helped me in my reviewing as it was first time for me. Thanks to the Packt team for giving me the support for this startup, especially to Viraj, Rashmi, and Abhijeet. I really enjoyed reviewing and hope to do better in future. I had heard about the author of this book but had no chance to work together. It was a chance for me to work with him and feel proud to help him make a nice book. I would like to thank my Program Managers who have always helped me to do and learn in better ways: Sahadatul Hakim (Enosis Solutions). [[[ FFFMMM---666 ]]] Table of Contents Preface 1 Chapter 1: Introduction 5 Using PDO 6 Connecting to the Database 6 Connection Strings 7 Issuing SQL Queries, Quoting Parameters, and Handling Result Sets 8 Error Handling 11 Prepared Statements 14 Appropriate Understanding of PDO 17 Summary 17 Chapter 2: Using PHP Data Objects: First Steps 19 Connection Strings 20 Creating the Sample Database 21 The Data Model 21 Creating the MySQL Database 22 Designing Our Code 24 PDO Statements and Result Sets 31 Retrieving Result Set Metadata 44 Summary 46 Chapter 3: Error Handling 47 Sources of Errors 48 Server Software Failure or Overload 48 Improper Configuration of the Application 49 Improper Validation of User Input 49 Inserting a Record with a Duplicate Primary Key or Unique Index Value 50 Syntax Errors in SQL Statements 50 Types of Error Handling in PDO 51 Defining an Error Handling Function 53 Table of Contents Creating the Edit Book Page 55 Creating the Edit Author Page 64 Securing against Uncaught Exceptions 69 Summary 70 Chapter 4: Prepared Statements 71 Prepared Statements 71 Positional and Named Placeholders 73 Prepared Statements and Bound Values 75 Working with BLOBs 80 Summary 93 Chapter 5: Handling Rowsets 95 Retrieving the Number of Rows in a Result Set 95 Limiting the Number of Rows Returned 104 Using Database-Specific SQL 104 Processing the Top N Rows Only 104 Starting at an Arbitrary Offset 105 Summary 107 Chapter 6: Advanced PDO Usage 109 Setting and Getting Connection Attributes 109 MySQL Buffered Queries 114 Connecting Using the Connection Configuration File and php.ini Setting 115 Getting the List of Available Drivers 116 Transactions 117 Summary 129 Chapter 7: An Advanced Example 131 Designing the Model 131 Modifying the Frontend to Use the Model 141 Advantages of Separating the Model 151 Further Thoughts 153 Finishing Up 154 Appendix A: Introduction to OOP in PHP5 155 What is Object-Oriented Programming? 155 The Syntax for Declaring Objects 155 Constructors 158 Destructors 160 The Advantages of OOP 161 Inheritance 162 Encapsulation 164 [ ii ] Table of Contents Polymorphism 165 Static Properties, Methods, and Class Constants 167 Exceptions 169 Summary 172 Index 173 [ iii ]