Build your own database driven web site using php

Chia sẻ: Phung Tuyet | Ngày: | Loại File: PDF | Số trang:0

Thêm vào BST

Báo xấu

66
lượt xem 7
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Build Your own Database Driven Web Site Using PHP & MySQL is a practical hands-on guide to learning all the tools, principles and techniques needed to build a fully functional database-driven web site using PHP & MySQL. This book covers everything from installing PHP & MySQL under Windows, Linux, and Mac through to building a live web-based content management system.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Build your own database driven web site using php

www.it-ebooks.info ALL SOURCE CODE AVAILABLE FOR DOWNLOAD Grey scale CMYK: Pantone: Pantone: Black 50% CMYK O, 53, 100, 0 PANTONE Orange 021 C TEACH YOURSELF PHP & MYSQL CMYK: Black 100% CMYK 100, 45, 0, 37 PANTONE 2955 C THE EASY WAY ... BUILD YOUR OWN PHP & MYSQL Grey scale DATABASE Build Your Own Database Driven Web Site Using PHP & MySQL WHAT’S INSIDE? is a practical hands-on guide to learning all the tools, principles, and techniques needed to build a fully functional database driven web site using PHP & MySQL. This book covers everything from DRIVEN WEB SITE installing PHP and MySQL on Windows, Linux, and Mac computers PANTONE Orange 021 C PANTONE 2955 C CMYK O, 53, 100, 0 CMYK 100, 45, 0, 37 through to building a live, web-based content management system. Black 50% Black 100% You’ll learn how to: USING PHP & MYSQL Install PHP 5 & MySQL 5 on Windows, Linux, or Mac OS X Detailed installation instructions Gain a thorough understanding of PHP syntax BY KEVIN YANK Master database design principles and SQL Build a working content management system 4TH EDITION Add, edit, and delete web content without using HTML Build an ecommerce shopping cart Utilize sessions and cookies to track site visitors DATABASE DRIVEN WEB SITE Craft SEO-friendly and memorable URLs Easy-to-understand diagrams And a whole lot more ... ABOUT KEVIN YANK Kevin Yank is a world-renowned leader in web development. When not writing best sellers, Kevin is the Technical Director of sitepoint.com and editor of the popular SitePoint Tech Times newsletter. Kevin has also co-authored BUILD YOUR OWN Simply JavaScript and Everything You Know About CSS Is Wrong! Practical code examples USING PHP & MYSQL SITEPOINT BOOKS WEB PROGRAMMING ISBN: 978-0-9805768-1-8 Advocate best practice techniques Lead you through practical examples Provide working code for your web site Make learning easy and fun LEARNING PHP & MYSQL HAS NEVER BEEN SO EASY! CAD $49.95 USD $39.95 Visit us on the Web at sitepoint.com or for sales and support email books@sitepoint.com YANK Licensed to botuongxulang@yahoo.com phpmysql4.indd 1 5/28/2009 5:51:24 PM
www.it-ebooks.info Summary of Contents Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix 1. Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 2. Introducing MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 3. Introducing PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 4. Publishing MySQL Data on the Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 5. Relational Database Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151 6. Structured PHP Programming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 7. A Content Management System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197 8. Content Formatting with Regular Expressions . . . . . . . . . . . . . . . . . . . . . . 241 9. Cookies, Sessions, and Access Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261 10. MySQL Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313 11. Advanced SQL Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337 12. Binary Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357 A. MySQL Syntax Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389 B. MySQL Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415 C. MySQL Column Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435 D. PHP Functions for Working with MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . 449 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463 Licensed to botuongxulang@yahoo.com
www.it-ebooks.info BUILD YOUR OWN DATABASE DRIVEN WEB SITE USING PHP & MYSQL BY KEVIN YANK 4TH EDITION Licensed to botuongxulang@yahoo.com
www.it-ebooks.info iv Build Your Own Database Driven Web Site Using PHP & MySQL by Kevin Yank Copyright © 2009 SitePoint Pty. Ltd. Managing Editor: Chris Wyness Editor: Kelly Steele Technical Editor: Andrew Tetlaw Cover Design: Alex Walker Indexer: Russell Brooks Printing History: Latest Update: July 2009 1st Ed. Aug. 2001, 2nd Ed. Feb. 2003, 3rd Ed. Oct. 2004 Fourth Edition: July 2009 Notice of Rights All rights reserved. No part of this book may be reproduced, stored in a retrieval system or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews. Notice of Liability The author and publisher have made every effort to ensure the accuracy of the information herein. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors and SitePoint Pty. Ltd., nor its dealers or distributors will be held liable for any damages to be caused either directly or indirectly by the instructions contained in this book, or by the software or hardware products described herein. Trademark Notice Rather than indicating every occurrence of a trademarked name as such, this book uses the names only in an editorial fashion and to the benefit of the trademark owner with no intention of infringement of the trademark. Published by SitePoint Pty. Ltd. 48 Cambridge Street Collingwood VIC Australia 3066. Web: www.sitepoint.com Email: business@sitepoint.com ISBN 978-0-9805768-1-8 Printed and bound in the United States of America Licensed to botuongxulang@yahoo.com
www.it-ebooks.info v About the Author As Technical Director for SitePoint, Kevin Yank keeps abreast of all that is new and exciting in web technology. Best known for the book you are reading right now, he also co-authored Simply JavaScript (http://www.sitepoint.com/books/javascript1/) with Cameron Adams and Everything You Know About CSS Is Wrong! (http://www.sitepoint.com/books/csswrong1/ ) with Rachel Andrew. In addition, Kevin hosts the SitePoint Podcast (http://www.sitepoint.com/podcast/) and writes the SitePoint Tech Times, a free email newsletter that goes out to over 240,000 subscribers worldwide. Kevin lives in Melbourne, Australia and enjoys speaking at conferences, as well as visiting friends and family in Canada. He’s also passionate about performing improvised comedy theater with Impro Melbourne (http://www.impromelbourne.com.au/) and flying light aircraft. Kevin’s personal blog is Yes, I’m Canadian (http://yesimcanadian.com/). About the Technical Editor Andrew Tetlaw has been tinkering with web sites as a web developer since 1997. At SitePoint he is dedicated to making the world a better place through the technical editing of SitePoint books, kits, articles, and newsletters. He is also a busy father of five, enjoys coffee, and often neglects his blog at http://tetlaw.id.au/. About SitePoint SitePoint specializes in publishing fun, practical, and easy-to-understand content for Web professionals. Visit http://www.sitepoint.com/ to access our blogs, books, newsletters, articles, and community forums. Licensed to botuongxulang@yahoo.com
www.it-ebooks.info Licensed to botuongxulang@yahoo.com
www.it-ebooks.info To my parents, Cheryl and Richard, for making all this possible. Licensed to botuongxulang@yahoo.com
www.it-ebooks.info Licensed to botuongxulang@yahoo.com
www.it-ebooks.info Table of Contents Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix Who Should Read This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xx What’s in This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi Where to Find Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiv The SitePoint Forums . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiv The Book’s Web Site . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiv The SitePoint Newsletters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv Your Feedback . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv Conventions Used in This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvi Code Samples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvi Tips, Notes, and Warnings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvii Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Chapter 1 Your Own Web Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Windows Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 All-in-one Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Installing Individual Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Mac OS X Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 All-in-one Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Installing Individual Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Linux Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Installing MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Installing PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 Post-Installation Set-up Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 What to Ask Your Web Host . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Your First PHP Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Full Toolbox, Dirty Hands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 Licensed to botuongxulang@yahoo.com
www.it-ebooks.info x Introducing MySQL . . . . . . . . . . . . . . . . . . . . . 53 Chapter 2 An Introduction to Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 Logging On to MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 Structured Query Language . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 Creating a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 Creating a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 Inserting Data into a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Viewing Stored Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 Modifying Stored Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 Deleting Stored Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 Let PHP Do the Typing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 Introducing PHP . . . . . . . . . . . . . . . . . . . . . . . . . Chapter 3 73 Basic Syntax and Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 Variables, Operators, and Comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 Arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 User Interaction and Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 Control Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 Hiding the Seams . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104 Avoid Advertising Your Technology Choices . . . . . . . . . . . . . . . . . . 104 Use PHP Templates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 Many Templates, One Controller . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 Bring On the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 Publishing MySQL Data on the Chapter 4 Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 The Big Picture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 Connecting to MySQL with PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 Sending SQL Queries with PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123 Licensed to botuongxulang@yahoo.com
www.it-ebooks.info xi Handling SELECT Result Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126 Inserting Data into the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132 Deleting Data from the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142 Mission Accomplished . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 Relational Database Design . . . . . . . . . 151 Chapter 5 Giving Credit Where Credit is Due . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152 Rule of Thumb: Keep Entities Separate . . . . . . . . . . . . . . . . . . . . . . . . . . 153 SELECT with Multiple Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158 Simple Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163 Many-to-Many Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166 One for Many, and Many for One . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 Structured PHP Programming . . . . . . 171 Chapter 6 Include Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172 Including HTML Content . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172 Including PHP Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174 Types of Includes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180 Shared Include Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181 Custom Functions and Function Libraries . . . . . . . . . . . . . . . . . . . . . . . . 184 Variable Scope and Global Access . . . . . . . . . . . . . . . . . . . . . . . . . . 187 Structure in Practice: Template Helpers . . . . . . . . . . . . . . . . . . . . . . . . . . 191 The Best Way . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 A Content Management System . . . . 197 Chapter 7 The Front Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198 Managing Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202 Deleting Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204 Adding and Editing Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207 Licensed to botuongxulang@yahoo.com
www.it-ebooks.info xii Managing Categories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212 Managing Jokes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218 Searching for Jokes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218 Adding and Editing Jokes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225 Deleting Jokes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238 Content Formatting with Regular Chapter 8 Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241 Regular Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242 String Replacement with Regular Expressions . . . . . . . . . . . . . . . . . . . . 247 Boldface and Italic Text . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248 Paragraphs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249 Hyperlinks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252 Matching Tags . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255 Putting It All Together . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257 Real World Content Submission . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260 Cookies, Sessions, and Access Chapter 9 Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261 Cookies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261 PHP Sessions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267 A Simple Shopping Cart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269 Access Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279 Database Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279 Controller Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283 Function Library . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290 Managing Passwords and Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300 A Challenge: Joke Moderation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309 Licensed to botuongxulang@yahoo.com
www.it-ebooks.info xiii The Sky’s the Limit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311 MySQL Administration . . . . . . . . . . . . . . . 313 Chapter 10 phpMyAdmin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314 Backing Up MySQL Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319 Database Backups Using mysqldump . . . . . . . . . . . . . . . . . . . . . . . 319 Incremental Backups Using Binary Logs . . . . . . . . . . . . . . . . . . . . . 321 MySQL Access Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324 Granting Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324 Revoking Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328 Access Control Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329 Locked Out? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331 Checking and Repairing MySQL Data Files . . . . . . . . . . . . . . . . . . . . . . . 332 Better Safe than Sorry . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336 Advanced SQL Queries . . . . . . . . . . . . . . . 337 Chapter 11 Sorting SELECT Query Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337 Setting LIMITs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 340 LOCKing TABLES . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341 Column and Table Name Aliases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344 GROUPing SELECT Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 347 LEFT JOIN s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349 Limiting Results with HAVING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353 Further Reading . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 354 Binary Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357 Chapter 12 Semi-dynamic Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358 Handling File Uploads . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 364 Assigning Unique Filenames . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367 Licensed to botuongxulang@yahoo.com
www.it-ebooks.info xiv Recording Uploaded Files in the Database . . . . . . . . . . . . . . . . . . . . . . . . 369 Binary Column Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 370 Storing Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372 Viewing Stored Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374 Putting It All Together . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379 Large File Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386 MySQL Packet Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386 PHP Script Timeout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386 The End . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 387 MySQL Syntax Reference . . . . . . . . . . . . 389 Appendix A SQL Statements Implemented in MySQL . . . . . . . . . . . . . . . . . . . . . . . . . 389 ALTER TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389 ANALYZE TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392 CREATE DATABASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393 CREATE INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393 CREATE TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393 DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 395 DESCRIBE/DESC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396 DROP DATABASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397 DROP INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397 DROP TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397 EXPLAIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397 GRANT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398 INSERT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398 LOAD DATA INFILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400 LOCK/UNLOCK TABLES . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400 OPTIMIZE TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401 RENAME TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 402 REPLACE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 402 Licensed to botuongxulang@yahoo.com
www.it-ebooks.info xv REVOKE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403 SELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403 SET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 410 SHOW . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411 TRUNCATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 412 UNLOCK TABLES . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 412 UPDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413 USE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 414 MySQL Functions . . . . . . . . . . . . . . . . . . . . . . Appendix B 415 Control Flow Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415 Mathematical Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 416 String Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 419 Date and Time Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 423 Miscellaneous Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 430 Functions for Use with GROUP BY Clauses . . . . . . . . . . . . . . . . . . . . . . . 433 MySQL Column Types . . . . . . . . . . . . . . . . 435 Appendix C Numerical Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436 Character Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 440 Date/Time Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445 PHP Functions for Working with Appendix D MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 449 Common PHP mysqli_* Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 449 mysqli_affected_rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 449 mysqli_character_set_name . . . . . . . . . . . . . . . . . . . . . . . . . 449 mysqli_close . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 450 mysqli_connect . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 450 Licensed to botuongxulang@yahoo.com
www.it-ebooks.info xvi mysqli_connect_errno . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451 mysqli_connect_error . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451 mysqli_data_seek . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451 mysqli_errno . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 452 mysqli_error . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 452 mysqli_fetch_all . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 452 mysqli_fetch_array . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453 mysqli_fetch_assoc . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453 mysqli_fetch_field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453 mysqli_fetch_field_direct . . . . . . . . . . . . . . . . . . . . . . . . . 454 mysqli_fetch_fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 454 mysqli_fetch_lengths . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455 mysqli_fetch_object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455 mysqli_fetch_row . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455 mysqli_field_count . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455 mysqli_field_seek . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 456 mysqli_field_tell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 456 mysqli_free_result . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 456 mysqli_get_client_info . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 456 mysqli_get_client_version . . . . . . . . . . . . . . . . . . . . . . . . . 456 mysqli_get_host_info . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457 mysqli_get_proto_info . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457 mysqli_get_server_info . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457 mysqli_get_server_version . . . . . . . . . . . . . . . . . . . . . . . . . 457 mysqli_info . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457 mysqli_insert_id . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 458 mysqli_num_fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 458 mysqli_num_rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 458 mysqli_ping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 458 mysqli_query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 458 Licensed to botuongxulang@yahoo.com
www.it-ebooks.info xvii mysqli_real_escape_string . . . . . . . . . . . . . . . . . . . . . . . . . 459 mysqli_real_query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459 mysqli_select_db . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 460 mysqli_set_charset . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 460 mysqli_stat . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 460 mysqli_store_result . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 460 mysqli_thread_id . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461 mysqli_use_result . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463 Licensed to botuongxulang@yahoo.com
www.it-ebooks.info Licensed to botuongxulang@yahoo.com
www.it-ebooks.info Preface PHP and MySQL have changed. Back in 2001, when I wrote the first edition of this book, readers were astonished to discover that you could create a site full of web pages without having to write a separate HTML file for each page. PHP stood out from the crowd of programming languages, mainly because it was easy enough for almost anyone to learn and free to download and install. The MySQL database, likewise, provided a simple and free solution to a problem that, up until that point, had been solvable only by expert programmers with corporate budgets. Back then, PHP and MySQL were special—heck, they were downright miraculous! But over the years, they have gained plenty of fast-moving competition. In an age when anyone with a free WordPress1 account can set up a full-featured blog in 30 seconds flat, it’s no longer enough for a programming language like PHP to be easy to learn; nor is it enough for a database like MySQL to be free. Indeed, as you sit down to read this book, you probably have ambitions that extend beyond what you can throw together using the free point-and-click tools of the Web. You might even be thinking of building an exciting, new point-and-click tool of your own. WordPress, after all, is built using PHP and MySQL, so why limit your vision to anything less? To keep up with the competition, and with the needs of more demanding projects, PHP and MySQL have had to evolve. PHP is now a far more intricate and powerful language than it was back in 2001, and MySQL is a vastly more complex and capable database. Learning PHP and MySQL today opens up a lot of doors that would have remained closed to the PHP and MySQL experts of 2001. That’s the good news. The bad news is that, in the same way that a butter knife is easier to figure out than a Swiss Army knife (and less likely to cause self-injury!), all these dazzling new features and improvements have indisputably made PHP and MySQL more difficult for beginners to learn. 1 http://wordpress.com/ Licensed to botuongxulang@yahoo.com
www.it-ebooks.info xx Worse yet, PHP has completely abandoned several of the beginner-friendly features that gave it a competitive advantage in 2001, because they turned out to be oversim- plifications, or could lead inexperienced programmers into building web sites with gaping security holes. This is a problem if you’re the author of a beginner’s book about PHP and MySQL. PHP and MySQL have changed, and those changes have made writing this book a lot more difficult. But they have also made this book a lot more important. The more twisty the path, the more valuable the map, right? In this book, I’ll provide you with a hands-on look at what’s involved in building a database driven web site using PHP and MySQL. If your web host provides PHP and MySQL support, you’re in great shape. If not, I’ll show you how to install them on Windows, Mac OS X, and Linux computers, so don’t sweat it. This book is your map to the twisty path that every beginner must navigate to learn PHP and MySQL today. Grab your favorite walking stick; let’s go hiking! Who Should Read This Book This book is aimed at intermediate and advanced web designers looking to make the leap into server-side programming. You’ll be expected to be comfortable with simple HTML, as I’ll make use of it without much in the way of explanation. No knowledge of Cascading Style Sheets (CSS) or JavaScript is assumed or required, but if you do know JavaScript, you’ll find it will make learning PHP a breeze, since these languages are quite similar. By the end of this book, you can expect to have a grasp of what’s involved in building a database driven web site. If you follow the examples, you’ll also learn the basics of PHP (a server-side scripting language that gives you easy access to a database, and a lot more) and Structured Query Language (SQL—the standard language for interacting with relational databases) as supported by MySQL, the most popular free database engine available today. Most importantly, you’ll come away with everything you need to start on your very own database driven site! Licensed to botuongxulang@yahoo.com