Xem mẫu
- 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
nguon tai.lieu . vn