VISUAL QUICKPRO GUIDE PHP 6 M SQL 5 AND Y FOR DYNAMIC WEB SITES Larry Ullman Peachpit Press Visual QuickPro Guide PHP6 and MySQL5 for Dynamic Web Sites Larry Ullman Peachpit Press 1249 Eighth Street Berkeley, CA94710 510/524-2178 510/524-2221 (fax) Find us on the Web at: www.peachpit.com To report errors, please send a note to: [email protected] Peachpit Press isa division of Pearson Education. Copyright © 2008 by Larry Ullman Editor: Rebecca Gulick Copy Editor: Bob Campbell Production Coordinator: Becky Winter Compositors: Myrna Vladic, Jerry Ballew, and Rick Gordon Indexer: Rebecca Plunkett Cover Production: Louisa Adair Technical Reviewer: Arpad Ray Notice of rights All rights reserved. No part of this book may be reproduced or transmitted in any form by any means, elec- tronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the pub- lisher. For information on getting permission for reprints and excerpts, contact [email protected]. Notice of liability The information in this book is distributed on an “As Is” basis, without warranty. While every precaution has been taken in the preparation of the book, neither the author nor Peachpit Press shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indi- rectly by the instructions contained in this book or by the computersoftware and hardware products described in it. Trademarks MySQL is a registered trademark of MySQL AB in the United States and in other countries. Macintosh and Mac OS X are registered trademarks of Apple Computer, Inc. Microsoft and Windows are registered trade- marks of Microsoft Corporation. Other product names used in this book may be trademarks of their own respective owners. Images of Web sites in this book are copyrighted by the original holders and are used with their kind permission. This book is not officially endorsed by nor affiliated with any of the above com- panies, including MySQL AB. Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and Peachpit was aware of a trademark claim, the designations appear as requested by the owner of the trademark. All other product names and services identified throughout this book are used in editorial fashion only and for the benefit of such companies with no intention of infringement of the trademark. No such use, or the use of any trade name, is intended to convey endorsement or other affiliation with this book. ISBN-13: 978-0-321-52599-4 ISBN-10:0-321-52599-X 9 8 7 6 5 4 3 2 1 Printed and bound in the United States of America Table of Contents Introduction: ix What Are Dynamic Web Sites? . . . . . . . . . . . . . . . . x What You’ll Need . . . . . . . . . . . . . . . . . . . . . . . . . . . xvi About This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii Companion Web Site . . . . . . . . . . . . . . . . . . . . . . . . xix Chapter 1: Introduction to PHP 1 T Basic Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 a b Sending Data to the l e Web Browser . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 o Writing Comments . . . . . . . . . . . . . . . . . . . . . . . . . . 10 f C What Are Variables? . . . . . . . . . . . . . . . . . . . . . . . . . 14 o n Introducing Strings . . . . . . . . . . . . . . . . . . . . . . . . . . 18 t e Concatenating Strings . . . . . . . . . . . . . . . . . . . . . . . 21 n Introducing Numbers . . . . . . . . . . . . . . . . . . . . . . . . 23 ts Introducing Constants . . . . . . . . . . . . . . . . . . . . . . . 27 Single vs. Double Quotation Marks . . . . . . . . . . . . 30 Chapter 2: Programming with PHP 33 Creating an HTML Form . . . . . . . . . . . . . . . . . . . . . 34 Handling an HTML Form . . . . . . . . . . . . . . . . . . . . 38 Conditionals and Operators . . . . . . . . . . . . . . . . . . 42 Validating Form Data . . . . . . . . . . . . . . . . . . . . . . . . 46 Introducing Arrays . . . . . . . . . . . . . . . . . . . . . . . . . . 52 For and While Loops . . . . . . . . . . . . . . . . . . . . . . . . 70 Chapter 3: Creating Dynamic Web Sites 73 Including Multiple Files . . . . . . . . . . . . . . . . . . . . . . 74 Handling HTML Forms, Revisited . . . . . . . . . . . . . 84 Making Sticky Forms . . . . . . . . . . . . . . . . . . . . . . . . 89 Creating Your Own Functions . . . . . . . . . . . . . . . . 92 Chapter 4: Introduction to MySQL 107 Naming Database Elements . . . . . . . . . . . . . . . . . 108 Choosing Your Column Types . . . . . . . . . . . . . . . 110 Choosing Other Column Properties . . . . . . . . . . 114 Accessing MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . 116 v Table of Contents Chapter 5: Introduction to SQL 123 Creating Databases and Tables . . . . . . . . . . . . . . . 124 Inserting Records . . . . . . . . . . . . . . . . . . . . . . . . . . . 127 Selecting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131 Using Conditionals . . . . . . . . . . . . . . . . . . . . . . . . . 133 Using LIKE and NOT LIKE . . . . . . . . . . . . . . . . . . 136 Sorting Query Results . . . . . . . . . . . . . . . . . . . . . . . 138 Limiting Query Results . . . . . . . . . . . . . . . . . . . . . 140 Updating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142 Deleting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144 Using Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146 Chapter 6: Advanced SQLand MySQL 157 Database Design . . . . . . . . . . . . . . . . . . . . . . . . . . . 158 Performing Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 Grouping Selected Results . . . . . . . . . . . . . . . . . . . 178 s t Creating Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . 180 n e Using Different Table Types . . . . . . . . . . . . . . . . . 185 t n Performing FULLTEXT Searches . . . . . . . . . . . . 188 o Performing Transactions . . . . . . . . . . . . . . . . . . . . 194 C f o Chapter 7: Error Handling and Debugging 199 e l Error Types and Basic Debugging . . . . . . . . . . . . 200 b a Displaying PHP Errors . . . . . . . . . . . . . . . . . . . . . . 206 T Adjusting Error Reporting in PHP . . . . . . . . . . . . 208 Creating Custom Error Handlers . . . . . . . . . . . . . 211 PHP Debugging Techniques . . . . . . . . . . . . . . . . . 216 SQL and MySQL Debugging Techniques . . . . . . 220 Chapter 8: Using PHPwith MySQL 223 Modifying the Template . . . . . . . . . . . . . . . . . . . . . 224 Connecting to MySQL . . . . . . . . . . . . . . . . . . . . . . 226 Executing Simple Queries . . . . . . . . . . . . . . . . . . . 230 Retrieving Query Results . . . . . . . . . . . . . . . . . . . . 239 Ensuring Secure SQL . . . . . . . . . . . . . . . . . . . . . . . 243 Counting Returned Records . . . . . . . . . . . . . . . . . 249 Updating Records withPHP . . . . . . . . . . . . . . . . . 251 Chapter 9: Common Programming Techniques 259 Sending Values to a Script . . . . . . . . . . . . . . . . . . . 260 Using Hidden Form Inputs . . . . . . . . . . . . . . . . . . 264 Editing Existing Records . . . . . . . . . . . . . . . . . . . . 270 Paginating Query Results . . . . . . . . . . . . . . . . . . . . 277 Making Sortable Displays . . . . . . . . . . . . . . . . . . . 285 vi Table of Contents Chapter 10: Web Application Development 291 Sending Email . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292 Date and Time Functions . . . . . . . . . . . . . . . . . . . 298 Handling File Uploads . . . . . . . . . . . . . . . . . . . . . . 302 PHP and JavaScript . . . . . . . . . . . . . . . . . . . . . . . . . 315 Understanding HTTP Headers . . . . . . . . . . . . . . . 322 Chapter 11: Cookies and Sessions 327 Making a Login Page . . . . . . . . . . . . . . . . . . . . . . . 328 Making the Login Functions . . . . . . . . . . . . . . . . 331 Using Cookies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336 Using Sessions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349 Improving Session Security . . . . . . . . . . . . . . . . . . 358 Chapter 12: Security Methods 361 Preventing Spam . . . . . . . . . . . . . . . . . . . . . . . . . . . 362 Validating Data by Type . . . . . . . . . . . . . . . . . . . . . 369 T a Preventing XSS Attacks . . . . . . . . . . . . . . . . . . . . . 374 b l Preventing SQL Injection Attacks . . . . . . . . . . . . 377 e o Database Encryption . . . . . . . . . . . . . . . . . . . . . . . 383 f C Chapter 13: Perl-Compatible o n Regular Expressions 389 t e n Creating a Test Script . . . . . . . . . . . . . . . . . . . . . . . 390 t s Defining Simple Patterns . . . . . . . . . . . . . . . . . . . . 394 Using Quantifiers . . . . . . . . . . . . . . . . . . . . . . . . . . . 397 Using Character Classes . . . . . . . . . . . . . . . . . . . . . 400 Finding All Matches . . . . . . . . . . . . . . . . . . . . . . . . 403 Using Modifiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407 Matching and Replacing Patterns . . . . . . . . . . . . 409 Chapter 14: Making Universal Sites 413 Character Sets and Encoding . . . . . . . . . . . . . . . . 414 Creating Multilingual Web Pages . . . . . . . . . . . . . 416 Unicode in PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . . 420 Collation in PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . 424 Transliteration in PHP . . . . . . . . . . . . . . . . . . . . . . 427 Languages and MySQL . . . . . . . . . . . . . . . . . . . . . 430 Time Zones and MySQL . . . . . . . . . . . . . . . . . . . . 434 Working with Locales . . . . . . . . . . . . . . . . . . . . . . . 437 vii Table of Contents Chapter 15: Example—Message Board 441 Making the Database . . . . . . . . . . . . . . . . . . . . . . . 442 Writing the Templates . . . . . . . . . . . . . . . . . . . . . . 451 Creating the IndexPage . . . . . . . . . . . . . . . . . . . . . 460 Creating the Forum Page . . . . . . . . . . . . . . . . . . . . 461 Creating the Thread Page . . . . . . . . . . . . . . . . . . . 466 Posting Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . 471 Chapter 16: Example—User Registration 483 Creating the Templates . . . . . . . . . . . . . . . . . . . . . 484 Writing the Configuration Scripts . . . . . . . . . . . . 490 Creating the Home Page . . . . . . . . . . . . . . . . . . . . 498 Registration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 500 Activating an Account . . . . . . . . . . . . . . . . . . . . . . 509 Logging In and Logging Out . . . . . . . . . . . . . . . . . 513 Password Management . . . . . . . . . . . . . . . . . . . . . 519 s t n Chapter 17: Example—E-Commerce 529 e t Creating the Database . . . . . . . . . . . . . . . . . . . . . . 530 n o The Administrative Side . . . . . . . . . . . . . . . . . . . . 536 C f Creating the Public Template . . . . . . . . . . . . . . . . 553 o The Product Catalog . . . . . . . . . . . . . . . . . . . . . . . . 557 e l The Shopping Cart . . . . . . . . . . . . . . . . . . . . . . . . . 569 b a Recording the Orders . . . . . . . . . . . . . . . . . . . . . . . 579 T Appendix A: Installation 587 Installation on Windows . . . . . . . . . . . . . . . . . . . . 588 Installation on Mac OS X . . . . . . . . . . . . . . . . . . . 591 MySQL Permissions . . . . . . . . . . . . . . . . . . . . . . . . 594 Testing Your Installation . . . . . . . . . . . . . . . . . . . . 598 Configuring PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . 601 Index 603 viii i Introduction Today’s Web users expect exciting pages that are updated frequently and provide a customized experience. For them, Web sites are more like communities, to which they’ll return time and again. At the same time, Web site administrators want sites that are easier to update and maintain, understanding that’s the only real way to keep up with visitors’ expectations. For these reasons and more, PHP and MySQL have become the de facto standards for creating dynamic, database-driven Web sites. This book represents the culmination of my many years of Web development experi- ence coupled with the value of having written several previous books on the technologies I n discussed herein. The focus of this book is on covering the most important knowledge t r in the most efficient manner. It will teach you how to begin developing dynamic Web o d sites and give you plenty of example code to get you started. All you need to provide u c is an eagerness to learn. t i o n Well, that and a computer. ix Introduction What Are Dynamic ◆ Often have interfaces where administra- tors can manage the site’s content Web Sites? ◆ Are easierto maintain, upgrade, and Dynamic Web sites are flexible and potent build upon than statically made sites creatures, more accurately described as There are many technologies available for applicationsthan merely sites. Dynamic creating dynamic Web sites. The most com- Web sites mon are ASP.NET (Active Server Pages, a ◆ Respond to different parameters (for Microsoft construct), JSP (Java Server Pages), example, the time of day or the version of ColdFusion, Ruby on Rails, and PHP. Dynamic the visitor’s Web browser) Web sites don’t always rely on a database, but more and more of them do, particularly ◆ Have a “memory,” allowing for user regis- as excellent databaseapplications like tration and login, e-commerce, and simi- MySQL are available at little to no cost. lar processes ◆ Almost always have HTML forms, so that ? people can perform searches, provide s e feedback, and so forth t i S b e W c i m a n y D e r A t a h W Figure i.1 The home page for PHP. x Introduction What is PHP? Also, PHP is a scripting language, as opposed to a programming language: PHP was PHP originally stood for “Personal Home designed to write Web scripts, not stand- Page” as it was created in 1994 by Rasmus alone applications (although, with some extra Lerdorf to track the visitors to his online effort, you can now create applications in résumé. As its usefulness and capabilities PHP). PHP scripts run only after an event grew (and as it started being used in more occurs—for example, when a user submits professional situations), it came to mean aform or goes to a URL. “PHP: Hypertext Preprocessor.” I should add to this definition that PHP is According to the official PHP Web site, aserver-side,cross-platformtechnology, both found at www.php.net(Figure i.1), PHP is a descriptionsbeing important. Server-side “widely-used general-purpose scripting lan- refers to the fact that everything PHP does guage thatis especially suited for Web devel- occurs on the server. AWeb server applica- opment and can be embedded into HTML.” tion, like Apache or Microsoft’s IIS (Internet It’s a long but descriptive definition, whose Information Services), is required and all meaning I’ll explain. PHP scripts must be accessed through a W Starting at the end of that statement, to say URL (http://-something). Its cross-platform h a that PHP can be embedded into HTML means nature means that PHP runs on most oper- t ating systems,including Windows, Unix A that you can take a standard HTML page, r drop in some PHP wherever you need it, and (and its many variants), and Macintosh. e More important, the PHP scripts written on D end up with a dynamic result. This attribute y one server will normally work onanother n makes PHP very approachable for anyone a that’s done even a little bit of HTML work. with little or no modification. m i c At the timethe book was written, PHP was W at version 5.2.4, with version 4.4.7 still being e b maintained. Support for version 4 is being S dropped, though, and it’s recommended that i t everyone use at least version 5 of PHP. This e s edition of this book actually focuses on ver- ? sion 6 of PHP, to be released in late 2007 or in 2008. If you’re still using version 4, you really should upgrade. If that’s not in your plans, then please grab the second edition of this book instead. If you’re using PHP 5, either the second or this edition of the book will work for you. In this edition, I will make it clear which features and functions are PHP 6–specific. xi Introduction What’s new in PHP6 Why use PHP? Because of the planned extinction of PHP 4, Put simply, when it comes to developing many users and Web hosting companies will dynamic Web sites, PHP is better, faster, and likely make a quick transition from PHP 4 to easier to learn than the alternatives. What PHP 5 to PHP 6. To discuss what’s new in you get with PHP is excellent performance, PHP 6, I’ll start with the even bigger differ- atight integration withnearly every database ences between PHP 4 and 5. available, stability, portability, and a nearly limitless feature set due to its extendibility. PHP 5, like PHP 4 before it, is a major new All of this comes at no cost (PHP is open development of this popular programming source) and with a very manageable learning language. The most critical changes in PHP 5 curve. PHP is one of the best marriages I’ve involve object-oriented programming ever seen between the ease with which (OOP).Those changes don’t really impact beginning programmers can start using it this book, as OOP isn’t covered (I do so in and the ability for more advanced program- my book PHP 5 Advanced: Visual QuickPro mers to do everything they require. Guide). With respect to this book, the ? s biggest change in PHP 5 is the addition of Finally, the proof is in the pudding: PHP has e t the Improved MySQL Extension, which is seen an exponential growth in use since its i S used to communicate with MySQL. The inception, overtaking ASP as the most pop- b e Improved MySQL Extension offers many ular scripting language being used today. It’s W benefits over the older MySQL extension the most requested module for Apache (the c i and will be used exclusively. most-used Web server), and by the time this m book hits the shelves, PHP will be on nearly a The big change in PHP 6 is support for n 25 million domains. y Unicode, which is to say that PHP can now D e handle characters in every language in the Of course, you might assume that I, as the r world. This is huge, and it’s also one of the author of a book on PHP (several, actually), A t reasons it’s taken a while to release PHP 6. have a biased opinion.Although not nearly a h What this means in terms of programming to the same extent as PHP, I’ve also devel- W is covered in Chapter 14, “Making Universal oped sites using Java Server Pages (JSP), Sites.” The information in that chapter is Ruby on Rails (RoR), and ASP.NET. Each has also used in Chapter 15, “Example—Message its pluses and minuses, but PHP is the tech- Board.” Beyond Unicode support, PHP 6 cleans nology I always return to. You might hear up a lot of garbage that was left in PHP 5 even that it doesn’t perform or scale as well as though the recommendation was not to use other technologies, but Yahoo! handles over such things. The two biggest removals are the 3.5 billion hits per day using PHP (yes, billion). “Magic Quotes” and “register globals” features. You might also wonder how secure PHP is. But security isn’t in the language; it’s in how that language is used. Rest assured that acomplete and up-to-date discussion of all the relevant security concerns is provided bythis book! xii