beginning-php-and-mysql.pdf

Gilmore_552-1Front.fm Page i Wednesday, December 21, 2005 3:05 PM Beginning PHP and MySQL 5 From Novice to Professional, Second Edition W. Jason Gilmore Gilmore_552-1Front.fm Page ii Wednesday, December 21, 2005 3:05 PM...

Gilmore_552-1Front.fm Page i Wednesday, December 21, 2005 3:05 PM Beginning PHP and MySQL 5 From Novice to Professional, Second Edition W. Jason Gilmore Gilmore_552-1Front.fm Page ii Wednesday, December 21, 2005 3:05 PM Beginning PHP and MySQL 5: From Novice to Professional, Second Edition Copyright © 2006 by W. Jason Gilmore All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher. ISBN-13 (pbk): 978-1-59059-552-7 ISBN-10 (pbk): 1-59059-552-1 Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1 Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. Lead Editor: Matthew Moodie Technical Reviewer: Matthew Wade Editorial Board: Steve Anglin, Dan Appleman, Ewan Buckingham, Gary Cornell, Tony Davis, Jason Gilmore, Jonathan Hassell, Chris Mills, Dominic Shakeshaft, Jim Sumser Project Managers: Laura Cheu, Beth Christmas Copy Edit Manager: Nicole LeClerc Copy Editor: Bill McManus Assistant Production Director: Kari Brooks-Copony Production Editor: Laura Cheu Compositor: Susan Glinert Stevens Proofreader: Nancy Sixsmith Indexer: John Collin Cover Designer: Kurt Krames Manufacturing Director: Tom Debolski Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax 201-348-4505, e-mail [email protected], or visit http://www.springeronline.com. For information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219, Berkeley, CA 94710. Phone 510-549-5930, fax 510-549-5939, e-mail [email protected], or visit http://www.apress.com. The information in this book is distributed on an “as is” basis, without warranty. Although every precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in this work. The source code for this book is available to readers at http://www.apress.com in the Source Code section. Gilmore_552-1Front.fm Page iii Wednesday, December 21, 2005 3:05 PM This book is dedicated to the memory of Dr. Giovanni “Nino” Sanzi (1929–2004). Addio, caro amico. Gilmore_552-1Front.fm Page iv Wednesday, December 21, 2005 3:05 PM Gilmore_552-1Front.fm Page v Wednesday, December 21, 2005 3:05 PM Contents at a Glance About the Author............................................................. xxvii About the Technical Reviewer................................................... xxix Acknowledgments............................................................ xxxi Introduction................................................................. xxxiii CHAPTER 1 An Introduction to PHP........................................ 1 CHAPTER 2 Installing and Configuring Apache and PHP..................... 9 CHAPTER 3 PHP Basics.................................................. 43 CHAPTER 4 Functions................................................... 91 CHAPTER 5 Arrays..................................................... 103 CHAPTER 6 Object-Oriented PHP........................................ 133 CHAPTER 7 Advanced OOP Features.................................... 157 CHAPTER 8 Error and Exception Handling............................... 177 CHAPTER 9 Strings and Regular Expressions............................ 191 CHAPTER 10 Working with the File and Operating System................. 229 CHAPTER 11 PEAR...................................................... 259 CHAPTER 12 Date and Time............................................. 271 CHAPTER 13 Forms and Navigational Cues............................... 303 CHAPTER 14 Authentication............................................. 325 CHAPTER 15 Handling File Uploads....................................... 345 CHAPTER 16 Networking................................................ 359 CHAPTER 17 PHP and LDAP.............................................. 399 CHAPTER 18 Session Handlers........................................... 425 CHAPTER 19 Templating with Smarty.................................... 447 CHAPTER 20 Web Services.............................................. 473 CHAPTER 21 Secure PHP Programming................................... 515 CHAPTER 22 SQLite..................................................... 535 CHAPTER 23 Introducing PDO............................................ 555 v Gilmore_552-1Front.fm Page vi Wednesday, December 21, 2005 3:05 PM vi C O N T E N T S A T A G L A N C E CHAPTER 24 Introducing MySQL......................................... 573 CHAPTER 25 Installing and Configuring MySQL............................ 581 CHAPTER 26 The Many MySQL Clients.................................... 607 CHAPTER 27 MySQL Storage Engines and Datatypes...................... 631 CHAPTER 28 Securing MySQL............................................ 661 CHAPTER 29 PHP’s MySQL Extension..................................... 689 CHAPTER 30 PHP’s mysqli Extension..................................... 719 CHAPTER 31 Stored Routines............................................ 745 CHAPTER 32 MySQL Triggers............................................ 767 CHAPTER 33 Views...................................................... 779 CHAPTER 34 Practical Database Queries................................. 793 CHAPTER 35 Indexes and Searching...................................... 817 CHAPTER 36 Transactions............................................... 831 CHAPTER 37 Importing and Exporting Data............................... 845 INDEX.................................................................... 859 Gilmore_552-1Front.fm Page vii Wednesday, December 21, 2005 3:05 PM Contents About the Author............................................................. xxvii About the Technical Reviewer................................................... xxix Acknowledgments............................................................ xxxi Introduction................................................................. xxxiii CHAPTER 1 An Introduction to PHP....................................1 History....................................................... 1 PHP 4.................................................... 2 PHP 5.................................................... 3 General Language Features...................................... 4 Practicality................................................ 5 Power.................................................... 5 Possibility................................................. 6 Price..................................................... 7 Summary..................................................... 7 CHAPTER 2 Installing and Configuring Apache and PHP..............9 Installation.................................................... 9 Obtaining the Distributions.................................. 9 The Installation Process.................................... 11 Testing Your Installation.................................... 16 Customizing the Unix Build................................. 17 Customizing the Windows Build............................. 18 Common Pitfalls.......................................... 18 Viewing and Downloading the Documentation................. 19 Configuration................................................. 19 Managing PHP’s Configuration Directives..................... 20 PHP’s Configuration Directives.............................. 22 Summary.................................................... 42 vii Gilmore_552-1Front.fm Page viii Wednesday, December 21, 2005 3:05 PM viii C O N T E N T S CHAPTER 3 PHP Basics................................................ 43 Escaping to PHP.............................................. 43 Default Syntax............................................ 44 Short-Tags............................................... 44 Script................................................... 45 ASP-Style................................................ 45 Embedding Multiple Code Blocks............................ 45 Comments................................................... 46 Single-line C++ Syntax.................................... 46 Shell Syntax.............................................. 46 Multiple-Line C Syntax..................................... 46 Output...................................................... 47 print()................................................... 47 echo()................................................... 48 printf()................................................... 49 sprintf().................................................. 50 Datatypes.................................................... 50 Scalar Datatypes.......................................... 50 Compound Datatypes...................................... 52 Special Datatypes......................................... 53 Type Casting............................................. 54 Type Juggling............................................ 55 Type-Related Functions.................................... 56 Type Identifier Functions................................... 57 Identifiers.................................................... 57 Variables.................................................... 58 Variable Declaration....................................... 58 Variable Scope........................................... 60 PHP’s Superglobal Variables................................ 63 Variable Variables......................................... 67 Constants.................................................... 68 Expressions.................................................. 68 Operands................................................ 69 Operators................................................ 69 String Interpolation............................................ 75 Double Quotes............................................ 75 Single Quotes............................................ 76 Heredoc................................................. 77 Gilmore_552-1Front.fm Page ix Wednesday, December 21, 2005 3:05 PM C O N T E N T S ix Control Structures............................................. 78 Execution Control Statements............................... 78 Conditional Statements.................................... 79 Looping Statements....................................... 81 File Inclusion Statements................................... 86 Summary.................................................... 89 CHAPTER 4 Functions................................................. 91 Invoking a Function........................................... 91 Creating a Function........................................... 92 Passing Arguments by Value................................ 92 Passing Arguments by Reference............................ 93 Default Argument Values................................... 94 Optional Arguments....................................... 94 Returning Values from a Function............................ 95 Nesting Functions......................................... 96 Recursive Functions....................................... 97 Variable Functions........................................ 99 Function Libraries............................................ 100 Summary................................................... 101 CHAPTER 5 Arrays.................................................... 103 What Is an Array?............................................ 104 Outputting Arrays............................................ 105 Creating an Array............................................ 106 Testing for an Array.......................................... 108 Adding and Removing Array Elements........................... 109 Locating Array Elements...................................... 111 Traversing Arrays............................................ 112 Determining Array Size and Uniqueness......................... 116 Sorting Arrays............................................... 118 Merging, Slicing, Splicing, and Dissecting Arrays.................. 124 Other Useful Array Functions................................... 129 Summary................................................... 131 Gilmore_552-1Front.fm Page x Wednesday, December 21, 2005 3:05 PM x C O N T E N T S CHAPTER 6 Object-Oriented PHP..................................... 133 The Benefits of OOP.......................................... 134 Encapsulation........................................... 134 Inheritance.............................................. 134 Polymorphism........................................... 135 Key OOP Concepts........................................... 135 Classes................................................. 135 Objects................................................. 136 Fields.................................................. 137 Properties.............................................. 140 __set()................................................. 140 Constants............................................... 143 Methods................................................ 143 Type Hinting................................................ 147 Constructors and Destructors.................................. 148 Constructors............................................ 148 Destructors............................................. 151 Static Class Members........................................ 152 The instanceof Keyword....................................... 153 Helper Functions............................................. 153 Autoloading Objects.......................................... 155 Summary................................................... 156 CHAPTER 7 Advanced OOP Features................................. 157 Advanced OOP Features Not Supported by PHP................... 157 Object Cloning............................................... 158 Cloning Example......................................... 158 The __clone() Method.................................... 160 Inheritance.................................................. 161 Class Inheritance........................................ 162 Inheritance and Constructors.............................. 164 Interfaces................................................... 165 Implementing a Single Interface............................ 167 Implementing Multiple Interfaces........................... 168 Abstract Classes............................................. 168 Gilmore_552-1Front.fm Page xi Wednesday, December 21, 2005 3:05 PM C O N T E N T S xi Reflection................................................... 169 Writing the ReflectionClass Class........................... 170 Writing the ReflectionMethod Class......................... 172 Writing the ReflectionParameter Class....................... 174 Writing the ReflectionProperty Class........................ 175 Other Reflection Applications.............................. 176 Summary................................................... 176 CHAPTER 8 Error and Exception Handling........................... 177 Configuration Directives....................................... 177 Error Logging................................................ 180 Exception Handling........................................... 183 Why Exception Handling Is Handy........................... 183 PHP’s Exception-Handling Implementation................... 185 Summary................................................... 189 CHAPTER 9 Strings and Regular Expressions........................ 191 Complex (Curly) Offset Syntax.................................. 191 Regular Expressions.......................................... 192 Regular Expression Syntax (POSIX).......................... 193 PHP’s Regular Expression Functions (POSIX Extended)......... 195 Regular Expression Syntax (Perl Style)....................... 198 Other String-Specific Functions................................ 205 Determining the Length of a String......................... 205 Comparing Two Strings................................... 206 Manipulating String Case.................................. 208 Converting Strings to and from HTML....................... 209 Alternatives for Regular Expression Functions................ 214 Padding and Stripping a String............................. 222 Counting Characters and Words............................ 224 Taking Advantage of PEAR: Validate_US......................... 226 Installing Validate_US..................................... 226 Using Validate_US....................................... 227 Summary................................................... 227 Gilmore_552-1Front.fm Page xii Wednesday, December 21, 2005 3:05 PM xii C O N T E N T S CHAPTER 10 Working with the File and Operating System........... 229 Learning About Files and Directories............................ 230 Parsing Directory Paths................................... 230 File Types and Links...................................... 232 Calculating File, Directory, and Disk Sizes................... 235 Access and Modification Times............................ 238 File Ownership and Permissions................................ 239 File I/O..................................................... 242 The Concept of a Resource................................ 242 Newline................................................ 242 End-of-File.............................................. 242 Opening and Closing a File................................ 242 Reading from a File...................................... 244 Moving the File Pointer................................... 249 Writing to a File.......................................... 250 Reading Directory Contents................................ 251 Executing Shell Commands.................................... 252 PHP’s Built-in System Commands.......................... 252 System-Level Program Execution............................... 254 Sanitizing the Input....................................... 254 PHP’s Program Execution Functions......................... 255 Summary................................................... 258 CHAPTER 11 PEAR..................................................... 259 Popular PEAR Packages....................................... 259 Converting Numeral Formats............................... 261 Installing and Updating PEAR.................................. 262 Installing PEAR.......................................... 262 PEAR and Hosting Companies.............................. 263 Updating PEAR.......................................... 264 Using the PEAR Package Manager.............................. 264 Viewing Installed Packages................................ 264 Learning More About an Installed Package................... 265 Installing a Package...................................... 266 Using a Package......................................... 267 Upgrading a Package..................................... 268 Uninstalling a Package.................................... 269 Downgrading a Package.................................. 269 Summary................................................... 270 Gilmore_552-1Front.fm Page xiii Wednesday, December 21, 2005 3:05 PM C O N T E N T S xiii CHAPTER 12 Date and Time........................................... 271 The Unix Timestamp.......................................... 271 PHP’s Date and Time Library................................... 272 Date Fu..................................................... 279 Displaying the Localized Date and Time..................... 279 Displaying the Web Page’s Most Recent Modification Date..... 283 Determining the Number Days in the Current Month........... 283 Calculating the Date X Days from the Present Date............ 284 Creating a Calendar...................................... 285 PHP 5.1.................................................... 288 Date Fundamentals...................................... 289 The Date Constructor..................................... 289 Accessors and Mutators.................................. 290 Validators............................................... 293 Manipulation Methods.................................... 294 Summary................................................... 301 CHAPTER 13 Forms and Navigational Cues........................... 303 PHP and Web Forms.......................................... 303 A Simple Example........................................ 304 Passing Form Data to a Function........................... 306 Working with Multivalued Form Components................. 307 Generating Forms with PHP................................ 308 Autoselecting Forms Data................................. 310 PHP, Web Forms, and JavaScript........................... 311 Navigational Cues............................................ 313 User-Friendly URLs....................................... 313 Breadcrumb Trails....................................... 317 Creating Custom Error Handlers............................ 321 Summary................................................... 323 CHAPTER 14 Authentication........................................... 325 HTTP Authentication Concepts................................. 325 PHP Authentication........................................... 326 Authentication Variables.................................. 327 Authentication Methodologies.............................. 328 Gilmore_552-1Front.fm Page xiv Wednesday, December 21, 2005 3:05 PM xiv C O N T E N T S User Login Administration..................................... 337 Password Designation.................................... 337 Testing Password Guessability with the CrackLib Library....... 339 One-Time URLs and Password Recovery..................... 341 CHAPTER 15 Handling File Uploads................................... 345 Uploading Files via the HTTP Protocol........................... 345 Handling Uploads with PHP.................................... 346 PHP’s File Upload/Resource Directives...................... 346 The $_FILES Array....................................... 348 PHP’s File-Upload Functions............................... 349 Upload Error Messages................................... 350 File-Upload Examples..................................... 351 Taking Advantage of PEAR: HTTP_Upload........................ 355 Installing HTTP_Upload................................... 355 Learning More About an Uploaded File...................... 355 Moving an Uploaded File to the Final Destination.............. 356 Uploading Multiple Files................................... 357 Summary................................................... 358 CHAPTER 16 Networking.............................................. 359 DNS, Services, and Servers.................................... 360 DNS................................................... 360 Services................................................ 364 Establishing Socket Connections........................... 365 Mail........................................................ 367 Configuration Directives................................... 367 Sending a Plain-Text E-Mail............................... 369 Sending an E-Mail with Additional Headers................... 369 Sending an E-Mail to Multiple Recipients.................... 369 Sending an HTML-Formatted E-Mail........................ 370 Sending an Attachment................................... 371 IMAP, POP3, and NNTP....................................... 372 Requirements........................................... 373 Establishing and Closing a Connection...................... 374 Learning More About Mailboxes and Mail.................... 375 Gilmore_552-1Front.fm Page xv Wednesday, December 21, 2005 3:05 PM C O N T E N T S xv Retrieving Messages..................................... 378 Composing a Message.................................... 386 Sending a Message...................................... 387 Mailbox Administration.................................... 388 Message Administration.................................. 389 Streams.................................................... 390 Stream Wrappers and Contexts............................ 390 Stream Filters........................................... 391 Common Networking Tasks................................... 393 Pinging a Server......................................... 394 A Port Scanner.......................................... 395 Subnet Converter........................................ 395 Testing User Bandwidth................................... 397 Summary................................................... 398 CHAPTER 17 PHP and LDAP............................................ 399 An Introduction to LDAP....................................... 400 Learning More About LDAP................................ 400 Using LDAP from PHP......................................... 401 Connecting to the LDAP Server............................. 401 Binding to the LDAP Server................................ 402 Closing the LDAP Server Connection........................ 403 Retrieving LDAP Data..................................... 404 Working with Entry Values................................. 405 Counting Retrieved Entries................................ 407 Retrieving Attributes...................................... 407 Sorting and Comparing LDAP Entries........................ 410 Working with Entries..................................... 412 Deallocating Memory..................................... 415 Inserting LDAP Data...................................... 415 Updating LDAP Data...................................... 417 Deleting LDAP Data...................................... 417 Configuration Functions................................... 418 Character Encoding...................................... 420 Working with the Distinguished Name....................... 421 Error Handling........................................... 422 Summary................................................... 423 Gilmore_552-1Front.fm Page xvi Wednesday, December 21, 2005 3:05 PM xvi C O N T E N T S CHAPTER 18 Session Handlers........................................ 425 What Is Session Handling?.................................... 425 Cookies................................................ 426 URL Rewriting........................................... 426 The Session-Handling Process............................. 426 Configuration Directives....................................... 427 Key Concepts................................................ 432 Starting a Session........................................ 432 Destroying a Session..................................... 433 Retrieving and Setting the Session ID....................... 434 Creating and Deleting Session Variables..................... 434 Encoding and Decoding Session Data....................... 435 Practical Session-Handling Examples........................... 437 Auto-Login.............................................. 437 Recently Viewed Document Index........................... 439 Creating Custom Session Handlers.............................. 441 Tying Custom Session Functions into PHP’s Logic............. 442 Custom MySQL-Based Session Handlers.................... 442 Summary................................................... 445 CHAPTER 19 Templating with Smarty................................. 447 What’s a Templating Engine?.................................. 447 Introducing Smarty........................................... 449 Installing Smarty............................................. 450 Using Smarty................................................ 452 Smarty’s Presentational Logic.................................. 454 Comments.............................................. 454 Variable Modifiers........................................ 454 Control Structures........................................ 457 Statements............................................. 462 Creating Configuration Files................................... 465 config_load............................................. 465 Referencing Configuration Variables........................ 466 Using CSS in Conjunction with Smarty........................... 467 Caching.................................................... 468 Working with the Cache Lifetime........................... 468 Eliminating Processing Overhead with is_cached()............ 469 Creating Multiple Caches per Template...................... 470 Some Final Words About Caching........................... 471 Summary................................................... 471 Gilmore_552-1Front.fm Page xvii Wednesday, December 21, 2005 3:05 PM C O N T E N T S xvii CHAPTER 20 Web Services............................................ 473 Why Web Services?.......................................... 474 Real Simple Syndication...................................... 476 RSS Syntax............................................. 478 MagpieRSS............................................. 479 SimpleXML................................................. 486 SimpleXML Functions..................................... 486 SimpleXML Methods...................................... 488 SOAP...................................................... 491 NuSOAP................................................ 492 PHP 5’s SOAP Extension.................................. 502 Using a C# Client with a PHP Web Service....................... 512 Summary................................................... 514 CHAPTER 21 Secure PHP Programming............................... 515 Configuring PHP Securely..................................... 516 Safe Mode.............................................. 516 Other Security-Related Configuration Parameters............. 518 Hiding Configuration Details................................... 520 Hiding Apache and PHP................................... 520 Hiding Sensitive Data......................................... 522 Take Heed of the Document Root........................... 523 Denying Access to Certain File Extensions................... 523 Sanitizing User Data.......................................... 524 File Deletion............................................. 524 Cross-Site Scripting...................................... 524 Sanitizing User Input: The Solution.......................... 526 Data Encryption.............................................. 528 PHP’s Encryption Functions................................ 528 mhash................................................. 529 MCrypt................................................. 531 Summary................................................... 532 CHAPTER 22 SQLite.................................................... 535 Introduction to SQLite......................................... 535 Installing SQLite......................................... 536 Using the SQLite Command-Line Interface................... 536 Gilmore_552-1Front.fm Page xviii Wednesday, December 21, 2005 3:05 PM xviii C O N T E N T S PHP’s SQLite Library.......................................... 537 SQLite Directives......................................... 537 Opening a Connection.................................... 538 Creating a Table in Memory............................... 539 Closing a Connection..................................... 539 Querying a Database..................................... 540 Parsing Result Sets....................................... 541 Retrieving Result Set Details............................... 544 Manipulating the Result Set Pointer......................... 546 Learning More About Table Schemas....................... 548 Working with Binary Data................................. 549 Creating and Overriding SQLite Functions.................... 550 Creating Aggregate Functions.............................. 551 Summary................................................... 553 CHAPTER 23 Introducing PDO......................................... 555 Another Database Abstraction Layer?........................... 556 Using PDO.................................................. 557 Installing PDO........................................... 558 PDO’s Database Support.................................. 558 Connecting to a Database Server and Selecting a Database..... 559 Getting and Setting Attributes.............................. 561 Error Handling........................................... 562 Query Execution......................................... 562 Prepared Statements..................................... 564 Retrieving Data.......................................... 567 Setting Bound Columns................................... 571 Transactions............................................ 572 Summary................................................... 572 CHAPTER 24 Introducing MySQL...................................... 573 What Makes MySQL So Popular?............................... 573 Flexibility............................................... 574 Power.................................................. 574 Flexible Licensing Options................................. 576 A (Hyper) Active User Community........................... 577 MySQL 4................................................... 577 MySQL 5................................................... 578 Gilmore_552-1Front.fm Page xix Wednesday, December 21, 2005 3:05 PM C O N T E N T S xix Prominent MySQL Users...................................... 579 craigslist............................................... 579 Yahoo! Finance.......................................... 580 Wikipedia............................................... 580 Summary................................................... 580 CHAPTER 25 Installing and Configuring MySQL....................... 581 PHP and MySQL Licensing Issues............................... 581 Linux................................................... 582 Windows............................................... 582 Downloading MySQL......................................... 583 Installing MySQL............................................. 584 Linux................................................... 584 Windows............................................... 588 Set the MySQL Administrator Password......................... 591 Starting and Stopping MySQL.................................. 591 Controlling the Daemon Manually........................... 592 Starting and Stopping MySQL Automatically.................. 594 Configuring and Optimizing MySQL............................. 596 mysqld_safe............................................ 597 Configuration and Optimization Parameters.................. 597 The my.cnf File.......................................... 602 Summary................................................... 605 CHAPTER 26 The Many MySQL Clients................................ 607 Standard Client Options....................................... 607 Connection Options........................................... 608 General Options.............................................. 609 mysql...................................................... 610 Key mysql Options....................................... 610 Using mysql in Interactive Mode............................ 612 Viewing Configuration Variables and System Status........... 614 Using mysql in Batch Mode................................ 616 Useful mysql Tips........................................ 616 mysqladmin................................................. 619 mysqladmin Commands.................................. 619 Gilmore_552-1Front.fm Page xx Wednesday, December 21, 2005 3:05 PM xx C O N T E N T S The Other Utilities............................................ 621 mysqldump............................................. 621 mysqlshow............................................. 621 mysqlhotcopy........................................... 622 mysqlimport............................................ 623 myisamchk............................................. 623 mysqlcheck............................................. 624 Third-Party Client Programs................................... 624 MySQL Administrator..................................... 624 phpMyAdmin............................................ 626 MySQL Query Browser.................................... 627 Navicat................................................. 629 Summary................................................... 630 CHAPTER 27 MySQL Storage Engines and Datatypes................. 631 Storage Engines............................................. 631 InnoDB................................................. 632 MyISAM................................................ 633 MEMORY............................................... 635 MERGE................................................. 636 BDB................................................... 637 FEDERATED............................................. 637 ARCHIVE................................................ 638 CSV.................................................... 639 EXAMPLE............................................... 640 BLACKHOLE............................................. 640 Storage Engine FAQ...................................... 640 Datatypes and Attributes...................................... 642 Datatypes............................................... 642 Datatype Attributes....................................... 648 Working with Databases and Tables............................ 651 Working with Databases.................................. 651 Working with Tables...................................... 653 Altering a Table Structure................................. 656 The INFORMATION_SCHEMA............................... 656 Summary................................................... 659 Gilmore_552-1Front.fm Page xxi Wednesday, December 21, 2005 3:05 PM C O N T E N T S xxi CHAPTER 28 Securing MySQL......................................... 661 What You Should Do First..................................... 661 Securing the mysqld Daemon.................................. 662 The MySQL Access Privilege System............................ 663 How the Privilege System Works........................... 663 Where Is Access Information Stored?........................ 665 User and Privilege Management................................ 675 CREATE USER........................................... 675 DROP USER............................................. 676 RENAME USER.......................................... 676 The GRANT and REVOKE Commands........................ 676 Reviewing Privileges...................................... 682 Limiting User Resources...................................... 682 Secure MySQL Connections................................... 683 Grant Options........................................... 683 SSL Options............................................. 685 Starting the SSL-Enabled MySQL Server..................... 686 Connecting Using an SSL-Enabled Client.................... 686 Storing SSL Options in the my.cnf File....................... 686 Summary................................................... 687 CHAPTER 29 PHP’s MySQL Extension................................. 689 Prerequisites................................................ 689 Enabling the MySQL Extension on Linux..................... 689 Enabling the MySQL Extension on Windows.................. 690 User Privileges.......................................... 690 Sample Data............................................ 690 PHP’s MySQL Commands..................................... 690 Establishing and Closing a Connection...................... 691 Storing Connection Information in a Separate File............. 693 Securing Your Connection Information....................... 693 Choosing a Database......................................... 694 Querying MySQL............................................. 694 Retrieving and Displaying Data................................. 696 Inserting Data............................................... 699 Modifying Data.............................................. 701 Gilmore_552-1Front.fm Page xxii Wednesday, December 21, 2005 3:05 PM xxii C O N T E N T S Deleting Data................................................ 704 Rows Selected and Rows Affected.............................. 705 Retrieving Database and Table Information....................... 706 Retrieving Field Information.................................... 708 Viewing Table Properties.................................. 712 Retrieving Error Information............................... 713 Helper Functions............................................. 715 Summary................................................... 718 CHAPTER 30 PHP’s mysqli Extension.................................. 719 Prerequisites................................................ 720 Enabling the mysqli Extension on Unix....................... 720 Enabling the mysqli Extension on Windows................... 720 Sample Data............................................ 721 Using the mysqli Extension.................................... 721 Connecting to the MySQL Server........................... 721 Connection Error Reporting................................ 722 Selecting a MySQL Database.............................. 725 Closing a MySQL Connection.............................. 725 Queries..................................................... 726 Query Execution......................................... 726 Recuperating Query Memory............................... 728 Readying the Result Set................................... 728 Parsing Results.......................................... 730 Multiple Queries......................................... 733 Prepared Statements..................................... 735 Database Transactions........................................ 741 Summary................................................... 743 CHAPTER 31 Stored Routines.......................................... 745 Should You Use Stored Routines?............................... 745 Stored Routine Advantages................................ 746 Stored Routine Disadvantages............................. 746 How MySQL Implements Stored Routines........................ 747 Stored Routine Privilege Tables............................ 747 Creating a Stored Routine................................. 749 Declaring and Setting Variables............................ 751 Gilmore_552-1Front.fm Page xxiii Wednesday, December 21, 2005 3:05 PM C O N T E N T S xxiii Executing a Stored Routine................................ 753 Multistatement Stored Routines............................ 753 Calling a Routine from Within Another Routine................ 761 Modifying a Stored Routine................................ 761 Deleting a Stored Routine................................. 762 Viewing a Routine’s Status................................ 762 Viewing a Routine’s Creation Syntax........................ 763 Conditions and Handlers.................................. 764 Integrating Routines into Web Applications....................... 764 Creating the Employee Bonus Interface...................... 764 Retrieving Multiple Rows.................................. 765 Summary................................................... 766 CHAPTER 32 MySQL Triggers.......................................... 767 Introducing Triggers.......................................... 767 Why Use Triggers?....................................... 768 Taking Action Before an Event............................. 768 Taking Action After an Event............................... 768 Before Triggers vs. After Triggers........................... 769 MySQL’s Trigger Support...................................... 770 Creating a Trigger........................................ 771 Viewing Existing Triggers.................................. 772 Modifying a Trigger...................................... 774 Deleting a Trigger........................................ 774 Cascading Triggers....................................... 775 Integrating Triggers into Web Applications....................... 776 Summary................................................... 778 CHAPTER 33 Views..................................................... 779 Introducing Views............................................ 780 MySQL’s View Support........................................ 780 Creating and Executing Views.............................. 781 Viewing View Information................................. 786 Modifying a View......................................... 788 Deleting a View.......................................... 788 Updating Views.......................................... 788 Incorporating Views into Web Applications....................... 789 Summary................................................... 791 Gilmore_552-1Front.fm Page xxiv Wednesday, December 21, 2005 3:05 PM xxiv C O N T E N T S CHAPTER 34 Practical Database Queries.............................. 793 Sample Data................................................ 794 Creating Tabular Output with PEAR............................. 794 Installing HTML_Table.................................... 795 Creating a Simple Table................................... 795 Creating More Readable Row Output........................ 797 Creating a Table from Database Data....................... 798 Generalizing the Output Process............................ 799 Sorting Output............................................... 802 Creating Paged Output........................................ 803 Listing Page Numbers........................................ 806 Subqueries.................................................. 808 Performing Comparisons with Subqueries................... 809 Determining Existence with Subqueries..................... 809 Database Maintenance with Subqueries..................... 811 Using Subqueries with PHP................................ 811 Cursors..................................................... 812 Cursor Basics........................................... 812 Creating a Cursor........................................ 813 Opening a Cursor........................................ 813 Using a Cursor........................................... 813 Closing a Cursor......................................... 815 Using Cursors with PHP................................... 815 Summary................................................... 815 CHAPTER 35 Indexes and Searching.................................. 817 Database Indexing........................................... 817 Primary Key Indexes...................................... 818 Unique Indexes.......................................... 819 Normal Indexes.......................................... 820 Full-Text Indexes........................................ 822 Indexing Best Practices................................... 825 Forms-Based Searches....................................... 826 Performing a Simple Search............................... 826 Extending Search Capabilities.............................. 827 Performing a Full-Text Search............................. 829 Summary................................................... 830 Gilmore_552-1Front.fm Page xxv Wednesday, December 21, 2005 3:05 PM C O N T E N T S xxv CHAPTER 36 Transactions............................................. 831 What’s a Transaction?........................................ 831 MySQL’s Transactional Capabilities............................. 832 System Requirements.................................... 832 Table Creation........................................... 833 InnoDB Configuration Parameters........................... 833 A Sample Project............................................ 836 Sample Data............................................ 836 Executing an Example Transaction.......................... 837 Backing Up and Restoring InnoDB Tables.................... 839 Usage Tips.............................................. 839 Building Transactional Applications with PHP..................... 840 The Swap Meet Revisited................................. 840 Summary................................................... 843 CHAPTER 37 Importing and Exporting Data........................... 845 Sample Table............................................... 845 Attaining a Happy Medium..................................... 846 Exporting Data............................................... 846 SELECT INTO OUTFILE.................................... 847 Importing Data.............................................. 850 Importing Data with LOAD DATA INFILE...................... 850 Importing with mysqlimport................................ 853 Loading Table Data with PHP.............................. 856 Summary................................................... 857 INDEX.................................................................... 859 32d088203d70df39442d18a2c1065d0c Gilmore_552-1Front.fm Page xxvi Wednesday, December 21, 2005 3:05 PM Gilmore_552-1Front.fm Page xxvii Wednesday, December 21, 2005 3:05 PM About the Author W. JASON GILMORE has developed countless PHP and MySQL applications over the past seven years, and has dozens of articles to his credit on this and other topics pertinent to Internet application development. He has had articles featured in, among others, Linux Magazine and Developer.com, and adopted for use within United Nations and Ford Foundation educational programs. Jason is the author of three books, including most recently the best-selling Beginning PHP and MySQL: From Novice to Professional, and, with coauthor Robert Treat, Beginning PHP and PostgreSQL 8: From Novice to Professional. These days Jason splits his time between running Apress’s Open Source program, experimenting with spatially enabled Web applications, and starting more home remodeling projects than he could possibly complete. Contact Jason at [email protected], and be sure to visit his Web site at http://www.wjgilmore.com. xxvii Gilmore_552-1Front.fm Page xxviii Wednesday, December 21, 2005 3:05 PM Gilmore_552-1Front.fm Page xxix Wednesday, December 21, 2005 3:05 PM About the Technical Reviewer MATT WADE is a database analyst by day and a freelance PHP developer by night. He has extensive experience with database technologies ranging from Microsoft SQL Server to MySQL. Matt is also an accomplished systems administrator and has experience with all flavors of Windows and FreeBSD. Matt resides in Florida with his wife Michelle and three children, Matthew, Jonathan, and Amanda. He spends his (little) spare time fiddling with his aquariums, doing something at church, or just trying to catch a few winks. Matt is the founder of Codewalkers.com, which is a resource for PHP developers. xxix Gilmore_552-1Front.fm Page xxx Wednesday, December 21, 2005 3:05 PM Gilmore_552-1Front.fm Page xxxi Wednesday, December 21, 2005 3:05 PM Acknowledgments W riting a book is an enormous undertaking, and although the author’s name is the one appearing on the cover, this book would not have been possible without the efforts of numerous individuals. I’d like to thank Gary Cornell for yet another opportunity to write for the greatest computer book publisher on the planet. Assistant Publisher Dominic Shakeshaft offered unwavering support and encouragement throughout the project. Project Managers Beth Christmas and Laura Cheu demonstrated their skills for otherworldly patience and schedule wrangling while I muddled through this project. Matt Wade’s keen eye for detail resulted in vastly improved code and helped fill in more than a few blanks regarding some of PHP’s and MySQL’s undocumented features. Bill McManus diligently turned my often incoherent ramblings into a far more read- able format. Editor Matt Moodie saved what’s left of my sanity by helping out on late-stage chapter reviews. Designer-extraordinaire Kurt Krames produced yet another beautiful cover. Of course, thank you to all of the other members of the staff who do such a tremendous job not only on this but all of the Apress books. A sincere thank you is also in order for the PHP and MySQL developer communities who have worked so tirelessly over the years to advance these two truly special technologies. Last but certainly not least, I’d like to thank my family and friends just for being there, and for dragging me away from the laptop on occasion. Any errors in this book are mine and mine alone. xxxi Gilmore_552-1Front.fm Page xxxii Wednesday, December 21, 2005 3:05 PM Gilmore_552-1Front.fm Page xxxiii Wednesday, December 21, 2005 3:05 PM Introduction M ost great programming books sway far more toward the realm of the practical than of the academic. Although I have no illusions regarding my place among the great technical authors of our time, it is always my goal to write with this point in mind, producing material that you can apply to your own situation. Given the size of this book, it’s probably apparent that I attempt to squeeze out every last drop of such practicality from the subject matter. That said, if you’re interested in gaining practical and comprehensive insight into the PHP programming language and MySQL database server, and how these prominent technologies can be used together to create dynamic, database-driven Web applications, this book is for you. In the 18 months since the first edition of this book was published, the PHP and MySQL communities have continued to work feverishly to advance the capabilities of these two prom- inent technologies. Accordingly, this revision could not have come without the addition of a substantial amount of new material, to the tune of more than 100 additional pages. In total, seven new chapters have been added. Three of these chapters are devoted to PHP-specific topics, including the PHP Extension and Application Repository (PEAR), date and time functionality, and the PHP Data Objects (PDO) extension. Four additional chapters cover PHP 5’s mysqli extension, and MySQL 5’s new stored routine, trigger, and view functionality. Furthermore, all existing chapters have been carefully revised, and in some cases heavily modified, to both update and improve upon the first edition material. If you’re new to PHP, I heartily recommend beginning with Chapter 1, because first gaining fundamental knowledge of PHP will be of considerable benefit to you when reading later chapters. If you know PHP but are new to MySQL, consider beginning with Chapter 24. Intermediate and advanced readers are invited to jump around as necessary; after all, this isn’t a romance novel. Regardless of your reading strategy, I’ve attempted to compartmentalize the material found in each chapter so that you can quickly learn each topic without necessarily having to master other chapters beyond those that concentrate on the technology fundamentals. Furthermore, novice and seasoned PHP and MySQL developers alike have something to gain from this book, as I’ve intentionally organized it in a hybrid format of both tutorial and reference. I appreciate the fact that you have traded hard-earned cash for this book, and there- fore I have strived to present the material in a fashion that will prove useful not only the first few times you peruse it, but far into the future. Download the Code Experimenting with the code found in this book is the most efficient way to best understand the concepts presented within. For your convenience, a ZIP file containing all of the examples can be downloaded from http://www.apress.com. xxxiii Gilmore_552-1Front.fm Page xxxiv Wednesday, December 21, 2005 3:05 PM xxxiv I N T R O D U C T I O N Contact Me! I love reader e-mail, and invite you to contact me with comments, suggestions, and questions. Feel free to e-mail me at [email protected]. Also be sure to regularly check http://www. wjgilmore.com for links to my latest projects and articles. Gilmore 2E_552-1.book Page 1 Tuesday, November 1, 2005 1:31 PM CHAPTER 1 An Introduction to PHP T his chapter serves to better acquaint you with the basics of PHP, offering insight into its roots, popularity, and users. This information sets the stage for a discussion of PHP’s feature set, including the new features in PHP 5. By the conclusion of this chapter, you’ll learn: How a Canadian developer’s Web page hit counter spawned one of the world’s most popular scripting languages What PHP’s developers have done to once again reinvent the language, making version 5 the best yet released Which features of PHP attract both new and expert programmers alike History The origins of PHP date back to 1995, when an independent software development contractor named Rasmus Lerdorf developed a Perl/CGI script that enabled him to know how many visitors were reading his online résumé. His script performed two tasks: logging visitor information, and displaying the count of visitors to the Web page. Because the Web as we know it today was still young at that time, tools such as these were nonexistent, and they prompted e-mails inquiring about Lerdorf’s scripts. Lerdorf thus began giving away his toolset, dubbed Personal Home Page (PHP). The clamor for the PHP toolset prompted Lerdorf to continue developing the language, perhaps the most notable early change coming when he added a feature for converting data entered in an HTML form into symbolic variables, encouraging exportation into other systems. To accomplish this, he opted to continue development in C code rather than Perl. Ongoing additions to the PHP toolset culminated in November 1997 with the release of PHP 2.0, or Personal Home Page—Form Interpreter (PHP-FI). As a result of PHP’s rising popularity, the 2.0 release was accompanied by a number of enhancements and improvements from program- mers worldwide. The new PHP release was extremely popular, and a core team of developers soon joined Lerdorf. They kept the original concept of incorporating code directly alongside HTML and rewrote the parsing engine, giving birth to PHP 3.0. By the June 1998 release of version 3.0, more than 50,000 users were using PHP to enhance their Web pages. 1 Gilmore 2E_552-1.book Page 2 Tuesday, November 1, 2005 1:31 PM 2 CHAPTER 1 AN INTRODUCTION TO PHP Note 1997 also saw the change of the words underlying the PHP abbreviation from Personal Home Page to the recursive acronym Hypertext Preprocessor. Development continued at a hectic pace over the next two years, with hundreds of functions being added and the user count growing in leaps and bounds. At the beginning of 1999, Netcraft (http://www.netcraft.com/) reported a conservative estimate of a user base surpassing 1,000,000, making PHP one of the most popular scripting languages in the world. Its popularity surpassed even the greatest expectations of the developers, as it soon became apparent that users intended to use PHP to power far larger applications than was originally anticipated. Two core developers, Zeev Suraski and Andi Gutmans, took the initiative to completely rethink the way PHP operated, culminating in a rewriting of the PHP parser, dubbed the Zend scripting engine. The result of this work was found in the PHP 4 release. Note In addition to leading development of the Zend engine and playing a major role in steering the overall development of the PHP language, Zend Technologies Ltd. (http://www.zend.com/), based in Israel, offers a host of tools for developing and deploying PHP. These include Zend Studio, Zend Encoder, and Zend Optimizer, among others. Check out the Zend Web site for more information. PHP 4 On May 22, 2000, roughly 18 months after the first official announcement of the new development effort, PHP 4.0 was released. Many considered the release of PHP 4 to be the language’s official debut within the enterprise development scene, an opinion backed by the language’s meteoric rise in popularity. Just a few months after the major release, Netcraft (http://www.netcraft.com/) estimated that PHP had been installed on more than 3.6 million domains. Features PHP 4 included several enterprise-level improvements, including the following: Improved resource handling: One of version 3.X’s primary drawbacks was scalability. This was largely because the designers underestimated how much the language would be used for large-scale applications. The language wasn’t originally intended to run enterprise-class Web sites, and subsequent attempts to do so caused the developers to rethink much of the language’s mechanics. The result was vastly improved resource- handling functionality in version 4. Object-oriented support: Version 4 incorporated a degree of object-oriented functionality, although it was largely considered an unexceptional implementation. Nonetheless, the new features played an important role in attracting users used to working with traditional object-oriented programming (OOP) languages. Standard class and object development methodologies were made available, in addition to object overloading, and run-time class information. A much more comprehensive OOP implementation has been made available in version 5, and is introduced in Chapter 5. Gilmore 2E_552-1.book Page 3 Tuesday, November 1, 2005 1:31 PM CHAPTER 1 AN INTRODUCTION TO PHP 3 Native session-handling support: HTTP session handling, available to version 3.X users through the third-party package PHPLIB (http://phplib.sourceforge.net), was natively incorporated into version 4. This feature offers developers a means for tracking user activity and preferences with unparalleled efficiency and ease. Chapter 15 covers PHP’s session-handling capabilities. Encryption: The MCrypt (http://mcrypt.sourceforge.net) library was incorporated into the default distribution, offering users both full and hash encryption using encryption algorithms including Blowfish, MD5, SHA1, and TripleDES, among others. Chapter 18 delves into PHP’s encryption capabilities. ISAPI support: ISAPI support offered users the ability to use PHP in conjunction with Microsoft’s IIS Web server as an ISAPI module, greatly increasing its performance and security. Native COM/DCOM support: Another bonus for Windows users is PHP 4’s ability to access and instantiate COM objects. This functionality opened up a wide range of interoperability with Windows applications. Native Java support: In another boost to PHP’s interoperability, support for binding to Java objects from a PHP application was made available in version 4.0. Perl Compatible Regular Expressions (PCRE) library: The Perl language has long been heralded as the reigning royalty of the string parsing kingdom. The developers knew that powerful regular expression functionality would play a major role in the widespread acceptance of PHP, and opted to simply incorporate Perl’s functionality rather than reproduce it, rolling the PCRE library package into PHP’s default distribution (as of version 4.2.0). Chapter 9 introduces this important feature in great detail, and offers a general introduction to the often confusing regular expression syntax. In addition to these features, literally hundreds of functions were added to version 4, greatly enhancing the language’s capabilities. Throughout the course of this book, much of this func- tionality is discussed, as it remains equally important in the version 5 release. Drawbacks PHP 4 represented a gigantic leap forward in the language’s maturity. The new functionality, power, and scalability offered by the new version swayed an enormous number of burgeoning and expert developers alike, resulting in its firm establishment among the Web scripting behe- moths. Yet maintaining user adoration in the language business is a difficult task; programmers often hold a “what have you done for me lately?” mindset. The PHP development team kept this notion close in mind, because it wasn’t too long before it set out upon another monumental task, one that could establish the language as the 800-pound gorilla of the Web scripting world: PHP 5. PHP 5 Version 5 is yet another watershed in the evolution of the PHP language. Although previous major releases had enormous numbers of new library additions, version 5 contains improve- ments over existing functionality and adds several features commonly associated with mature programming language architectures: Gilmore 2E_552-1.book Page 4 Tuesday, November 1, 2005 1:31 PM 4 CHAPTER 1 AN INTRODUCTION TO PHP Vastly improved object-oriented capabilities: Improvements to PHP’s object-oriented architecture is version 5’s most visible feature. Version 5 includes numerous functional additions such as explicit constructors and destructors, object cloning, class abstraction, variable scope, interfaces, and a major improvement regarding how PHP handles object management. Chapters 6 and 7 offer thorough introductions to this topic. Try/catch exception handling: Devising custom error-handling strategies within structural programming languages is, ironically, error-prone and inconsistent. To remedy this problem, version 5 now supports exception handling. Long a mainstay of error manage- ment in many languages, C++, C#, Python, and Java included, exception handling offers an excellent means for standardizing your error-reporting logic. This new and convenient methodology is introduced in Chapter 8. Improved string handling: Prior versions of PHP have treated strings as arrays by default, a practice indicative of the language’s traditional loose-knit attitude toward datatypes. This strategy has been tweaked in version 5, in which a specialized string offset syntax has been introduced, and the previous methodology has been deprecated. The new features, changes, and effects offered by this new syntax are discussed in Chapter 9. Improved XML and Web Services support: XML support is now based on the libxml2 library, and a new and rather promising extension for parsing and manipulating XML, known as SimpleXML, has been introduced. In addition, a SOAP extension is now avail- able. In Chapter 20, these two new extensions are introduced, along with a number of slick third-party Web Services extensions. Native support for SQLite: Always keen on choice, the developers have added support for the powerful yet compact SQLite database server (http://www.sqlite.org/). SQLite offers a convenient solution for developers looking for many of the features found in some of the heavyweight database products without incurring the accompanying administrative overhead. PHP’s support for this powerful database engine is introduced in Chapter 22. A host of other improvements and additions are offered in version 5, many of which are introduced, as relevant, throughout the book. With the release of version 5, PHP’s prevalence is at a historical high. At press time, PHP has been installed on almost 19 million domains (Netcraft, http://www.netcraft.com/). According to E-Soft, Inc. (http://www.securityspace.com/), PHP is by far the most popular Apache module, available on almost 54 percent of all Apache installations. So far, this chapter has discussed only versio