Gilmore Beginning PHP and MySQL

From Novice to Professional
4th ed
ISBN: 978-1-4302-3115-8
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 824 Seiten

Beginning PHP and MySQL: From Novice to Professional, Fourth Edition is a major update of W. Jason Gilmore's authoritative book on PHP and MySQL. The fourth edition includes complete coverage of PHP 5.3 features, including namespacing, an update of AMP stack installation and configuration, updates to Zend Framework, coverage of MySQL Workbench, and much more. You'll not only receive extensive introductions to the core features of PHP, MySQL, and related tools, but you'll also learn how to effectively integrate them in order to build robust data-driven applications. Gilmore has seven years of experience working with these technologies, and he has packed this book with practical examples and insight into the real-world challenges faced by developers. Accordingly, you will repeatedly return to this book as both a valuable instructional tool and reference guide.

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.

Autoren/Hrsg.

Gilmore, W Jason

Inhaltsverzeichnis

1;Title Page
;1
2;Copyright Page
;2
3;Contents at a Glance;3
4;Table of Contents
;5
5;About the Author;29
6;About the Technical Reviewer;30
7;Acknowledgments;31
8;Introduction;32
8.1;Download the Code;32
8.2;Contact Me!;33
9;Chapter 1: Introducing PHP
;34
9.1;History;34
9.1.1;PHP 4;35
9.1.2;PHP 5;36
9.1.3;PHP 5.3;37
9.1.4;PHP 6;38
9.2;General Language Features;38
9.2.1;Practicality;38
9.2.2;Power;39
9.2.3;Possibility;40
9.2.4;Price;40
9.3;Summary;41
10;Chapter 2: Configuring Your Environment
;42
10.1;Installation Prerequisites;42
10.1.1;Downloading Apache;43
10.1.2;Downloading PHP;43
10.1.3;Downloading the Documentation;44
10.1.3.1;Downloading the Apache Manual;44
10.1.3.2;Downloading the PHP Manual;44
10.2;Installing Apache and PHP on Linux;45
10.3;Installing Apache and PHP on Windows;46
10.4;Installing IIS and PHP on Windows;48
10.5;Testing Your Installation;49
10.6;Configuring PHP;51
10.6.1;Configuring PHP at Build Time on Linux;51
10.6.2;Customizing the Windows Build;51
10.7;Run-Time Configuration;52
10.7.1;Managing PHP’s Configuration Directives;52
10.7.1.1;The php.ini File;52
10.7.1.2;The Apache httpd.conf and .htaccess Files;53
10.7.1.3;Within the Executing Script;54
10.7.1.4;Configuration Directive Scope;54
10.7.2;PHP’s Configuration Directives;54
10.7.2.1;Language Options;54
10.7.2.2;Safe Mode;58
10.7.2.3;Syntax Highlighting;60
10.7.2.4;Miscellaneous;61
10.7.2.5;Resource Limits;62
10.7.2.6;Data Handling;62
10.7.2.7;Paths and Directories;67
10.7.2.8;Fopen Wrappers;68
10.7.2.9;Dynamic Extensions;69
10.8;Choosing a Code Editor;70
10.8.1;Adobe Dreamweaver CS5;69
10.8.2;Notepad++;69
10.8.3;PDT (PHP Development Tools);70
10.8.4;Zend Studio;70
10.9;Choosing a Web Hosting Provider;70
10.9.1;Seven Questions for Any Prospective Hosting Provider;71
10.10;Summary;72
11;Chapter 3: PHP Basics
;73
11.1;Embedding PHP Code in Your Web Pages;73
11.1.1;Default Syntax;74
11.1.2;Short-Tags;74
11.1.3;Script;75
11.1.4;ASP Style;75
11.1.5;Embedding Multiple Code Blocks;76
11.2;Commenting Your Code;76
11.2.1;Single-Line C++ Syntax;76
11.2.2;Shell Syntax;76
11.2.3;Multiple-Line C Syntax;77
11.3;Outputting Data to the Browser;77
11.3.1;The print() Statement;78
11.3.2;The echo() Statement;79
11.3.3;The printf() Statement;79
11.3.4;The sprintf() Statement;81
11.4;PHP’s Supported Data Types;81
11.4.1;Scalar Data Types;81
11.4.1.1;Boolean;81
11.4.1.2;Integer;82
11.4.1.3;Float;82
11.4.1.4;String;82
11.4.2;Compound Data Types;83
11.4.2.1;Array;83
11.4.2.2;Object;84
11.4.3;Converting Between Data Types Using Type Casting;84
11.4.4;Adapting Data Types with Type Juggling;85
11.4.5;Type-Related Functions;86
11.4.5.1;Retrieving Types;86
11.4.5.2;Converting Types;87
11.4.6;Type Identifier Functions;87
11.5;Identifiers;87
11.6;Variables;88
11.6.1;Variable Declaration;89
11.6.2;Value Assignment;89
11.6.3;Reference Assignment;89
11.6.4;Variable Scope;90
11.6.4.1;Local Variables;90
11.6.4.2;Function Parameters;91
11.6.4.3;Global Variables;92
11.6.4.4;Static Variables;92
11.6.5;PHP’s Superglobal Variables;93
11.6.5.1;Learning More About the Server and Client;95
11.6.5.2;Retrieving Variables Passed Using GET;95
11.6.5.3;Retrieving Variables Passed Using POST;96
11.6.5.4;Retrieving Information Stored Within Cookies;96
11.6.5.5;Retrieving Information About Files Uploaded Using POST;96
11.6.5.6;Learning More About the Operating System Environment;97
11.6.5.7;Retrieving Information Stored in Sessions;97
11.6.6;Variable Variables;98
11.7;Constants;98
11.7.1;Defining a Constant;98
11.8;Expressions;99
11.8.1;Operands;99
11.8.2;Operators;99
11.8.2.1;Operator Precedence;100
11.8.2.2;Operator Associativity;101
11.8.2.3;Arithmetic Operators;101
11.8.2.4;Assignment Operators;102
11.8.2.5;String Operators;102
11.8.2.6;Increment and Decrement Operators;103
11.8.2.7;Logical Operators;104
11.8.2.8;Equality Operators;104
11.8.2.9;Comparison Operators;105
11.8.2.10;Bitwise Operators;105
11.9;String Interpolation;106
11.9.1;Double Quotes;107
11.9.2;Escape Sequences;107
11.9.3;Single Quotes;108
11.9.4;Curly Braces;109
11.9.5;Heredoc;109
11.9.6;Nowdoc;110
11.10;Control Structures;110
11.10.1;Conditional Statements;110
11.10.1.1;The if Statement;110
11.10.1.2;The else Statement;111
11.10.1.3;The elseif Statement;111
11.10.1.4;The switch Statement;112
11.10.2;Looping Statements;112
11.10.2.1;The while Statement;113
11.10.2.2;The do...while Statement;114
11.10.2.3;The for Statement;114
11.10.2.4;The foreach Statement;115
11.10.2.5;The break and goto Statements;117
11.10.2.6;The continue Statement;118
11.10.3;File-Inclusion Statements;118
11.10.3.1;The include() Statement;118
11.10.3.2;Ensuring a File Is Included Only Once;120
11.10.3.3;Requiring a File;120
11.10.3.4;Ensuring a File Is Required Only Once;120
11.11;Summary;121
12;Chapter 4: Functions
;122
12.1;Invoking a Function;122
12.2;Creating a Function;123
12.2.1;Passing Arguments by Value;124
12.2.2;Passing Arguments by Reference;125
12.2.3;Default Argument Values;125
12.2.4;Using Type Hinting;126
12.2.5;Returning Values from a Function;127
12.2.5.1;The return Statement;127
12.2.5.2;Returning Multiple Values;127
12.2.6;Recursive Functions;128
12.3;Function Libraries;131
12.4;Summary;132
13;Chapter 5: Arrays
;133
13.1;What Is an Array?;133
13.2;Creating an Array;135
13.2.1;Creating Arrays with array();135
13.2.2;Extracting Arrays with list();136
13.2.3;Populating Arrays with a Predefined Value Range;137
13.2.4;Testing for an Array;137
13.3;Outputting an Array;138
13.3.1;Printing Arrays for Testing Purposes;139
13.4;Adding and Removing Array Elements;140
13.4.1;Adding a Value to the Front of an Array;140
13.4.2;Adding a Value to the End of an Array;140
13.4.3;Removing a Value from the Front of an Array;141
13.4.4;Removing a Value from the End of an Array;141
13.5;Locating Array Elements;141
13.5.1;Searching an Array;141
13.5.1.1;Searching Associative Array Keys;142
13.5.1.2;Searching Associative Array Values;142
13.5.2;Retrieving Array Keys;143
13.5.3;Retrieving Array Values;143
13.6;Traversing Arrays;143
13.6.1;Retrieving the Current Array Key;144
13.6.2;Retrieving the Current Array Value;144
13.6.3;Retrieving the Current Array Key and Value;145
13.6.4;Moving the Array Pointer;145
13.6.4.1;Moving the Pointer to the Next Array Position;145
13.6.4.2;Moving the Pointer to the Previous Array Position;145
13.6.4.3;Moving the Pointer to the First Array Position;146
13.6.4.4;Moving the Pointer to the Last Array Position;146
13.6.5;Passing Array Values to a Function;146
13.7;Determining Array Size and Uniqueness;147
13.7.1;Determining the Size of an Array;147
13.7.2;Counting Array Value Frequency;148
13.7.3;Determining Unique Array Values;149
13.8;Sorting Arrays;149
13.8.1;Reversing Array Element Order;149
13.8.2;Flipping Array Keys and Values;150
13.8.3;Sorting an Array;150
13.8.3.1;Sorting an Array While Maintaining Key/Value Pairs;151
13.8.3.2;Sorting an Array in Reverse Order;152
13.8.3.3;Sorting an Array in Reverse Order While Maintaining Key/Value Pairs;152
13.8.3.4;Sorting an Array Naturally;153
13.8.3.5;Case-Insensitive Natural Sorting;153
13.8.3.6;Sorting an Array by Key Values;153
13.8.3.7;Sorting Array Keys in Reverse Order;154
13.8.3.8;Sorting According to User-Defined Criteria;154
13.9;Merging, Slicing, Splicing, and Dissecting Arrays;155
13.9.1;Merging Arrays;156
13.9.2;Recursively Appending Arrays;156
13.9.3;Combining Two Arrays;157
13.9.4;Slicing an Array;157
13.9.5;Splicing an Array;158
13.9.6;Calculating an Array Intersection;159
13.9.7;Calculating Associative Array Intersections;159
13.9.8;Calculating Array Differences;160
13.9.9;Calculating Associative Array Differences;161
13.10;Other Useful Array Functions;161
13.10.1;Returning a Random Set of Keys;161
13.10.2;Shuffling Array Elements;162
13.10.2.1;Adding Array Values;162
13.10.2.2;Subdividing an Array;163
13.11;Summary;163
14;Chapter 6: Object-Oriented PHP
;164
14.1;The Benefits of OOP;164
14.1.1;Encapsulation;164
14.1.2;Inheritance;165
14.1.3;Polymorphism;165
14.2;Key OOP Concepts;166
14.2.1;Classes;166
14.2.2;Objects;167
14.2.3;Properties;167
14.2.3.1;Declaring Properties;167
14.2.3.2;Invoking Properties;168
14.2.3.3;Property Scopes;168
14.2.3.3.1;Public;168
14.2.3.3.2;Private;169
14.2.3.3.3;Protected
;170
14.2.3.3.4;Final;170
14.2.3.4;Property Overloading;170
14.2.3.5;Setting Properties with the __set() Method;171
14.2.3.6;Getting Properties with the __get() Method;172
14.2.3.7;Creating Custom Getters and Setters;173
14.2.4;Constants;173
14.2.5;Methods;174
14.2.5.1;Declaring Methods;174
14.2.5.2;Invoking Methods;175
14.2.5.3;Method Scopes;175
14.2.5.3.1;Public;175
14.2.5.3.2;Private;176
14.2.5.3.3;Protected;176
14.2.5.3.4;Abstract;177
14.2.5.3.5;Final;177
14.2.5.4;Type Hinting;178
14.3;Constructors and Destructors;178
14.3.1;Constructors;178
14.3.1.1;Invoking Parent Constructors;180
14.3.1.2;Invoking Unrelated Constructors;181
14.3.2;Destructors;181
14.4;Static Class Members;182
14.5;The instanceof Keyword;183
14.6;Helper Functions;183
14.6.1;Creating a Class Alias;184
14.6.2;Determining Whether a Class Exists;184
14.6.3;Determining Object Context;184
14.6.4;Learning about Class Methods;184
14.6.5;Learning about Class Properties;184
14.6.6;Learning about Declared Classes;184
14.6.7;Learning about Object Properties;185
14.6.8;Determining an Object’s Parent Class;185
14.6.9;Determining Interface Existence;185
14.6.10;Determining Object Type;185
14.6.11;Determining Object Subclass Type;185
14.6.12;Determining Method Existence;185
14.7;Autoloading Objects;186
14.8;Summary;186
15;Chapter 7: Advanced OOP
Features;187
15.1;Advanced OOP Features Not Supported by PHP;188
15.2;Object Cloning;188
15.2.1;Cloning Example;188
15.2.2;The __clone() Method;190
15.3;Inheritance;191
15.3.1;Class Inheritance;191
15.3.2;Inheritance and Constructors;193
15.3.3;Inheritance and Late Static Binding;195
15.4;Interfaces;196
15.4.1;Implementing a Single Interface;197
15.4.2;Implementing Multiple Interfaces;198
15.5;Abstract Classes;199
15.6;Introducing Namespaces;200
15.7;Summary;202
16;Chapter 8: Error and Exception
Handling;203
16.1;Configuration Directives;203
16.1.1;Setting the Desired Error Sensitivity Level;204
16.1.1.1;Displaying Errors to the Browser;205
16.1.1.2;Displaying Startup Errors;206
16.1.1.3;Logging Errors;206
16.1.1.4;Identifying the Log File;206
16.1.1.5;Setting the Maximum Log Line Length;206
16.1.1.6;Ignoring Repeated Errors;206
16.1.1.7;Ignoring Errors Originating from the Same Location;207
16.1.1.8;Storing Most Recent Error in a Variable;207
16.2;Error Logging;207
16.2.1;Initializing PHP’s Logging Facility;208
16.2.2;Opening the Logging Connection;208
16.2.3;Closing the Logging Connection;209
16.2.4;Sending a Message to the Logging Destination;209
16.3;Exception Handling;210
16.3.1;Why Exception Handling Is Handy;210
16.3.2;PHP’s Exception-Handling Implementation;211
16.3.2.1;Extending the Base Exception Class;211
16.3.2.1.1;The Default Constructor;212
16.3.2.1.2;The Overloaded Constructor;212
16.3.2.2;Extending the Exception Class;213
16.3.2.3;Catching Multiple Exceptions;214
16.3.3;SPL’s Exceptions;216
16.4;Summary;218
17;Chapter 9: Strings and Regular
Expressions;219
17.1;Regular Expressions;219
17.1.1;Regular Expression Syntax (POSIX);220
17.1.1.1;Brackets;220
17.1.1.2;Quantifiers;221
17.1.1.3;Predefined Character Ranges (Character Classes);222
17.1.2;PHP’s Regular Expression Functions (POSIX Extended);222
17.1.2.1;Performing a Case-Sensitive Search;222
17.1.2.2;Performing a Case-Insensitive Search;223
17.1.2.3;Replacing Text in a Case-Sensitive Fashion;224
17.1.2.4;Replacing Text in a Case-Insensitive Fashion;225
17.1.2.5;Splitting a String into Various Elements Based on a Case-Sensitive Pattern;225
17.1.2.6;Splitting a String into Various Elements Based on a Case-Insensitive Pattern;225
17.1.2.7;Accommodating Products Supporting Solely Case-Sensitive Regular Expressions;226
17.1.3;Regular Expression Syntax (Perl);226
17.1.3.1;Modifiers;227
17.1.3.2;Metacharacters;227
17.1.3.3;PHP’s Regular Expression Functions (Perl Compatible);228
17.1.3.4;Searching an Array;228
17.1.3.5;Searching for a Pattern;229
17.1.3.6;Matching All Occurrences of a Pattern;230
17.1.3.7;Delimiting Special Regular Expression Characters;230
17.1.3.8;Replacing All Occurrences of a Pattern;231
17.1.3.9;Creating a Custom Replacement Function;232
17.1.3.10;Splitting a String into Various Elements Based on a Case-Insensitive Pattern;233
17.2;Other String-Specific Functions;233
17.2.1;Determining the Length of a String;234
17.2.2;Comparing Two Strings;234
17.2.2.1;Comparing Two Strings Case Sensitively;234
17.2.2.2;Comparing Two Strings Case Insensitively;235
17.2.2.3;Calculating the Similarity Between Two Strings;236
17.2.2.4;Calculating the Difference Between Two Strings;236
17.2.3;Manipulating String Case;237
17.2.3.1;Converting a String to All Lowercase;237
17.2.3.2;Converting a String to All Uppercase;237
17.2.3.3;Capitalizing the First Letter of a String;238
17.2.3.4;Capitalizing Each Word in a String;238
17.2.4;Converting Strings to and from HTML;239
17.2.4.1;Converting Newline Characters to HTML Break Tags;239
17.2.4.2;Converting Special Characters to Their HTML Equivalents;239
17.2.4.3;Using Special HTML Characters for Other Purposes;241
17.2.4.4;Converting Text into Its HTML Equivalent;242
17.2.4.5;Creating a Customized Conversion List;243
17.2.4.6;Converting HTML to Plain Text;243
17.3;Alternatives for Regular Expression Functions;244
17.3.1;Tokenizing a String Based on Predefined Characters;244
17.3.2;Exploding a String Based on a Predefined Delimiter;245
17.3.3;Converting an Array into a String;245
17.3.4;Performing Complex String Parsing;246
17.3.5;Finding the Last Occurrence of a String;247
17.3.6;Replacing All Instances of a String with Another String;247
17.3.7;Retrieving Part of a String;248
17.3.8;Returning Part of a String Based on Predefined Offsets;248
17.3.9;Determining the Frequency of a String’s Appearance;250
17.3.10;Replacing a Portion of a String with Another String;250
17.3.11;Padding and Stripping a String;251
17.3.11.1;Trimming Characters from the Beginning of a String;251
17.3.11.2;Trimming Characters from the End of a String;251
17.3.11.3;Trimming Characters from Both Sides of a String;252
17.3.11.4;Padding a String;252
17.3.12;Counting Characters and Words;253
17.3.13;Counting the Number of Characters in a String;253
17.3.14;Counting the Total Number of Words in a String;254
17.4;Taking Advantage of PEAR: Validate_US;255
17.4.1;Installing Validate_US;255
17.4.2;Using Validate_US;256
17.5;Summary;256
18;Chapter 10: Working with the File
and Operating System;257
18.1;Learning About Files and Directories;258
18.1.1;Parsing Directory Paths;258
18.1.1.1;Retrieving a Path’s Filename;258
18.1.1.2;Retrieving a Path’s Directory;258
18.1.1.3;Learning More about a Path;259
18.1.1.4;Identifying the Absolute Path;260
18.1.2;Calculating File, Directory, and Disk Sizes;260
18.1.2.1;Determining a File’s Size;260
18.1.2.2;Calculating a Disk’s Free Space;261
18.1.2.3;Calculating Total Disk Size;261
18.1.2.4;Retrieving a Directory Size;262
18.1.3;Determining Access and Modification Times;263
18.1.3.1;Determining a File’s Last Access Time;263
18.1.3.2;Determining a File’s Last Changed Time;263
18.1.3.3;Determining a File’s Last Modified Time;264
18.2;Working with Files;264
18.2.1;The Concept of a Resource;265
18.2.2;Recognizing Newline Characters;265
18.2.3;Recognizing the End-of-File Character;265
18.2.4;Opening and Closing a File;265
18.2.4.1;Opening a File;266
18.2.4.2;Closing a File;267
18.2.5;Reading from a File;267
18.2.5.1;Reading a File into an Array;267
18.2.5.2;Reading File Contents into a String Variable;268
18.2.5.3;Reading a CSV File into an Array;269
18.2.5.4;Reading a Specific Number of Characters;271
18.2.5.5;Stripping Tags from Input;271
18.2.5.6;Reading a File One Character at a Time;272
18.2.5.7;Ignoring Newline Characters;272
18.2.5.8;Reading in an Entire File;273
18.2.5.9;Reading a File According to a Predefined Format;273
18.2.6;Writing a String to a File;274
18.2.7;Moving the File Pointer;275
18.2.7.1;Moving the File Pointer to a Specific Offset;275
18.2.7.2;Retrieving the Current Pointer Offset;275
18.2.7.3;Moving the File Pointer Back to the Beginning of the File;275
18.2.8;Reading Directory Contents;275
18.2.8.1;Opening a Directory Handle;275
18.2.8.2;Parsing Directory Contents;276
18.2.8.3;Parsing Directory Contents;276
18.2.8.4;Reading a Directory into an Array;276
18.3;Executing Shell Commands;277
18.3.1;Removing a Directory;277
18.3.2;Renaming a File;278
18.3.3;Touching a File;278
18.4;System-Level Program Execution;279
18.4.1;Sanitizing the Input;279
18.4.1.1;Delimiting Input;280
18.4.1.2;Escaping Potentially Dangerous Input;280
18.4.2;PHP’s Program Execution Functions;280
18.4.2.1;Executing a System-Level Command;281
18.4.2.2;Retrieving a System Command’s Results;281
18.4.2.3;Returning Binary Output;282
18.4.2.4;Executing a Shell Command with Backticks;282
18.4.2.5;An Alternative to Backticks;283
18.5;Summary;283
19;Chapter 11: PEAR
;284
19.1;The Power of PEAR: Converting Numeral Formats;284
19.2;Installing and Updating PEAR;285
19.2.1;Installing PEAR;285
19.2.1.1;Installing PEAR on Linux;286
19.2.1.2;Installing PEAR on Windows;286
19.2.2;PEAR and Hosting Companies;287
19.2.3;Updating PEAR;287
19.3;Using the PEAR Package Manager;287
19.3.1;Viewing an Installed PEAR Package;288
19.3.2;Learning More about an Installed PEAR Package;288
19.3.3;Installing a PEAR Package;289
19.3.3.1;Automatically Installing All Dependencies;290
19.3.3.2;Manually Installing a Package from the PEAR Web Site;290
19.3.4;Including a Package within Your Scripts;291
19.3.5;Upgrading Packages;291
19.3.5.1;Upgrading a Single Package;291
19.3.5.2;Upgrading All Packages;292
19.3.6;Uninstalling a Package;292
19.3.7;Downgrading a Package;293
19.4;Introducing Pyrus;293
19.4.1;Installing Pyrus;293
19.5;Summary;294
20;Chapter 12: Date and Time
;295
20.1;The Unix Timest295
20.2;PHP’s Date and Time Library;296
20.2.1;Validating Dates;296
20.2.2;Formatting Dates and Times;297
20.2.2.1;Working with Time;299
20.2.2.2;Learning More about the Current Time;300
20.2.3;Converting a Timestamp to User-Friendly Values;301
20.2.4;Working with Timestamps;302
20.2.5;Determining the Current Timest302
20.2.6;Creating a Timestamp Based on a Specific Date and Time;302
20.3;Date Fu;303
20.3.1;Displaying the Localized Date and Time;303
20.3.1.1;Setting the Default Locale;304
20.3.1.2;Localizing Dates and Times;305
20.3.2;Displaying the Web Page’s Most Recent Modification Date;308
20.3.3;Determining the Number of Days in the Current Month;308
20.3.4;Determining the Number of Days in Any Given Month;308
20.3.5;Calculating the Date X Days from the Present Date;309
20.4;Date and Time Enhancements for PHP 5.1+ Users;309
20.4.1;Introducing the DateTime Constructor;310
20.4.2;Formatting Dates;310
20.4.3;Setting the Date After Instantiation;310
20.4.4;Setting the Time After Instantiation;311
20.4.5;Modifying Dates and Times;311
20.4.6;Calculating the Difference between Two Dates;312
20.5;Summary;312
21;Chapter 13: Working with HTML
Forms;313
21.1;PHP and Web Forms;313
21.1.1;A Simple Example;314
21.2;Validating Form Data;315
21.2.1;File Deletion;315
21.2.2;Cross-Site Scripting;316
21.2.3;Sanitizing User Input;317
21.2.3.1;Escaping Shell Arguments;318
21.2.3.2;Escaping Shell Metacharacters;318
21.2.3.3;Converting Input into HTML Entities;319
21.2.3.4;Stripping Tags from User Input;320
21.2.4;Validating and Sanitizing Data with the Filter Extension;320
21.2.4.1;Sanitizing Data with the Filter Extension;321
21.2.5;Working with Multivalued Form Components;322
21.3;Taking Advantage of PEAR: HTML_QuickForm2;323
21.3.1;Installing HTML_QuickForm2;324
21.3.2;Creating and Validating a Simple Form;324
21.4;Summary;327
22;Chapter 14: Authenticating Your
Users;328
22.1;HTTP Authentication Concepts;328
22.1.1;Using Apache’s .htaccess Feature;329
22.2;Authenticating Your Users with PHP;330
22.2.1;PHP’s Authentication Variables;330
22.2.2;Useful Functions;331
22.2.2.1;Sending HTTP Headers with header();331
22.2.2.2;Determining if a Variable is Set with isset();331
22.2.2.3;PHP Authentication Methodologies;332
22.2.3;Hard-Coded Authentication;332
22.2.4;File-Based Authentication;332
22.2.5;Database-Based Authentication;334
22.2.6;Taking Advantage of PEAR: Auth_HTTP;336
22.2.6.1;Installing Auth_HTTP;336
22.2.6.2;Authenticating Against a MySQL Database;337
22.3;User Login Administration;338
22.3.1;Testing Password Guessability with the CrackLib Library;338
22.3.1.1;Installing PHP’s CrackLib Extension;339
22.3.1.2;Using the CrackLib Extension;339
22.3.1.3;Dictionaries;340
22.3.2;One-Time URLs and Password Recovery;340
22.4;Summary;343
23;Chapter 15: Handling File Uploads
;344
23.1;Uploading Files via HTTP;344
23.2;Uploading Files with PHP;345
23.2.1;PHP’s File Upload/Resource Directives;345
23.2.1.1;file_uploads = On | Off;345
23.2.1.2;max_input_time = integer;345
23.2.1.3;max_file_uploads = integer;346
23.2.1.4;memory_limit = integerM;346
23.2.1.5;post_max_size = integerM;346
23.2.1.6;upload_max_filesize = integerM;346
23.2.1.7;upload_tmp_dir = string;346
23.2.2;The $_FILES Array;347
23.2.3;PHP’s File-Upload Functions;347
23.2.3.1;Determining Whether a File Was Uploaded;348
23.2.3.2;Moving an Uploaded File;348
23.2.4;Upload Error Messages;349
23.2.5;A Simple Example;350
23.3;Taking Advantage of PEAR: HTTP_Upload;351
23.3.1;Installing HTTP_Upload;351
23.3.2;Uploading a File;351
23.3.3;Learning More About an Uploaded File;352
23.3.4;Uploading Multiple Files;353
23.4;Summary;354
24;Chapter 16: Networking
;355
24.1;DNS, Services, and Servers;355
24.1.1;DNS;356
24.1.1.1;Checking for the Existence of DNS Records;356
24.1.1.2;Retrieving DNS Resource Records;358
24.1.1.3;Retrieving MX Records;359
24.1.2;Services;360
24.1.2.1;Retrieving a Service’s Port Number;360
24.1.2.2;Retrieving a Port Number’s Service Name;361
24.1.3;Establishing Socket Connections;361
24.2;Mail;363
24.2.1;Configuration Directives;363
24.2.1.1;SMTP = string;363
24.2.1.2;sendmail_from = string;363
24.2.1.3;sendmail_path = string;363
24.2.1.4;smtp_port = integer;364
24.2.1.5;mail.force_extra_parameters = string;364
24.2.2;Sending E-mail Using a PHP Script;364
24.2.2.1;Sending a Plain-Text E-mail;364
24.2.2.2;Taking Advantage of PEAR: Mail and Mail_Mime;365
24.2.2.2.1;Installing Mail and Mail_Mime;365
24.2.2.2.2;Sending an E-mail with Multiple Recipients;365
24.2.2.2.3;Sending an HTML-Formatted E-mail;366
24.2.2.3;Sending an Attachment;368
24.3;Common Networking Tasks;368
24.3.1;Pinging a Server;369
24.3.2;Creating a Port Scanner;369
24.3.3;Creating a Subnet Converter;370
24.3.4;Testing User Bandwidth;372
24.4;Summary;373
25;Chapter 17: PHP and LDAP
;374
25.1;Using LDAP from PHP;375
25.1.1;Configuring LDAP for PHP;375
25.1.2;Connecting to an LDAP Server;376
25.1.2.1;Securely Connecting Using the Transport Layer Security Protocol;376
25.1.2.2;Binding to the LDAP Server;377
25.1.2.3;Closing the LDAP Server Connection;377
25.1.3;Retrieving LDAP Data;378
25.1.3.1;Searching for One or More Records;378
25.1.3.2;Doing Something with Returned Records;379
25.1.3.3;Retrieving a Specific Entry;380
25.1.4;Counting Retrieved Entries;381
25.1.5;Sorting LDAP Records;382
25.1.6;Inserting LDAP Data;383
25.1.6.1;Adding a New Entry;383
25.1.6.2;Adding to Existing Entries;383
25.1.7;Updating LDAP Data;384
25.1.7.1;Modifying Entries;384
25.1.7.2;Renaming Entries;384
25.1.8;Deleting LDAP Data;385
25.1.8.1;Deleting Entries;385
25.1.8.2;Deleting Entry Attributes;385
25.1.9;Working with the Distinguished Name;386
25.1.9.1;Converting the DN to a Readable Format;386
25.1.9.2;Loading the DN into an Array;386
25.1.10;Error Handling;387
25.1.11;Converting LDAP Error Numbers to Messages;387
25.1.12;Retrieving the Most Recent Error Number;387
25.2;Summary;388
26;Chapter 18: Session Handlers
;389
26.1;What Is Session Handling?;389
26.1.1;The Session-Handling Process;390
26.2;Configuration Directives;391
26.2.1;Managing the Session Storage Media;391
26.2.2;Setting the Session Files Path;391
26.2.3;Automatically Enabling Sessions;392
26.2.4;Setting the Session Name;392
26.2.5;Choosing Cookies or URL Rewriting;392
26.2.6;Automating URL Rewriting;392
26.2.7;Setting the Session Cookie Lifetime;392
26.2.8;Setting the Session Cookie’s Valid URL Path;393
26.2.8.1;Setting the Session Cookie’s Valid Domain;393
26.2.8.2;Validating Sessions Using a Referer;393
26.2.9;Setting Caching Directions for Session-Enabled Pages;393
26.2.9.1;Setting Cache Expiration Time for Session-Enabled Pages;394
26.2.9.2;Setting the Session Lifetime;394
26.3;Working with Sessions;395
26.3.1;Starting a Session;395
26.3.2;Destroying a Session;395
26.3.3;Setting and Retrieving the Session ID;396
26.3.4;Creating and Deleting Session Variables;396
26.3.5;Encoding and Decoding Session Data;397
26.3.5.1;Decoding Session Data;398
26.3.5.2;Regenerating Session IDs;399
26.4;Practical Session-Handling Examples;399
26.4.1;Automatically Logging In Returning Users;400
26.4.2;Generating a Recently Viewed Document Index;401
26.5;Creating Custom Session Handlers;403
26.5.1;Tying Custom Session Functions into PHP’s Logic;404
26.5.2;Using Custom MySQL-Based Session Handlers;404
26.6;Summary;407
27;Chapter 19: Templating with
Smarty;408
27.1;What’s a Templating Engine?;408
27.2;Introducing Smarty;410
27.3;Installing Smarty;411
27.4;Using Smarty;412
27.5;Smarty’s Presentational Logic;414
27.5.1;Comments;415
27.5.2;Variable Modifiers;415
27.5.2.1;Capitalizing the First Letter;415
27.5.2.2;Counting Words;415
27.5.2.3;Formatting Dates;416
27.5.2.4;Assigning a Default Value;416
27.5.2.5;Removing Markup Tags;417
27.5.2.6;Truncating a String;417
27.5.3;Control Structures;418
27.5.3.1;The if Function;418
27.5.3.2;The foreach Function;419
27.5.3.3;The foreachelse Function;420
27.5.3.4;The section Function;420
27.5.3.5;The sectionelse Function;422
27.5.4;Statements;422
27.5.4.1;The include Statement;422
27.5.4.2;The insert Statement;423
27.5.4.3;The literal Statement;424
27.5.4.4;The php Statement;424
27.6;Creating Configuration Files;425
27.6.1;config_load;425
27.6.2;Referencing Configuration Variables;426
27.7;Using CSS in Conjunction with Smarty;426
27.8;Caching;427
27.8.1;Working with the Cache Lifetime;428
27.8.2;Eliminating Processing Overhead with isCached();428
27.8.3;Creating Multiple Caches per Template;429
27.8.4;Some Final Words About Caching;430
27.9;Summary;430
28;Chapter 20: Web Services
;431
28.1;Why Web Services?;431
28.2;Really Simple Syndication;433
28.2.1;Understanding RSS Syntax;434
28.3;Introducing SimplePie;436
28.3.1;Installing SimplePie;436
28.3.2;Parsing a Feed with SimplePie;437
28.3.3;Parsing Multiple Feeds;438
28.4;SimpleXML;439
28.4.1;Loading XML;440
28.4.1.1;Loading XML from a File;440
28.4.1.2;Loading XML from a String;441
28.4.1.3;Loading XML from a DOM Document;441
28.4.2;Parsing XML;441
28.4.2.1;Learning More About an Element;441
28.4.2.2;Creating XML from a SimpleXML Object;443
28.4.2.3;Learning About a Node’s Children;443
28.4.2.4;Using XPath to Retrieve Node Information;444
28.5;Summary;445
29;Chapter 21: Securing Your Web
Site;446
29.1;Configuring PHP Securely;447
29.1.1;Security-Related Configuration Parameters;447
29.1.1.1;disable_functions = string;447
29.1.1.2;disable_classes = string;447
29.1.1.3;display_errors = On | Off;447
29.1.1.4;max_execution_time = integer;448
29.1.1.5;memory_limit = integerM;448
29.1.1.6;open_basedir = string;448
29.1.1.7;sql.safe_mode = integer;448
29.1.1.8;user_dir = string;448
29.2;Hiding Configuration Details;449
29.2.1;Hiding Apache;449
29.2.1.1;Apache’s ServerSignature Directive;449
29.2.1.2;Apache’s ServerTokens Directive;449
29.2.2;Hiding PHP;450
29.2.2.1;expose_php = 1 | 0;450
29.2.2.2;Remove All Instances of phpinfo() Calls;450
29.2.2.3;Change the Document Extension;451
29.3;Hiding Sensitive Data;451
29.3.1;Hiding the Document Root;451
29.3.2;Denying Access to Certain File Extensions;452
29.4;Data Encryption;452
29.4.1;PHP’s Encryption Functions;453
29.4.1.1;Encrypting Data with the md5() Hash Function;453
29.4.2;The MCrypt Package;453
29.4.2.1;Encrypting Data with MCrypt;454
29.4.2.2;Decrypting Data with MCrypt;455
29.5;Summary;455
30;Chapter 22: Creating Ajax-
enhanced Features with jQuery and PHP;456
30.1;Introducing Ajax;456
30.2;Introducing jQuery;458
30.2.1;Installing jQuery;458
30.2.2;A Simple Example;459
30.2.3;Responding to Events;460
30.2.4;jQuery and the DOM;461
30.2.4.1;Modifying Page Elements;462
30.3;Creating a Username Existence Validator;463
30.3.1;Determining If a Username Exists;464
30.3.1.1;Integrating the Ajax Functionality;465
30.4;Summary;467
31;Chapter 23: Building Web Sites for the World
;468
31.1;Translating Web Sites with Gettext;469
31.1.1;Step 1: Update the Web Site Scripts;469
31.1.2;Step 2: Create the Localization Repository;471
31.1.3;Step 3: Create the Translation Files;471
31.1.4;Step 4: Translate the Text;472
31.1.5;Step 5: Generate Binary Files;473
31.1.6;Step 6: Set the Desired Language Within Your Scripts;473
31.2;Localizing Dates, Numbers, and Times;474
31.3;Summary;475
32;Chapter 24: Introducing the Zend
Framework;476
32.1;Introducing MVC;476
32.2;PHP’s Framework Solutions;478
32.2.1;The CakePHP Framework;479
32.2.2;The Solar Framework;479
32.2.3;The symfony Framework;479
32.2.4;The Zend Framework;480
32.3;Introducing the Zend Framework;480
32.3.1;Installing the Zend Framework;481
32.3.2;Creating Your First Zend Framework–Driven Web Site;482
32.3.2.1;Creating a New Project;482
32.3.2.1.1;Configuring Zend_Tool;483
32.3.2.1.2;Generate the Project Skeleton;483
32.3.2.1.2.1;application;483
32.3.2.1.2.2;docs;483
32.3.2.1.2.3;library;483
32.3.2.1.2.4;public;484
32.3.2.1.2.5;.zfproject.xml;484
32.3.2.1.3;Adjusting the Document Root;484
32.3.2.1.4;Navigating to the Home Page;484
32.3.2.2;Creating the Contacts Controller;485
32.3.2.2.1;Creating the Add Action;486
32.3.2.2.1.1;The Contacts Controller;486
32.3.2.2.1.2;The Add View;487
32.3.2.3;Creating the Layout;488
32.3.2.4;Interacting with the Database;488
32.3.2.4.1;Creating the contacts Table;489
32.3.2.4.2;Configuring the Database Connection;489
32.3.2.4.2.1;Managing Configuration Data;489
32.3.2.4.2.2;Adding the Database Connection Parameters;490
32.3.2.4.3;Listing Contacts;493
32.4;Summary;494
33;Chapter 25: Introducing MySQL
;495
33.1;What Makes MySQL So Popular?;495
33.1.1;Flexibility;495
33.1.2;Power;496
33.1.2.1;Enterprise-Level SQL Features;496
33.1.2.2;Full-Text Indexing and Searching;497
33.1.2.3;Query Caching;497
33.1.2.4;Replication;497
33.1.2.5;Security;497
33.1.3;Flexible Licensing Options;498
33.1.3.1;MySQL Open Source License;498
33.1.3.2;Commercial License;498
33.1.3.3;Which License Should You Use?;499
33.1.4;A (Hyper)Active User Community;499
33.1.4.1;The Evolution of MySQL;499
33.1.4.1.1;MySQL 4;499
33.1.4.1.2;MySQL 5;500
33.1.4.1.3;MySQL 5.1;501
33.1.4.1.4;MySQL 5.4 and 5.5;502
33.1.4.2;Prominent MySQL Users;502
33.1.4.2.1;craigslist;502
33.1.4.2.2;Wikipedia;502
33.1.4.2.3;Other Prominent Users;502
33.2;Summary;503
34;Chapter 26: Installing and
Configuring MySQL;504
34.1;Downloading MySQL;504
34.2;Installing MySQL;505
34.2.1;Installing MySQL on Linux;505
34.2.1.1;RPM, Binary, or Source?;505
34.2.1.1.1;The RPM Installation Process;505
34.2.1.1.2;The Binary Installation Process;506
34.2.1.1.3;The Source Installation Process;507
34.2.2;Installing and Configuring MySQL on Windows;508
34.2.2.1;Installing MySQL on Windows;509
34.2.2.2;Configuring MySQL on Windows;510
34.3;Setting the MySQL Administrator Password;511
34.4;Starting and Stopping MySQL;512
34.4.1;Controlling the Daemon Manually;512
34.4.1.1;Starting MySQL on Linux;512
34.4.1.2;Starting MySQL on Windows;512
34.4.1.3;Stopping MySQL on Linux and Windows;512
34.5;Configuring and Optimizing MySQL;513
34.5.1;The mysqld_safe Wrapper;513
34.5.2;MySQL’s Configuration and Optimization Parameters;514
34.5.2.1;Viewing MySQL’s Configuration Parameters;514
34.5.2.2;Managing Connection Loads;515
34.5.2.3;Setting the Data Directory Location;516
34.5.2.4;Setting the Default Storage Engine;516
34.5.2.5;Automatically Executing SQL Commands;516
34.5.2.6;Logging Potentially Nonoptimal Queries;516
34.5.2.7;Logging Slow Queries;517
34.5.2.8;Setting the Maximum Allowable Simultaneous Connections;517
34.5.2.9;Setting MySQL’s Communication Port;517
34.5.2.10;Disabling DNS Resolution;517
34.5.2.11;Limiting Connections to the Local Server;517
34.5.2.12;Setting the MySQL Daemon User;517
34.5.3;The my.cnf File;517
34.6;Configuring PHP to Work with MySQL;520
34.6.1;Reconfiguring PHP on Linux;520
34.6.2;Reconfiguring PHP on Windows;520
34.7;Summary;521
35;Chapter 27: The Many MySQL
Clients;522
35.1;Introducing the Command-Line Clients;522
35.1.1;The mysql Client;522
35.1.1.1;Using mysql in Interactive Mode;523
35.1.1.2;Using mysql in Batch Mode;525
35.1.1.3;Useful mysql Tips;526
35.1.1.3.1;Paging Output;526
35.1.1.3.2;Displaying Results Vertically;526
35.1.1.3.3;Logging Queries;527
35.1.1.3.4;Getting Server Statistics;527
35.1.1.3.5;Preventing Accidents;527
35.1.1.3.6;Modifying the mysql Prompt;527
35.1.1.3.7;Outputting Table Data in HTML and XML;528
35.1.1.4;Viewing Configuration Variables and System Status;529
35.1.1.5;Useful mysql Options;530
35.1.2;The mysqladmin Client;532
35.1.2.1;mysqladmin Commands;532
35.1.3;Other Useful Clients;534
35.1.3.1;mysqldump;534
35.1.3.2;mysqlshow;534
35.1.3.3;mysqlhotcopy;535
35.1.3.4;mysqlimport;536
35.1.3.5;myisamchk;536
35.1.3.6;mysqlcheck;537
35.1.4;Client Options;537
35.1.4.1;Connection Options;538
35.1.4.2;General Options;539
35.2;MySQL’s GUI Client Programs;540
35.3;phpMyAdmin;541
35.4;Summary;542
36;Chapter 28: MySQL Storage Engines and Data Types
;543
36.1;Storage Engines;543
36.1.1;MyISAM;544
36.1.1.1;MyISAM Static;545
36.1.1.2;MyISAM Dynamic;546
36.1.1.3;MyISAM Compressed;546
36.1.2;IBMDB2I;547
36.1.3;InnoDB;547
36.1.4;MEMORY;547
36.1.5;MERGE;549
36.1.6;FEDERATED;549
36.1.7;ARCHIVE;550
36.1.8;CSV;551
36.1.9;EXAMPLE;551
36.1.10;BLACKHOLE;551
36.1.11;Storage Engine FAQ;551
36.1.11.1;Which Storage Engines Are Available on My Server?;551
36.1.11.2;How Do I Take Advantage of the Storage Engines on Windows?;552
36.1.11.3;How Do I Convert ISAM Tables to MyISAM Tables?;552
36.1.11.4;Is It Wrong to Use Multiple Storage Engines Within the Same Database?;552
36.1.11.5;How Can I Specify a Storage Engine at Creation Time or Change It Later?;553
36.1.11.6;I Need Speed! What’s the Fastest Storage Engine?;553
36.2;Data Types and Attributes;553
36.2.1;Data Types;553
36.2.1.1;Date and Time Data Types;553
36.2.1.1.1;DATE;553
36.2.1.1.2;DATETIME;554
36.2.1.1.3;TIME;554
36.2.1.1.4;TIMESTAMP [DEFAULT] [ON UPDATE];554
36.2.1.1.5;YEAR[(2|4)];555
36.2.1.2;Numeric Data Types;556
36.2.1.2.1;BOOL, BOOLEAN;556
36.2.1.2.2;BIGINT [(M)];556
36.2.1.2.3;INT [(M)] [UNSIGNED] [ZEROFILL];556
36.2.1.2.4;MEDIUMINT [(M)] [UNSIGNED] [ZEROFILL];556
36.2.1.2.5;SMALLINT [(M)] [UNSIGNED] [ZEROFILL];556
36.2.1.2.6;TINYINT [(M)] [UNSIGNED] [ZEROFILL];556
36.2.1.2.7;DECIMAL([M[,D]]) [UNSIGNED] [ZEROFILL];557
36.2.1.2.8;DOUBLE([M,D]) [UNSIGNED] [ZEROFILL];557
36.2.1.2.9;FLOAT([M,D]) [UNSIGNED] [ZEROFILL];557
36.2.1.2.10;FLOAT (precision) [UNSIGNED] [ZEROFILL];557
36.2.1.3;String Data Types;557
36.2.1.3.1;[NATIONAL] CHAR(Length) [BINARY | ASCII | UNICODE];557
36.2.1.3.2;[NATIONAL] VARCHAR(Length) [BINARY];558
36.2.1.3.3;LONGBLOB;558
36.2.1.3.4;LONGTEXT;558
36.2.1.3.5;MEDIUMBLOB;558
36.2.1.3.6;MEDIUMTEXT;558
36.2.1.3.7;BLOB;558
36.2.1.3.8;TEXT;558
36.2.1.3.9;TINYBLOB;558
36.2.1.3.10;TINYTEXT;559
36.2.1.3.11;ENUM("member1","member2",…"member65,535");559
36.2.1.3.12;SET("member1", "member2",…"member64");559
36.2.1.4;Data Type Attributes;559
36.2.1.4.1;AUTO_INCREMENT;559
36.2.1.4.2;BINARY;559
36.2.1.4.3;DEFAULT;560
36.2.1.4.4;INDEX;560
36.2.1.4.5;NATIONAL;560
36.2.1.4.6;NOT NULL;561
36.2.1.4.7;NULL;561
36.2.1.4.8;PRIMARY KEY;561
36.2.2;Data Type Attributes;559
36.2.2.1;AUTO_INCREMENT;559
36.2.2.2;BINARY;559
36.2.2.3;DEFAULT;560
36.2.2.4;INDEX;560
36.2.2.5;NATIONAL;560
36.2.2.6;NOT NULL;561
36.2.2.7;NULL;561
36.2.2.8;PRIMARY KEY;561
36.2.2.8.1;UNIQUE;562
36.2.2.8.2;ZEROFILL;562
36.3;Working with Databases and Tables;562
36.3.1;Working with Databases;562
36.3.1.1;Viewing Databases;563
36.3.1.2;Creating a Databas;563
36.3.1.3;Using a Database;564
36.3.1.4;Deleting a Database;564
36.3.2;Working with Tables;564
36.3.2.1;Creating a Table;564
36.3.2.1.1;Conditionally Creating a Table;565
36.3.2.1.2;Copying a Table;565
36.3.2.1.3;Creating a Temporary Table;566
36.3.2.1.4;Viewing a Database’s Available Tables;566
36.3.2.1.5;Viewing a Table Structure;567
36.3.2.1.6;Deleting a Table;567
36.3.3;Altering a Table Structure;567
36.3.4;The INFORMATION_SCHEMA;568
36.4;Summary;571
37;Chapter 29: Securing MySQL
;572
37.1;What You Should Do First;573
37.2;Securing the mysqld Daemon;574
37.3;The MySQL Access Privilege System;575
37.3.1;How the Privilege System Works;575
37.3.1.1;The Two Stages of Access Control;575
37.3.1.2;Tracing a Real-World Connection Request;576
37.3.2;Where Is Access Information Stored?;577
37.3.2.1;The user Table;577
37.3.2.1.1;Host;580
37.3.2.1.2;User;580
37.3.2.1.3;Password;580
37.3.2.1.4;The Privilege Columns;581
37.3.2.1.5;The Remaining Columns;583
37.3.2.2;The db Table;583
37.3.2.3;The host Table;584
37.3.2.4;The tables_priv Table;586
37.3.2.5;The columns_priv Table;587
37.3.2.6;The procs_priv Table;587
37.4;User and Privilege Management;588
37.4.1;Creating Users;588
37.4.2;Deleting Users;589
37.4.3;Renaming Users;589
37.4.4;The GRANT and REVOKE Commands;590
37.4.4.1;Granting Privileges;592
37.4.4.1.1;Creating a New User and Assigning Initial Privileges;592
37.4.4.1.2;Adding Privileges to an Existing User;592
37.4.4.1.3;Granting Table-Level Privileges;593
37.4.4.1.4;Granting Multiple Table-Level Privilege;593
37.4.4.1.5;Granting Column-Level Privileges;593
37.4.5;Reviewing Privileges;594
37.4.5.1;Revoking Previously Assigned Permissions;594
37.4.5.2;Revoking Table-Level Permissions;594
37.4.5.3;Revoking Column-Level Permissions;594
37.4.5.4;Deleting a User;595
37.4.6;GRANT and REVOKE Tips;595
37.4.6.1;Reviewing Privileg;596
37.4.7;SHOW GRANTS FOR;596
37.5;Limiting User Resources;596
37.6;Secure MySQL Connections;597
37.6.1;Grant Options;598
37.6.1.1;REQUIRE SSL;598
37.6.1.2;REQUIRE X509;599
37.6.1.3;REQUIRE ISSUER;599
37.6.1.4;REQUIRE SUBJECT;599
37.6.1.5;REQUIRE CIPHER;599
37.6.2;SSL Options;599
37.6.2.1;--ssl;600
37.6.2.2;--ssl-ca;600
37.6.2.3;--ssl-capath;600
37.6.2.4;--ssl-cert;600
37.6.2.5;--ssl-cipher;600
37.6.2.6;--ssl-key;600
37.6.3;Starting the SSL-Enabled MySQL Server;601
37.6.4;Connecting Using an SSL-Enabled Client;601
37.6.5;Storing SSL Options in the my.cnf File;601
37.7;Summary;601
38;Chapter 30: Using PHP with
MySQL;602
38.1;Installation Prerequisites;603
38.1.1;Enabling the mysqli Extension on Linux/Unix;621
38.1.2;Enabling the mysqli Extension on Windows;603
38.1.3;Using the MySQL Native Driver;603
38.1.4;Managing User Privileges;604
38.1.5;Working with Sample Data;604
38.2;Using the mysqli Extension;605
38.2.1;Setting Up and Tearing Down the Connection;605
38.2.2;Handling Connection Errors;606
38.2.3;Retrieving Error Information;606
38.2.3.1;Retrieving Error Codes;606
38.2.3.2;Retrieving Error Messages;607
38.2.4;Storing Connection Information in a Separate File;608
38.2.5;Securing Your Connection Information;608
38.3;Interacting with the Database;609
38.3.1;Sending a Query to the Database;609
38.3.1.1;Retrieving Data;609
38.3.1.2;Inserting, Updating, and Deleting Data;610
38.3.1.3;Recuperating Query Memory;611
38.3.2;Parsing Query Results;611
38.3.2.1;Fetching Results into an Object;612
38.3.2.2;Retrieving Results Using Indexed and Associative Arrays;612
38.3.3;Determining the Rows Selected and Rows Affected;613
38.3.3.1;Determining the Number of Rows Returned;613
38.3.3.2;Determining the Number of Affected Rows;614
38.3.4;Working with Prepared Statements;614
38.3.4.1;Preparing the Statement for Execution;615
38.3.4.2;Executing a Prepared Statement;616
38.3.4.3;Recuperating Prepared Statement Resources;616
38.3.4.4;Binding Parameters;616
38.3.4.5;Binding Variables;618
38.3.4.6;Retrieving Rows from Prepared Statements;619
38.3.4.7;Using Other Prepared Statement Methods;619
38.4;Executing Database Transactions;620
38.4.1;Enabling Autocommit Mode;620
38.4.2;Committing a Transaction;620
38.4.3;Rolling Back a Transaction;620
38.5;Summary;621
39;Chapter 31: Introducing PDO
;622
39.1;Another Database Abstraction Layer?;623
39.2;Using PDO;624
39.2.1;Installing PDO;625
39.2.2;PDO’s Database Options;625
39.2.3;Connecting to a Database Server and Selecting a Database;626
39.2.3.1;Embedding the Parameters into the Constructor;626
39.2.3.2;Placing the Parameters in a File;626
39.2.3.3;Referring to the php.ini File;626
39.2.3.4;Using PDO’s Connection-Related Options;627
39.2.3.5;Handling Connection Errors;628
39.2.4;Handling Errors;628
39.2.4.1;Retrieving SQL Error Codes;629
39.2.4.2;Retrieving SQL Error Messages;629
39.2.5;Getting and Setting Attributes;630
39.2.5.1;Retrieving Attributes;630
39.2.5.2;Setting Attributes;631
39.2.6;Executing Queries;631
39.2.6.1;Adding, Modifying, and Deleting Table Data;631
39.2.6.2;Selecting Table Data;632
39.2.7;Introducing Prepared Statements;633
39.2.7.1;Using Prepared Statements;633
39.2.7.2;Executing a Prepared Query;634
39.2.7.3;Binding Parameters;634
39.2.8;Retrieving Data;636
39.2.8.1;Returning the Number of Retrieved Columns;636
39.2.8.2;Simultaneously Returning All Result Set Rows;638
39.2.8.3;Fetching a Single Column;639
39.2.9;Setting Bound Columns;639
39.2.10;Working with Transactions;640
39.2.10.1;Beginning a Transaction;640
39.2.10.2;Committing a Transaction;641
39.2.10.3;Rolling Back a Transa;641
39.3;Summary;641
40;Chapter 32: Stored Routines
;642
40.1;Should You Use Stored Routines?;642
40.1.1;Stored Routine Advantages;642
40.1.2;Stored Routine Disadvantages;643
40.2;How MySQL Implements Stored Routines;644
40.2.1;Creating a Stored Routine;644
40.2.1.1;Setting Security Privileges;645
40.2.1.2;Setting Input and Return Parameters;645
40.2.1.3;Characteristics;646
40.2.1.3.1;LANGUAGE SQL;646
40.2.1.3.2;[NOT] DETERMINISTICOnly used with stored functions,;646
40.2.1.3.3;CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA;646
40.2.1.3.4;SQL SECURITY {DEFINER | INVOKER};647
40.2.1.3.5;COMMENT 'string';647
40.2.2;Declaring and Setting Variables;647
40.2.2.1;Declaring Variables;647
40.2.2.2;Setting Variables;647
40.2.3;Executing a Stored Routine;648
40.2.4;Creating and Using Multistatement Stored Routines;649
40.2.4.1;The BEGIN and END Block;650
40.2.4.2;Conditionals;650
40.2.4.2.1;IF-ELSEIF-ELSE;651
40.2.4.2.2;CASE;651
40.2.4.3;Iteration;652
40.2.4.3.1;ITERATE;652
40.2.4.3.2;LEAVE;653
40.2.4.3.3;LOOP;653
40.2.4.3.4;REPEAT;655
40.2.4.3.5;WHILE;656
40.2.5;Calling a Routine from Within Another Routine;656
40.2.6;Modifying a Stored Routine;657
40.2.7;Deleting a Stored Routine;657
40.2.8;Viewing a Routine’s Status;658
40.2.9;Viewing a Routine’s Creation Syntax;659
40.2.10;Handling Conditions;659
40.3;Integrating Routines into Web Applications;660
40.3.1;Creating the Employee Bonus Interface;660
40.3.2;Retrieving Multiple Rows;661
40.4;Summary;662
41;Chapter 33: MySQL Triggers
;663
41.1;Introducing Triggers;663
41.1.1;Why Use Triggers?;663
41.1.2;Taking Action Before an Event;664
41.1.3;Taking Action After an Event;664
41.1.4;Before Triggers vs. After Triggers;665
41.2;MySQL’s Trigger Support;666
41.2.1;Creating a Trigger;666
41.2.2;Viewing Existing Triggers;668
41.2.2.1;The SHOW TRIGGERS Command;668
41.2.2.2;The INFORMATION_SCHEMA;669
41.2.3;Modifying a Trigger;670
41.2.4;Deleting a Trigger;670
41.3;Integrating Triggers into Web Applications;671
41.4;Summary;672
42;Chapter 34: MySQL Views
;673
42.1;Introducing Views;674
42.2;MySQL’s View Support;674
42.2.1;Creating and Executing Views;674
42.2.1.1;Customizing View Results;676
42.2.1.2;Passing in Parameters;677
42.2.1.3;Modifying the Returned Column Names;677
42.2.1.4;Using the ALGORITHM Attribute;678
42.2.1.4.1;MERGE;678
42.2.1.4.2;TEMPTABLE;678
42.2.1.4.3;UNDEFINED;679
42.2.1.5;Using Security Options;679
42.2.1.6;Using the WITH CHECK OPTION Clause;679
42.2.2;Viewing View Information;680
42.2.2.1;Using the DESCRIBE Command;680
42.2.2.2;Using the INFORMATION_SCHEMA Database;681
42.2.3;Modifying a View;682
42.2.4;Deleting a View;682
42.2.5;Updating Views;682
42.3;Incorporating Views into Web Applications;683
42.4;Summary;685
43;Chapter 35: Practical Database
Queries;686
43.1;Sample Data;686
43.2;Creating Tabular Output with PEAR;687
43.2.1;Installing HTML_Table;687
43.2.2;Creating a Simple Table;688
43.2.3;Creating More Readable Row Output;689
43.2.4;Creating a Table from Database Data;690
43.3;Sorting Output;692
43.4;Creating Paged Output;693
43.5;Listing Page Numbers;695
43.6;Querying Multiple Tables with Subqueries;697
43.6.1;Performing Comparisons with Subqueries;698
43.6.2;Determining Existence with Subqueries;698
43.6.3;Performing Database Maintenance with Subqueries;699
43.6.4;Using Subqueries with PHP;700
43.7;Iterating Result Sets with Cursors;700
43.7.1;Cursor Basics;701
43.7.2;Creating a Cursor;701
43.7.3;Opening a Cursor;702
43.7.4;Using a Cursor;702
43.7.5;Closing a Cursor;703
43.7.6;Using Cursors with PHP;703
43.8;Summary;704
44;Chapter 36: Indexes and Searching
;705
44.1;Database Indexing;705
44.1.1;Primary Key Indexes;706
44.1.2;Unique Indexes;707
44.1.3;Normal Indexes;708
44.1.3.1;Single-Column Normal Indexes;708
44.1.3.2;Multiple-Column Normal Indexes;709
44.1.4;Full-Text Indexes;710
44.1.4.1;Stopwords;712
44.1.4.2;Boolean Full-Text Searches;712
44.1.5;Indexing Best Practices;713
44.2;Forms-Based Searches;714
44.2.1;Performing a Simple Search;714
44.2.2;Extending Search Capabilities;716
44.2.3;Performing a Full-Text Search;717
44.3;Summary;719
45;Chapter 37: Transactions
;720
45.1;What’s a Transaction?;720
45.2;MySQL’s Transactional Capabilities;721
45.2.1;System Requirements;721
45.2.2;Table Creation;721
45.3;A Sample Project;722
45.3.1;Creating Tables and Adding Sample Data;722
45.3.1.1;The participants Table;723
45.3.1.2;The trunks Table;723
45.3.1.3;Adding Some Sample Data;723
45.3.2;Executing an Example Transaction;723
45.3.3;Usage Tips;725
45.4;Building Transactional Applications with PHP;726
45.4.1;The Swap Meet Revisited;726
45.5;Summary;728
46;Chapter 38: Importing and Exporting Data;729
46.1;Sample Table;729
46.2;Using Data Delimitation;730
46.3;Importing Data;730
46.3.1;Importing Data with LOAD DATA INFILE;731
46.3.1.1;A Simple Data Import Example;733
46.3.1.2;Security and LOAD DATA INFILE;734
46.3.2;Importing Data with mysqlimport;734
46.3.2.1;Useful Options;735
46.3.2.2;Writing a mysqlimport Script;737
46.3.3;Loading Table Data with PHP;737
46.4;Exporting Data;738
46.4.1;SELECT INTO OUTFILE;739
46.4.1.1;Usage Tips;739
46.4.1.2;A Simple Data Export Example;740
46.4.1.3;Exporting MySQL Data to Microsoft Excel;740
46.5;Summary;742
47;
Index;743