Beginning PHP and MySQL (eBook)

W. Jason Gilmore is a web developer and business consultant with more than 15 years of experience helping companies large and small build amazing software solutions. He has been teaching developers from around the world about web development for over a decade, having written six books, including the bestselling Beginning PHP and MySQL, Fourth Edition and Easy PHP Websites with the Zend Framework, Second Edition .Over the years Jason has been published more than 300 times within popular publications such as Developer.com, PHPBuilder.com, JSMag, and Linux Magazine, and instructed hundreds of students in the United States and Europe. He s recently led the successful development and deployment of a 10,000+ product e-commerce project, and is currently working on a variety of new e-commerce initiatives. Jason is cofounder of the wildly popular CodeMash Conference, the largest multi-day developer event in the Midwest.

Title Page 
1 
Copyright Page 
2 
Contents at a Glance 3
Table of Contents 
5 
About the Author 29
About the Technical Reviewer 30
Acknowledgments 31
Introduction 32
Download the Code 32
Contact Me! 33
Chapter 1: Introducing PHP 
34 
History 34
PHP 4 35
PHP 5 36
PHP 5.3 37
PHP 6 38
General Language Features 38
Practicality 38
Power 39
Possibility 40
Price 40
Summary 41
Chapter 2: Configuring Your Environment 
42 
Installation Prerequisites 42
Downloading Apache 43
Downloading PHP 43
Downloading the Documentation 44
Downloading the Apache Manual 44
Downloading the PHP Manual 44
Installing Apache and PHP on Linux 45
Installing Apache and PHP on Windows 46
Installing IIS and PHP on Windows 48
Testing Your Installation 49
Configuring PHP 51
Configuring PHP at Build Time on Linux 51
Customizing the Windows Build 51
Run-Time Configuration 52
Managing PHP’s Configuration Directives 52
The php.ini File 52
The Apache httpd.conf and .htaccess Files 53
Within the Executing Script 54
Configuration Directive Scope 54
PHP’s Configuration Directives 54
Language Options 54
Safe Mode 58
Syntax Highlighting 60
Miscellaneous 61
Resource Limits 62
Data Handling 62
Paths and Directories 67
Fopen Wrappers 68
Dynamic Extensions 69
Choosing a Code Editor 70
Adobe Dreamweaver CS5 69
Notepad++ 69
PDT (PHP Development Tools) 70
Zend Studio 70
Choosing a Web Hosting Provider 70
Seven Questions for Any Prospective Hosting Provider 71
Summary 72
Chapter 3: PHP Basics 
73 
Embedding PHP Code in Your Web Pages 73
Default Syntax 74
Short-Tags 74
Script 75
ASP Style 75
Embedding Multiple Code Blocks 76
Commenting Your Code 76
Single-Line C++ Syntax 76
Shell Syntax 76
Multiple-Line C Syntax 77
Outputting Data to the Browser 77
The print() Statement 78
The echo() Statement 79
The printf() Statement 79
The sprintf() Statement 81
PHP’s Supported Data Types 81
Scalar Data Types 81
Boolean 81
Integer 82
Float 82
String 82
Compound Data Types 83
Array 83
Object 84
Converting Between Data Types Using Type Casting 84
Adapting Data Types with Type Juggling 85
Type-Related Functions 86
Retrieving Types 86
Converting Types 87
Type Identifier Functions 87
Identifiers 87
Variables 88
Variable Declaration 89
Value Assignment 89
Reference Assignment 89
Variable Scope 90
Local Variables 90
Function Parameters 91
Global Variables 92
Static Variables 92
PHP’s Superglobal Variables 93
Learning More About the Server and Client 95
Retrieving Variables Passed Using GET 95
Retrieving Variables Passed Using POST 96
Retrieving Information Stored Within Cookies 96
Retrieving Information About Files Uploaded Using POST 96
Learning More About the Operating System Environment 97
Retrieving Information Stored in Sessions 97
Variable Variables 98
Constants 98
Defining a Constant 98
Expressions 99
Operands 99
Operators 99
Operator Precedence 100
Operator Associativity 101
Arithmetic Operators 101
Assignment Operators 102
String Operators 102
Increment and Decrement Operators 103
Logical Operators 104
Equality Operators 104
Comparison Operators 105
Bitwise Operators 105
String Interpolation 106
Double Quotes 107
Escape Sequences 107
Single Quotes 108
Curly Braces 109
Heredoc 109
Nowdoc 110
Control Structures 110
Conditional Statements 110
The if Statement 110
The else Statement 111
The elseif Statement 111
The switch Statement 112
Looping Statements 112
The while Statement 113
The do...while Statement 114
The for Statement 114
The foreach Statement 115
The break and goto Statements 117
The continue Statement 118
File-Inclusion Statements 118
The include() Statement 118
Ensuring a File Is Included Only Once 120
Requiring a File 120
Ensuring a File Is Required Only Once 120
Summary 121
Chapter 4: Functions 
122 
Invoking a Function 122
Creating a Function 123
Passing Arguments by Value 124
Passing Arguments by Reference 125
Default Argument Values 125
Using Type Hinting 126
Returning Values from a Function 127
The return Statement 127
Returning Multiple Values 127
Recursive Functions 128
Function Libraries 131
Summary 132
Chapter 5: Arrays 
133 
What Is an Array? 133
Creating an Array 135
Creating Arrays with array() 135
Extracting Arrays with list() 136
Populating Arrays with a Predefined Value Range 137
Testing for an Array 137
Outputting an Array 138
Printing Arrays for Testing Purposes 139
Adding and Removing Array Elements 140
Adding a Value to the Front of an Array 140
Adding a Value to the End of an Array 140
Removing a Value from the Front of an Array 141
Removing a Value from the End of an Array 141
Locating Array Elements 141
Searching an Array 141
Searching Associative Array Keys 142
Searching Associative Array Values 142
Retrieving Array Keys 143
Retrieving Array Values 143
Traversing Arrays 143
Retrieving the Current Array Key 144
Retrieving the Current Array Value 144
Retrieving the Current Array Key and Value 145
Moving the Array Pointer 145
Moving the Pointer to the Next Array Position 145
Moving the Pointer to the Previous Array Position 145
Moving the Pointer to the First Array Position 146
Moving the Pointer to the Last Array Position 146
Passing Array Values to a Function 146
Determining Array Size and Uniqueness 147
Determining the Size of an Array 147
Counting Array Value Frequency 148
Determining Unique Array Values 149
Sorting Arrays 149
Reversing Array Element Order 149
Flipping Array Keys and Values 150
Sorting an Array 150
Sorting an Array While Maintaining Key/Value Pairs 151
Sorting an Array in Reverse Order 152
Sorting an Array in Reverse Order While Maintaining Key/Value Pairs 152
Sorting an Array Naturally 153
Case-Insensitive Natural Sorting 153
Sorting an Array by Key Values 153
Sorting Array Keys in Reverse Order 154
Sorting According to User-Defined Criteria 154
Merging, Slicing, Splicing, and Dissecting Arrays 155
Merging Arrays 156
Recursively Appending Arrays 156
Combining Two Arrays 157
Slicing an Array 157
Splicing an Array 158
Calculating an Array Intersection 159
Calculating Associative Array Intersections 159
Calculating Array Differences 160
Calculating Associative Array Differences 161
Other Useful Array Functions 161
Returning a Random Set of Keys 161
Shuffling Array Elements 162
Adding Array Values 162
Subdividing an Array 163
Summary 163
Chapter 6: Object-Oriented PHP 
164 
The Benefits of OOP 164
Encapsulation 164
Inheritance 165
Polymorphism 165
Key OOP Concepts 166
Classes 166
Objects 167
Properties 167
Declaring Properties 167
Invoking Properties 168
Property Scopes 168
Public 168
Private 169
Protected 
170 
Final 170
Property Overloading 170
Setting Properties with the __set() Method 171
Getting Properties with the __get() Method 172
Creating Custom Getters and Setters 173
Constants 173
Methods 174
Declaring Methods 174
Invoking Methods 175
Method Scopes 175
Public 175
Private 176
Protected 176
Abstract 177
Final 177
Type Hinting 178
Constructors and Destructors 178
Constructors 178
Invoking Parent Constructors 180
Invoking Unrelated Constructors 181
Destructors 181
Static Class Members 182
The instanceof Keyword 183
Helper Functions 183
Creating a Class Alias 184
Determining Whether a Class Exists 184
Determining Object Context 184
Learning about Class Methods 184
Learning about Class Properties 184
Learning about Declared Classes 184
Learning about Object Properties 185
Determining an Object’s Parent Class 185
Determining Interface Existence 185
Determining Object Type 185
Determining Object Subclass Type 185
Determining Method Existence 185
Autoloading Objects 186
Summary 186
Chapter 7: Advanced OOP 
187 
Advanced OOP Features Not Supported by PHP 188
Object Cloning 188
Cloning Example 188
The __clone() Method 190
Inheritance 191
Class Inheritance 191
Inheritance and Constructors 193
Inheritance and Late Static Binding 195
Interfaces 196
Implementing a Single Interface 197
Implementing Multiple Interfaces 198
Abstract Classes 199
Introducing Namespaces 200
Summary 202
Chapter 8: Error and Exception 
203 
Configuration Directives 203
Setting the Desired Error Sensitivity Level 204
Displaying Errors to the Browser 205
Displaying Startup Errors 206
Logging Errors 206
Identifying the Log File 206
Setting the Maximum Log Line Length 206
Ignoring Repeated Errors 206
Ignoring Errors Originating from the Same Location 207
Storing Most Recent Error in a Variable 207
Error Logging 207
Initializing PHP’s Logging Facility 208
Opening the Logging Connection 208
Closing the Logging Connection 209
Sending a Message to the Logging Destination 209
Exception Handling 210
Why Exception Handling Is Handy 210
PHP’s Exception-Handling Implementation 211
Extending the Base Exception Class 211
The Default Constructor 212
The Overloaded Constructor 212
Extending the Exception Class 213
Catching Multiple Exceptions 214
SPL’s Exceptions 216
Summary 218
Chapter 9: Strings and Regular 
219 
Regular Expressions 219
Regular Expression Syntax (POSIX) 220
Brackets 220
Quantifiers 221
Predefined Character Ranges (Character Classes) 222
PHP’s Regular Expression Functions (POSIX Extended) 222
Performing a Case-Sensitive Search 222
Performing a Case-Insensitive Search 223
Replacing Text in a Case-Sensitive Fashion 224
Replacing Text in a Case-Insensitive Fashion 225
Splitting a String into Various Elements Based on a Case-Sensitive Pattern 225
Splitting a String into Various Elements Based on a Case-Insensitive Pattern 225
Accommodating Products Supporting Solely Case-Sensitive Regular Expressions 226
Regular Expression Syntax (Perl) 226
Modifiers 227
Metacharacters 227
PHP’s Regular Expression Functions (Perl Compatible) 228
Searching an Array 228
Searching for a Pattern 229
Matching All Occurrences of a Pattern 230
Delimiting Special Regular Expression Characters 230
Replacing All Occurrences of a Pattern 231
Creating a Custom Replacement Function 232
Splitting a String into Various Elements Based on a Case-Insensitive Pattern 233
Other String-Specific Functions 233
Determining the Length of a String 234
Comparing Two Strings 234
Comparing Two Strings Case Sensitively 234
Comparing Two Strings Case Insensitively 235
Calculating the Similarity Between Two Strings 236
Calculating the Difference Between Two Strings 236
Manipulating String Case 237
Converting a String to All Lowercase 237
Converting a String to All Uppercase 237
Capitalizing the First Letter of a String 238
Capitalizing Each Word in a String 238
Converting Strings to and from HTML 239
Converting Newline Characters to HTML Break Tags 239
Converting Special Characters to Their HTML Equivalents 239
Using Special HTML Characters for Other Purposes 241
Converting Text into Its HTML Equivalent 242
Creating a Customized Conversion List 243
Converting HTML to Plain Text 243
Alternatives for Regular Expression Functions 244
Tokenizing a String Based on Predefined Characters 244
Exploding a String Based on a Predefined Delimiter 245
Converting an Array into a String 245
Performing Complex String Parsing 246
Finding the Last Occurrence of a String 247
Replacing All Instances of a String with Another String 247
Retrieving Part of a String 248
Returning Part of a String Based on Predefined Offsets 248
Determining the Frequency of a String’s Appearance 250
Replacing a Portion of a String with Another String 250
Padding and Stripping a String 251
Trimming Characters from the Beginning of a String 251
Trimming Characters from the End of a String 251
Trimming Characters from Both Sides of a String 252
Padding a String 252
Counting Characters and Words 253
Counting the Number of Characters in a String 253
Counting the Total Number of Words in a String 254
Taking Advantage of PEAR: Validate_US 255
Installing Validate_US 255
Using Validate_US 256
Summary 256
Chapter 10: Working with the File 
257 
Learning About Files and Directories 258
Parsing Directory Paths 258
Retrieving a Path’s Filename 258
Retrieving a Path’s Directory 258
Learning More about a Path 259
Identifying the Absolute Path 260
Calculating File, Directory, and Disk Sizes 260
Determining a File’s Size 260
Calculating a Disk’s Free Space 261
Calculating Total Disk Size 261
Retrieving a Directory Size 262
Determining Access and Modification Times 263
Determining a File’s Last Access Time 263
Determining a File’s Last Changed Time 263
Determining a File’s Last Modified Time 264
Working with Files 264
The Concept of a Resource 265
Recognizing Newline Characters 265
Recognizing the End-of-File Character 265
Opening and Closing a File 265
Opening a File 266
Closing a File 267
Reading from a File 267
Reading a File into an Array 267
Reading File Contents into a String Variable 268
Reading a CSV File into an Array 269
Reading a Specific Number of Characters 271
Stripping Tags from Input 271
Reading a File One Character at a Time 272
Ignoring Newline Characters 272
Reading in an Entire File 273
Reading a File According to a Predefined Format 273
Writing a String to a File 274
Moving the File Pointer 275
Moving the File Pointer to a Specific Offset 275
Retrieving the Current Pointer Offset 275
Moving the File Pointer Back to the Beginning of the File 275
Reading Directory Contents 275
Opening a Directory Handle 275
Parsing Directory Contents 276
Parsing Directory Contents 276
Reading a Directory into an Array 276
Executing Shell Commands 277
Removing a Directory 277
Renaming a File 278
Touching a File 278
System-Level Program Execution 279
Sanitizing the Input 279
Delimiting Input 280
Escaping Potentially Dangerous Input 280
PHP’s Program Execution Functions 280
Executing a System-Level Command 281
Retrieving a System Command’s Results 281
Returning Binary Output 282
Executing a Shell Command with Backticks 282
An Alternative to Backticks 283
Summary 283
Chapter 11: PEAR 
284 
The Power of PEAR: Converting Numeral Formats 284
Installing and Updating PEAR 285
Installing PEAR 285
Installing PEAR on Linux 286
Installing PEAR on Windows 286
PEAR and Hosting Companies 287
Updating PEAR 287
Using the PEAR Package Manager 287
Viewing an Installed PEAR Package 288
Learning More about an Installed PEAR Package 288
Installing a PEAR Package 289
Automatically Installing All Dependencies 290
Manually Installing a Package from the PEAR Web Site 290
Including a Package within Your Scripts 291
Upgrading Packages 291
Upgrading a Single Package 291
Upgrading All Packages 292
Uninstalling a Package 292
Downgrading a Package 293
Introducing Pyrus 293
Installing Pyrus 293
Summary 294
Chapter 12: Date and Time 
295 
The Unix Timestamp 295
PHP’s Date and Time Library 296
Validating Dates 296
Formatting Dates and Times 297
Working with Time 299
Learning More about the Current Time 300
Converting a Timestamp to User-Friendly Values 301
Working with Timestamps 302
Determining the Current Timestamp 302
Creating a Timestamp Based on a Specific Date and Time 302
Date Fu 303
Displaying the Localized Date and Time 303
Setting the Default Locale 304
Localizing Dates and Times 305
Displaying the Web Page’s Most Recent Modification Date 308
Determining the Number of Days in the Current Month 308
Determining the Number of Days in Any Given Month 308
Calculating the Date X Days from the Present Date 309
Date and Time Enhancements for PHP 5.1+ Users 309
Introducing the DateTime Constructor 310
Formatting Dates 310
Setting the Date After Instantiation 310
Setting the Time After Instantiation 311
Modifying Dates and Times 311
Calculating the Difference between Two Dates 312
Summary 312
Chapter 13: Working with HTML 
313 
PHP and Web Forms 313
A Simple Example 314
Validating Form Data 315
File Deletion 315
Cross-Site Scripting 316
Sanitizing User Input 317
Escaping Shell Arguments 318
Escaping Shell Metacharacters 318
Converting Input into HTML Entities 319
Stripping Tags from User Input 320
Validating and Sanitizing Data with the Filter Extension 320
Sanitizing Data with the Filter Extension 321
Working with Multivalued Form Components 322
Taking Advantage of PEAR: HTML_QuickForm2 323
Installing HTML_QuickForm2 324
Creating and Validating a Simple Form 324
Summary 327
Chapter 14: Authenticating Your 
328 
HTTP Authentication Concepts 328
Using Apache’s .htaccess Feature 329
Authenticating Your Users with PHP 330
PHP’s Authentication Variables 330
Useful Functions 331
Sending HTTP Headers with header() 331
Determining if a Variable is Set with isset() 331
PHP Authentication Methodologies 332
Hard-Coded Authentication 332
File-Based Authentication 332
Database-Based Authentication 334
Taking Advantage of PEAR: Auth_HTTP 336
Installing Auth_HTTP 336
Authenticating Against a MySQL Database 337
User Login Administration 338
Testing Password Guessability with the CrackLib Library 338
Installing PHP’s CrackLib Extension 339
Using the CrackLib Extension 339
Dictionaries 340
One-Time URLs and Password Recovery 340
Summary 343
Chapter 15: Handling File Uploads 
344 
Uploading Files via HTTP 344
Uploading Files with PHP 345
PHP’s File Upload/Resource Directives 345
file_uploads = On | Off 345
max_input_time = integer 345
max_file_uploads = integer 346
memory_limit = integerM 346
post_max_size = integerM 346
upload_max_filesize = integerM 346
upload_tmp_dir = string 346
The $_FILES Array 347
PHP’s File-Upload Functions 347
Determining Whether a File Was Uploaded 348
Moving an Uploaded File 348
Upload Error Messages 349
A Simple Example 350
Taking Advantage of PEAR: HTTP_Upload 351
Installing HTTP_Upload 351
Uploading a File 351
Learning More About an Uploaded File 352
Uploading Multiple Files 353
Summary 354
Chapter 16: Networking 
355 
DNS, Services, and Servers 355
DNS 356
Checking for the Existence of DNS Records 356
Retrieving DNS Resource Records 358
Retrieving MX Records 359
Services 360
Retrieving a Service’s Port Number 360
Retrieving a Port Number’s Service Name 361
Establishing Socket Connections 361
Mail 363
Configuration Directives 363
SMTP = string 363
sendmail_from = string 363
sendmail_path = string 363
smtp_port = integer 364
mail.force_extra_parameters = string 364
Sending E-mail Using a PHP Script 364
Sending a Plain-Text E-mail 364
Taking Advantage of PEAR: Mail and Mail_Mime 365
Installing Mail and Mail_Mime 365
Sending an E-mail with Multiple Recipients 365
Sending an HTML-Formatted E-mail 366
Sending an Attachment 368
Common Networking Tasks 368
Pinging a Server 369
Creating a Port Scanner 369
Creating a Subnet Converter 370
Testing User Bandwidth 372
Summary 373
Chapter 17: PHP and LDAP 
374 
Using LDAP from PHP 375
Configuring LDAP for PHP 375
Connecting to an LDAP Server 376
Securely Connecting Using the Transport Layer Security Protocol 376
Binding to the LDAP Server 377
Closing the LDAP Server Connection 377
Retrieving LDAP Data 378
Searching for One or More Records 378
Doing Something with Returned Records 379
Retrieving a Specific Entry 380
Counting Retrieved Entries 381
Sorting LDAP Records 382
Inserting LDAP Data 383
Adding a New Entry 383
Adding to Existing Entries 383
Updating LDAP Data 384
Modifying Entries 384
Renaming Entries 384
Deleting LDAP Data 385
Deleting Entries 385
Deleting Entry Attributes 385
Working with the Distinguished Name 386
Converting the DN to a Readable Format 386
Loading the DN into an Array 386
Error Handling 387
Converting LDAP Error Numbers to Messages 387
Retrieving the Most Recent Error Number 387
Summary 388
Chapter 18: Session Handlers 
389 
What Is Session Handling? 389
The Session-Handling Process 390
Configuration Directives 391
Managing the Session Storage Media 391
Setting the Session Files Path 391
Automatically Enabling Sessions 392
Setting the Session Name 392
Choosing Cookies or URL Rewriting 392
Automating URL Rewriting 392
Setting the Session Cookie Lifetime 392
Setting the Session Cookie’s Valid URL Path 393
Setting the Session Cookie’s Valid Domain 393
Validating Sessions Using a Referer 393
Setting Caching Directions for Session-Enabled Pages 393
Setting Cache Expiration Time for Session-Enabled Pages 394
Setting the Session Lifetime 394
Working with Sessions 395
Starting a Session 395
Destroying a Session 395
Setting and Retrieving the Session ID 396
Creating and Deleting Session Variables 396
Encoding and Decoding Session Data 397
Decoding Session Data 398
Regenerating Session IDs 399
Practical Session-Handling Examples 399
Automatically Logging In Returning Users 400
Generating a Recently Viewed Document Index 401
Creating Custom Session Handlers 403
Tying Custom Session Functions into PHP’s Logic 404
Using Custom MySQL-Based Session Handlers 404
Summary 407
Chapter 19: Templating with 
408 
What’s a Templating Engine? 408
Introducing Smarty 410
Installing Smarty 411
Using Smarty 412
Smarty’s Presentational Logic 414
Comments 415
Variable Modifiers 415
Capitalizing the First Letter 415
Counting Words 415
Formatting Dates 416
Assigning a Default Value 416
Removing Markup Tags 417
Truncating a String 417
Control Structures 418
The if Function 418
The foreach Function 419
The foreachelse Function 420
The section Function 420
The sectionelse Function 422
Statements 422
The include Statement 422
The insert Statement 423
The literal Statement 424
The php Statement 424
Creating Configuration Files 425
config_load 425
Referencing Configuration Variables 426
Using CSS in Conjunction with Smarty 426
Caching 427
Working with the Cache Lifetime 428
Eliminating Processing Overhead with isCached() 428
Creating Multiple Caches per Template 429
Some Final Words About Caching 430
Summary 430
Chapter 20: Web Services 
431 
Why Web Services? 431
Really Simple Syndication 433
Understanding RSS Syntax 434
Introducing SimplePie 436
Installing SimplePie 436
Parsing a Feed with SimplePie 437
Parsing Multiple Feeds 438
SimpleXML 439
Loading XML 440
Loading XML from a File 440
Loading XML from a String 441
Loading XML from a DOM Document 441
Parsing XML 441
Learning More About an Element 441
Creating XML from a SimpleXML Object 443
Learning About a Node’s Children 443
Using XPath to Retrieve Node Information 444
Summary 445
Chapter 21: Securing Your Web 
446 
Configuring PHP Securely 447
Security-Related Configuration Parameters 447
disable_functions = string 447
disable_classes = string 447
display_errors = On | Off 447
max_execution_time = integer 448
memory_limit = integerM 448
open_basedir = string 448
sql.safe_mode = integer 448
user_dir = string 448
Hiding Configuration Details 449
Hiding Apache 449
Apache’s ServerSignature Directive 449
Apache’s ServerTokens Directive 449
Hiding PHP 450
expose_php = 1 | 0 450
Remove All Instances of phpinfo() Calls 450
Change the Document Extension 451
Hiding Sensitive Data 451
Hiding the Document Root 451
Denying Access to Certain File Extensions 452
Data Encryption 452
PHP’s Encryption Functions 453
Encrypting Data with the md5() Hash Function 453
The MCrypt Package 453
Encrypting Data with MCrypt 454
Decrypting Data with MCrypt 455
Summary 455
Chapter 22: Creating Ajax- 
456 
Introducing Ajax 456
Introducing jQuery 458
Installing jQuery 458
A Simple Example 459
Responding to Events 460
jQuery and the DOM 461
Modifying Page Elements 462
Creating a Username Existence Validator 463
Determining If a Username Exists 464
Integrating the Ajax Functionality 465
Summary 467
Chapter 23: Building Web Sites for the World 
468 
Translating Web Sites with Gettext 469
Step 1: Update the Web Site Scripts 469
Step 2: Create the Localization Repository 471
Step 3: Create the Translation Files 471
Step 4: Translate the Text 472
Step 5: Generate Binary Files 473
Step 6: Set the Desired Language Within Your Scripts 473
Localizing Dates, Numbers, and Times 474
Summary 475
Chapter 24: Introducing the Zend 
476 
Introducing MVC 476
PHP’s Framework Solutions 478
The CakePHP Framework 479
The Solar Framework 479
The symfony Framework 479
The Zend Framework 480
Introducing the Zend Framework 480
Installing the Zend Framework 481
Creating Your First Zend Framework–Driven Web Site 482
Creating a New Project 482
Configuring Zend_Tool 483
Generate the Project Skeleton 483
application 483
docs 483
library 483
public 484
.zfproject.xml 484
Adjusting the Document Root 484
Navigating to the Home Page 484
Creating the Contacts Controller 485
Creating the Add Action 486
The Contacts Controller 486
The Add View 487
Creating the Layout 488
Interacting with the Database 488
Creating the contacts Table 489
Configuring the Database Connection 489
Managing Configuration Data 489
Adding the Database Connection Parameters 490
Listing Contacts 493
Summary 494
Chapter 25: Introducing MySQL 
495 
What Makes MySQL So Popular? 495
Flexibility 495
Power 496
Enterprise-Level SQL Features 496
Full-Text Indexing and Searching 497
Query Caching 497
Replication 497
Security 497
Flexible Licensing Options 498
MySQL Open Source License 498
Commercial License 498
Which License Should You Use? 499
A (Hyper)Active User Community 499
The Evolution of MySQL 499
MySQL 4 499
MySQL 5 500
MySQL 5.1 501
MySQL 5.4 and 5.5 502
Prominent MySQL Users 502
craigslist 502
Wikipedia 502
Other Prominent Users 502
Summary 503
Chapter 26: Installing and 
504 
Downloading MySQL 504
Installing MySQL 505
Installing MySQL on Linux 505
RPM, Binary, or Source? 505
The RPM Installation Process 505
The Binary Installation Process 506
The Source Installation Process 507
Installing and Configuring MySQL on Windows 508
Installing MySQL on Windows 509
Configuring MySQL on Windows 510
Setting the MySQL Administrator Password 511
Starting and Stopping MySQL 512
Controlling the Daemon Manually 512
Starting MySQL on Linux 512
Starting MySQL on Windows 512
Stopping MySQL on Linux and Windows 512
Configuring and Optimizing MySQL 513
The mysqld_safe Wrapper 513
MySQL’s Configuration and Optimization Parameters 514
Viewing MySQL’s Configuration Parameters 514
Managing Connection Loads 515
Setting the Data Directory Location 516
Setting the Default Storage Engine 516
Automatically Executing SQL Commands 516
Logging Potentially Nonoptimal Queries 516
Logging Slow Queries 517
Setting the Maximum Allowable Simultaneous Connections 517
Setting MySQL’s Communication Port 517
Disabling DNS Resolution 517
Limiting Connections to the Local Server 517
Setting the MySQL Daemon User 517
The my.cnf File 517
Configuring PHP to Work with MySQL 520
Reconfiguring PHP on Linux 520
Reconfiguring PHP on Windows 520
Summary 521
Chapter 27: The Many MySQL 
522 
Introducing the Command-Line Clients 522
The mysql Client 522
Using mysql in Interactive Mode 523
Using mysql in Batch Mode 525
Useful mysql Tips 526
Paging Output 526
Displaying Results Vertically 526
Logging Queries 527
Getting Server Statistics 527
Preventing Accidents 527
Modifying the mysql Prompt 527
Outputting Table Data in HTML and XML 528
Viewing Configuration Variables and System Status 529
Useful mysql Options 530
The mysqladmin Client 532
mysqladmin Commands 532
Other Useful Clients 534
mysqldump 534
mysqlshow 534
mysqlhotcopy 535
mysqlimport 536
myisamchk 536
mysqlcheck 537
Client Options 537
Connection Options 538
General Options 539
MySQL’s GUI Client Programs 540
phpMyAdmin 541
Summary 542
Chapter 28: MySQL Storage Engines and Data Types 
543 
Storage Engines 543
MyISAM 544
MyISAM Static 545
MyISAM Dynamic 546
MyISAM Compressed 546
IBMDB2I 547
InnoDB 547
MEMORY 547
MERGE 549
FEDERATED 549
ARCHIVE 550
CSV 551
EXAMPLE 551
BLACKHOLE 551
Storage Engine FAQ 551
Which Storage Engines Are Available on My Server? 551
How Do I Take Advantage of the Storage Engines on Windows? 552
How Do I Convert ISAM Tables to MyISAM Tables? 552
Is It Wrong to Use Multiple Storage Engines Within the Same Database? 552
How Can I Specify a Storage Engine at Creation Time or Change It Later? 553
I Need Speed! What’s the Fastest Storage Engine? 553
Data Types and Attributes 553
Data Types 553
Date and Time Data Types 553
DATE 553
DATETIME 554
TIME 554
TIMESTAMP [DEFAULT] [ON UPDATE] 554
YEAR[(2|4)] 555
Numeric Data Types 556
BOOL, BOOLEAN 556
BIGINT [(M)] 556
INT [(M)] [UNSIGNED] [ZEROFILL] 556
MEDIUMINT [(M)] [UNSIGNED] [ZEROFILL] 556
SMALLINT [(M)] [UNSIGNED] [ZEROFILL] 556
TINYINT [(M)] [UNSIGNED] [ZEROFILL] 556
DECIMAL([M[,D]]) [UNSIGNED] [ZEROFILL] 557
DOUBLE([M,D]) [UNSIGNED] [ZEROFILL] 557
FLOAT([M,D]) [UNSIGNED] [ZEROFILL] 557
FLOAT (precision) [UNSIGNED] [ZEROFILL] 557
String Data Types 557
[NATIONAL] CHAR(Length) [BINARY | ASCII | UNICODE] 557
[NATIONAL] VARCHAR(Length) [BINARY] 558
LONGBLOB 558
LONGTEXT 558
MEDIUMBLOB 558
MEDIUMTEXT 558
BLOB 558
TEXT 558
TINYBLOB 558
TINYTEXT 559
ENUM("member1","member2",…"member65,535") 559
SET("member1", "member2",…"member64") 559
Data Type Attributes 559
AUTO_INCREMENT 559
BINARY 559
DEFAULT 560
INDEX 560
NATIONAL 560
NOT NULL 561
NULL 561
PRIMARY KEY 561
Data Type Attributes 559
AUTO_INCREMENT 559
BINARY 559
DEFAULT 560
INDEX 560
NATIONAL 560
NOT NULL 561
NULL 561
PRIMARY KEY 561
UNIQUE 562
ZEROFILL 562
Working with Databases and Tables 562
Working with Databases 562
Viewing Databases 563
Creating a Databas 563
Using a Database 564
Deleting a Database 564
Working with Tables 564
Creating a Table 564
Conditionally Creating a Table 565
Copying a Table 565
Creating a Temporary Table 566
Viewing a Database’s Available Tables 566
Viewing a Table Structure 567
Deleting a Table 567
Altering a Table Structure 567
The INFORMATION_SCHEMA 568
Summary 571
Chapter 29: Securing MySQL 
572 
What You Should Do First 573
Securing the mysqld Daemon 574
The MySQL Access Privilege System 575
How the Privilege System Works 575
The Two Stages of Access Control 575
Tracing a Real-World Connection Request 576
Where Is Access Information Stored? 577
The user Table 577
Host 580
User 580
Password 580
The Privilege Columns 581
The Remaining Columns 583
The db Table 583
The host Table 584
The tables_priv Table 586
The columns_priv Table 587
The procs_priv Table 587
User and Privilege Management 588
Creating Users 588
Deleting Users 589
Renaming Users 589
The GRANT and REVOKE Commands 590
Granting Privileges 592
Creating a New User and Assigning Initial Privileges 592
Adding Privileges to an Existing User 592
Granting Table-Level Privileges 593
Granting Multiple Table-Level Privilege 593
Granting Column-Level Privileges 593
Reviewing Privileges 594
Revoking Previously Assigned Permissions 594
Revoking Table-Level Permissions 594
Revoking Column-Level Permissions 594
Deleting a User 595
GRANT and REVOKE Tips 595
Reviewing Privileg 596
SHOW GRANTS FOR 596
Limiting User Resources 596
Secure MySQL Connections 597
Grant Options 598
REQUIRE SSL 598
REQUIRE X509 599
REQUIRE ISSUER 599
REQUIRE SUBJECT 599
REQUIRE CIPHER 599
SSL Options 599
--ssl 600
--ssl-ca 600
--ssl-capath 600
--ssl-cert 600
--ssl-cipher 600
--ssl-key 600
Starting the SSL-Enabled MySQL Server 601
Connecting Using an SSL-Enabled Client 601
Storing SSL Options in the my.cnf File 601
Summary 601
Chapter 30: Using PHP with 
602 
Installation Prerequisites 603
Enabling the mysqli Extension on Linux/Unix 621
Enabling the mysqli Extension on Windows 603
Using the MySQL Native Driver 603
Managing User Privileges 604
Working with Sample Data 604
Using the mysqli Extension 605
Setting Up and Tearing Down the Connection 605
Handling Connection Errors 606
Retrieving Error Information 606
Retrieving Error Codes 606
Retrieving Error Messages 607
Storing Connection Information in a Separate File 608
Securing Your Connection Information 608
Interacting with the Database 609
Sending a Query to the Database 609
Retrieving Data 609
Inserting, Updating, and Deleting Data 610
Recuperating Query Memory 611
Parsing Query Results 611
Fetching Results into an Object 612
Retrieving Results Using Indexed and Associative Arrays 612
Determining the Rows Selected and Rows Affected 613
Determining the Number of Rows Returned 613
Determining the Number of Affected Rows 614
Working with Prepared Statements 614
Preparing the Statement for Execution 615
Executing a Prepared Statement 616
Recuperating Prepared Statement Resources 616
Binding Parameters 616
Binding Variables 618
Retrieving Rows from Prepared Statements 619
Using Other Prepared Statement Methods 619
Executing Database Transactions 620
Enabling Autocommit Mode 620
Committing a Transaction 620
Rolling Back a Transaction 620
Summary 621
Chapter 31: Introducing PDO 
622 
Another Database Abstraction Layer? 623
Using PDO 624
Installing PDO 625
PDO’s Database Options 625
Connecting to a Database Server and Selecting a Database 626
Embedding the Parameters into the Constructor 626
Placing the Parameters in a File 626
Referring to the php.ini File 626
Using PDO’s Connection-Related Options 627
Handling Connection Errors 628
Handling Errors 628
Retrieving SQL Error Codes 629
Retrieving SQL Error Messages 629
Getting and Setting Attributes 630
Retrieving Attributes 630
Setting Attributes 631
Executing Queries 631
Adding, Modifying, and Deleting Table Data 631
Selecting Table Data 632
Introducing Prepared Statements 633
Using Prepared Statements 633
Executing a Prepared Query 634
Binding Parameters 634
Retrieving Data 636
Returning the Number of Retrieved Columns 636
Simultaneously Returning All Result Set Rows 638
Fetching a Single Column 639
Setting Bound Columns 639
Working with Transactions 640
Beginning a Transaction 640
Committing a Transaction 641
Rolling Back a Transa 641
Summary 641
Chapter 32: Stored Routines 
642 
Should You Use Stored Routines? 642
Stored Routine Advantages 642
Stored Routine Disadvantages 643
How MySQL Implements Stored Routines 644
Creating a Stored Routine 644
Setting Security Privileges 645
Setting Input and Return Parameters 645
Characteristics 646
LANGUAGE SQL 646
[NOT] DETERMINISTICOnly used with stored functions, 646
CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA 646
SQL SECURITY {DEFINER | INVOKER} 647
COMMENT 'string' 647
Declaring and Setting Variables 647
Declaring Variables 647
Setting Variables 647
Executing a Stored Routine 648
Creating and Using Multistatement Stored Routines 649
The BEGIN and END Block 650
Conditionals 650
IF-ELSEIF-ELSE 651
CASE 651
Iteration 652
ITERATE 652
LEAVE 653
LOOP 653
REPEAT 655
WHILE 656
Calling a Routine from Within Another Routine 656
Modifying a Stored Routine 657
Deleting a Stored Routine 657
Viewing a Routine’s Status 658
Viewing a Routine’s Creation Syntax 659
Handling Conditions 659
Integrating Routines into Web Applications 660
Creating the Employee Bonus Interface 660
Retrieving Multiple Rows 661
Summary 662
Chapter 33: MySQL Triggers 
663 
Introducing Triggers 663
Why Use Triggers? 663
Taking Action Before an Event 664
Taking Action After an Event 664
Before Triggers vs. After Triggers 665
MySQL’s Trigger Support 666
Creating a Trigger 666
Viewing Existing Triggers 668
The SHOW TRIGGERS Command 668
The INFORMATION_SCHEMA 669
Modifying a Trigger 670
Deleting a Trigger 670
Integrating Triggers into Web Applications 671
Summary 672
Chapter 34: MySQL Views 
673 
Introducing Views 674
MySQL’s View Support 674
Creating and Executing Views 674
Customizing View Results 676
Passing in Parameters 677
Modifying the Returned Column Names 677
Using the ALGORITHM Attribute 678
MERGE 678
TEMPTABLE 678
UNDEFINED 679
Using Security Options 679
Using the WITH CHECK OPTION Clause 679
Viewing View Information 680
Using the DESCRIBE Command 680
Using the INFORMATION_SCHEMA Database 681
Modifying a View 682
Deleting a View 682
Updating Views 682
Incorporating Views into Web Applications 683
Summary 685
Chapter 35: Practical Database 
686 
Sample Data 686
Creating Tabular Output with PEAR 687
Installing HTML_Table 687
Creating a Simple Table 688
Creating More Readable Row Output 689
Creating a Table from Database Data 690
Sorting Output 692
Creating Paged Output 693
Listing Page Numbers 695
Querying Multiple Tables with Subqueries 697
Performing Comparisons with Subqueries 698
Determining Existence with Subqueries 698
Performing Database Maintenance with Subqueries 699
Using Subqueries with PHP 700
Iterating Result Sets with Cursors 700
Cursor Basics 701
Creating a Cursor 701
Opening a Cursor 702
Using a Cursor 702
Closing a Cursor 703
Using Cursors with PHP 703
Summary 704
Chapter 36: Indexes and Searching 
705 
Database Indexing 705
Primary Key Indexes 706
Unique Indexes 707
Normal Indexes 708
Single-Column Normal Indexes 708
Multiple-Column Normal Indexes 709
Full-Text Indexes 710
Stopwords 712
Boolean Full-Text Searches 712
Indexing Best Practices 713
Forms-Based Searches 714
Performing a Simple Search 714
Extending Search Capabilities 716
Performing a Full-Text Search 717
Summary 719
Chapter 37: Transactions 
720 
What’s a Transaction? 720
MySQL’s Transactional Capabilities 721
System Requirements 721
Table Creation 721
A Sample Project 722
Creating Tables and Adding Sample Data 722
The participants Table 723
The trunks Table 723
Adding Some Sample Data 723
Executing an Example Transaction 723
Usage Tips 725
Building Transactional Applications with PHP 726
The Swap Meet Revisited 726
Summary 728
Chapter 38: Importing and Exporting Data 729
Sample Table 729
Using Data Delimitation 730
Importing Data 730
Importing Data with LOAD DATA INFILE 731
A Simple Data Import Example 733
Security and LOAD DATA INFILE 734
Importing Data with mysqlimport 734
Useful Options 735
Writing a mysqlimport Script 737
Loading Table Data with PHP 737
Exporting Data 738
SELECT INTO OUTFILE 739
Usage Tips 739
A Simple Data Export Example 740
Exporting MySQL Data to Microsoft Excel 740
Summary 742

743