VISUALQ UICKPROG UIDE I PHP and MySQL for Dynamic Web Sites Fifth Edition Larry Ullman Visual QuickPro Guide PHP and MySQL for Dynamic Web Sites, Fifth Edition Larry Ullman Peachpit Press www.peachpit.com Copyright © 2018 by Larry Ullman To report errors, please send a note to: [email protected] Peachpit Press is a division of Pearson Education. Editor: Mark Taber Copy Editor: Elizabeth Welch Technical Reviewer: Timothy Boronczyk Production Coordinator: David Van Ness Compositor: Danielle Foster Proofreader: Scout Festa Indexer: Valerie Haynes Perry Cover Design: RHDG / Riezebos Holzbaur Design Group, Peachpit Press Interior Design: Peachpit Press Logo Design: MINE™ www.minesf.com Notice of Rights This publication is protected by copyright, and permission should be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise. For information on obtaining permission for reprints and excerpts, please complete the form at http://www.pearsoned.com/permissions/ 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 indirectly by the instructions contained in this book or by the computer software and hardware products described in it. Trademarks Visual QuickPro Guide is a registered trademark of Peachpit Press, a division of Pearson Education. MySQL is a registered trademark of MySQL AB in the United States and in other countries. Macintosh and macOS are registered trademarks of Apple, Inc. Microsoft and Windows are registered trademarks of Microsoft Corp. 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 companies, including MySQL AB. Unless otherwise indicated herein, any third party trademarks that may appear in this work are the property of their respective owners and any references to third party trademarks, logos or other trade dress are for demonstrative or descriptive purposes only. Such references are not intended to imply any sponsorship, endorsement, authorization, or promotion of Peachpit Press products by the owners of such marks, or any relationship between the owner and Peachpit Press or its affiliates, authors, licensees or distributors. ISBN-13: 978-0-13-430184-6 ISBN-10: 0-13-430184-6 1 17 Printed and bound in the United States of America Dedication Dedicated to the fine faculty at my alma mater, Northeast Missouri State University. In particular, I would like to thank Dr. Monica Barron, Dr. Dennis Leavens, Dr. Ed Tyler, and Dr. Cole Woodcox, whom I also have the pleasure of calling my friend. I would not be who I am as a writer, as a student, as a teacher, or as a person if it were not for the magnanimous, affecting, and brilliant instruction I received from these educators. Special Thanks to: My heartfelt thanks to everyone at Peachpit Press, as always. My gratitude to the fine editor on this project, Mark Taber, for leading the way and putting up with too many delayed emails and chapters! Thanks to David Van Ness and Elizabeth Welch for their hard work, helpful suggestions, and impressive attention to detail. Thanks to Scout Festa for ensuring the writing is “pixel perfect.” Thanks also to Valerie Perry for indexing and Danielle Foster for laying out the book, and thanks to Timothy Boronczyk for his technical review. Kudos to the good people working on PHP, MySQL, Apache, phpMyAdmin, MAMP, and XAMPP, among other great projects. And a hearty “cheers” to the denizens of the various newsgroups, mailing lists, support forums, etc., who offer assistance and advice to those in need. Thanks, as always, to the readers, whose support gives my job relevance. An extra helping of thanks to those who provided the translations in Chapter 17, “Example—Message Board,” and who offered up recommendations as to what they’d like to see in this edition. Finally, I would not be able to get through a single book if it weren’t for the love and support of my wife, Jessica. And a special shout-out to Zoe and Sam, who give me reasons to, and not to, write books!. Table of Contents Introduction Chapter 1 Introduction to PHP Basic Syntax Sending Data to the Browser Writing Comments What Are Variables? Introducing Strings Concatenating Strings Introducing Numbers Introducing Constants Single vs. Double Quotation Marks Basic Debugging Steps Review and Pursue Chapter 2 Programming with PHP Creating an HTML Form Handling an HTML Form Conditionals and Operators Validating Form Data Introducing Arrays For and While Loops Review and Pursue Chapter 3 Creating Dynamic Web Sites Including Multiple Files Handling HTML Forms, Revisited Making Sticky Forms Creating Your Own Functions Review and Pursue Chapter 4 Introduction to MySQL Naming Database Elements Choosing Your Column Types Choosing Other Column Properties Accessing MySQL Review and Pursue Chapter 5 Introduction to SQL Creating Databases and Tables Inserting Records Selecting Data Using Conditionals Using LIKE and NOT LIKE Sorting Query Results Limiting Query Results Updating Data Deleting Data Using Functions Review and Pursue Chapter 6 Database Design Normalization Creating Indexes Using Different Table Types Languages and MySQL Time Zones and MySQL Foreign Key Constraints Review and Pursue Chapter 7 Advanced SQL and MySQL Performing Joins Grouping Selected Results Advanced Selections Performing FULLTEXT Searches Optimizing Queries Performing Transactions Database Encryption Review and Pursue Chapter 8 Error Handling and Debugging Error Types and Basic Debugging Displaying PHP Errors Adjusting Error Reporting in PHP Creating Custom Error Handlers PHP Debugging Techniques SQL and MySQL Debugging Techniques Review and Pursue Chapter 9 Using PHP with MySQL Modifying the Template Connecting to MySQL Executing Simple Queries Retrieving Query Results Ensuring Secure SQL Counting Returned Records Updating Records with PHP Review and Pursue Chapter 10 Common Programming Techniques Sending Values to a Script Using Hidden Form Inputs Editing Existing Records Paginating Query Results Making Sortable Displays Review and Pursue Chapter 11 Web Application Development Sending Email Handling File Uploads PHP and JavaScript Understanding HTTP Headers Date and Time Functions Performing Transactions Review and Pursue Chapter 12 Cookies and Sessions Making a Login Page Making the Login Functions Using Cookies Using Sessions Improving Session Security Review and Pursue Chapter 13 Security Methods Preventing Spam Validating Data by Type Validating Files by Type Preventing XSS Attacks Using the Filter Extension Preventing SQL Injection Attacks Securing Passwords with PHP Review and Pursue Chapter 14 Perl-Compatible Regular Expressions Creating a Test Script Defining Simple Patterns Using Quantifiers Using Character Classes Finding All Matches Using Modifiers Matching and Replacing Patterns Review and Pursue Chapter 15 Introducing jQuery What Is jQuery? Incorporating jQuery Using jQuery Selecting Page Elements Event Handling DOM Manipulation Using Ajax Review and Pursue Chapter 16 An OOP Primer Fundamentals and Syntax Working with MySQL The DateTime Class Review and Pursue Chapter 17 Example—Message Board Making the Database Writing the Templates Creating the Index Page Creating the Forum Page Creating the Thread Page Posting Messages Review and Pursue Chapter 18 Example—User Registration Creating the Templates Writing the Configuration Scripts Creating the Home Page Registration Activating an Account Logging In and Logging Out Password Management Review and Pursue Appendix A Installation Installation on Windows Installation on macOS Managing MySQL Users Testing Your Installation Configuring PHP Configuring Apache Index