Beginning PHP and MySQL: From Novice to Professional, Third Edition

Beginning PHP and MySQL: From Novice to Professional, Third Edition......Page 1
Contents at a Glance......Page 6
Contents......Page 9
About the Author......Page 29
About the Technical Reviewers......Page 31
Acknowledgments......Page 33
Introduction......Page 35
Contact Me!......Page 36
Introducing PHP......Page 37
History......Page 38
PHP 4......Page 39
PHP 5......Page 40
PHP 6......Page 41
Practicality......Page 43
Power......Page 44
Price......Page 45
Summary......Page 46
Configuring Your Environment......Page 47
Downloading Apache......Page 48
Downloading PHP......Page 49
Downloading the Apache Manual......Page 50
Installing Apache and PHP on Linux......Page 51
Installing Apache and PHP on Windows......Page 53
Installing IIS and PHP on Windows......Page 56
Installing IIS and PHP......Page 57
Configuring FastCGI to Manage PHP Processes......Page 58
Testing Your Installation......Page 59
Configuring PHP at Build Time on Linux......Page 61
Customizing the Windows Build......Page 62
The php.ini File......Page 63
Within the Executing Script......Page 65
Language Options......Page 66
Safe Mode......Page 70
Syntax Highlighting......Page 73
Miscellaneous......Page 74
Resource Limits......Page 75
Data Handling......Page 76
Paths and Directories......Page 81
Fopen Wrappers......Page 82
Adobe Dreamweaver CS3......Page 84
PDT (PHP Development Tools)......Page 85
Choosing a Web Hosting Provider......Page 86
Seven Questions for Any Prospective Hosting Provider......Page 87
Summary......Page 89
PHP Basics......Page 91
Default Syntax......Page 92
Short-Tags......Page 93
ASP Style......Page 94
Single-Line C++ Syntax......Page 95
Multiple-Line C Syntax......Page 96
The print() Statement......Page 97
The printf() Statement......Page 99
Integer......Page 101
String......Page 102
Array......Page 103
Object......Page 104
Converting Between Datatypes Using Type Casting......Page 105
Adapting Datatypes with Type Juggling......Page 106
Converting Types......Page 107
Type Identifier Functions......Page 108
Variables......Page 109
Variable Declaration......Page 110
Reference Assignment......Page 111
Local Variables......Page 112
Function Parameters......Page 113
Global Variables......Page 114
Static Variables......Page 115
PHP’s Superglobal Variables......Page 116
Learning More About the Server and Client......Page 118
Retrieving Variables Passed Using POST......Page 119
Retrieving Information About Files Uploaded Using POST......Page 120
Learning More About the Operating System Environment......Page 121
Defining a Constant......Page 122
Unknown......Page 0
Operands......Page 123
Operators......Page 124
Operator Associativity......Page 125
Assignment Operators......Page 126
Increment and Decrement Operators......Page 127
Logical Operators......Page 128
Comparison Operators......Page 129
Bitwise Operators......Page 130
Double Quotes......Page 131
Single Quotes......Page 132
Heredoc......Page 133
Conditional Statements......Page 134
The else Statement......Page 135
The elseif Statement......Page 136
Looping Statements......Page 137
The while Statement......Page 138
The do...while Statement......Page 139
The for Statement......Page 140
The foreach Statement......Page 141
The break and goto Statements......Page 143
File-Inclusion Statements......Page 144
The include() Statement......Page 145
Ensuring a File Is Required Only Once......Page 147
Summary......Page 148
Invoking a Function......Page 149
Passing Arguments by Value......Page 151
Passing Arguments by Reference......Page 153
Default Argument Values......Page 154
The return Statement......Page 155
Returning Multiple Values......Page 156
Recursive Functions......Page 157
Function Libraries......Page 160
Summary......Page 161
Arrays......Page 163
What Is an Array?......Page 164
Creating an Array......Page 165
Creating Arrays with array()......Page 166
Extracting Arrays with list()......Page 167
Populating Arrays with a Predefined Value Range......Page 168
Adding and Removing Array Elements......Page 170
Removing a Value from the Front of an Array......Page 171
Searching Associative Array Keys......Page 172
Retrieving Array Keys......Page 173
Traversing Arrays......Page 174
Retrieving the Current Array Value......Page 175
Moving the Pointer to the Next Array Position......Page 176
Moving the Pointer to the Last Array Position......Page 177
Passing Array Values to a Function......Page 178
Determining the Size of an Array......Page 179
Counting Array Value Frequency......Page 180
Sorting Arrays......Page 181
Flipping Array Keys and Values......Page 182
Sorting an Array......Page 183
Sorting an Array While Maintaining Key/Value Pairs......Page 184
Sorting an Array in Reverse Order While Maintaining Key/Value Pairs......Page 185
Case-Insensitive Natural Sorting......Page 186
Sorting According to User-Defined Criteria......Page 187
Merging Arrays......Page 189
Recursively Appending Arrays......Page 190
Slicing an Array......Page 191
Splicing an Array......Page 192
Calculating an Array Intersection......Page 193
Calculating Associative Array Intersections......Page 194
Calculating Associative Array Differences......Page 195
Shuffling Array Elements......Page 196
Adding Array Values......Page 197
Summary......Page 198
Object-Oriented PHP......Page 199
Encapsulation......Page 200
Inheritance......Page 201
Classes......Page 202
Objects......Page 203
Declaring Fields......Page 204
Field Scopes......Page 205
Properties......Page 208
Setting Properties......Page 209
Getting Properties......Page 210
Creating Custom Getters and Setters......Page 211
Constants......Page 212
Declaring Methods......Page 213
Method Scopes......Page 214
Type Hinting......Page 217
Constructors......Page 218
Invoking Parent Constructors......Page 220
Destructors......Page 222
Static Class Members......Page 223
Determining Object Context......Page 225
Determining an Object’s Parent Class......Page 226
Autoloading Objects......Page 227
Summary......Page 228
Advanced OOP Features......Page 229
Object Cloning......Page 230
Cloning Example......Page 231
The __clone() Method......Page 232
Inheritance......Page 234
Class Inheritance......Page 235
Inheritance and Constructors......Page 237
Interfaces......Page 239
Implementing a Single Interface......Page 241
Implementing Multiple Interfaces......Page 242
Abstract Classes......Page 243
Introducing Namespaces......Page 244
Summary......Page 247
Error and Exception Handling......Page 249
Setting the Desired Error Sensitivity Level......Page 250
Displaying Errors to the Browser......Page 251
Identifying the Log File......Page 252
Error Logging......Page 253
Opening the Logging Connection......Page 254
Sending a Message to the Logging Destination......Page 255
Why Exception Handling Is Handy......Page 257
Extending the Base Exception Class......Page 259
Extending the Exception Class......Page 261
Catching Multiple Exceptions......Page 262
Summary......Page 265
Strings and Regular Expressions......Page 267
Regular Expression Syntax (POSIX)......Page 268
Quantifiers......Page 269
Predefined Character Ranges (Character Classes)......Page 270
Performing a Case-Sensitive Search......Page 271
Performing a Case-Insensitive Search......Page 272
Replacing Text in a Case-Sensitive Fashion......Page 273
Splitting a String into Various Elements Based on a Case-Sensitive Pattern......Page 274
Regular Expression Syntax (Perl)......Page 275
Modifiers......Page 276
Metacharacters......Page 277
Searching an Array......Page 278
Matching All Occurrences of a Pattern......Page 279
Delimiting Special Regular Expression Characters......Page 280
Replacing All Occurrences of a Pattern......Page 281
Creating a Custom Replacement Function......Page 282
Splitting a String into Various Elements Based on a Case-Insensitive Pattern......Page 283
Other String-Specific Functions......Page 284
Comparing Two Strings Case Sensitively......Page 285
Comparing Two Strings Case Insensitively......Page 286
Calculating the Difference Between Two Strings......Page 287
Converting a String to All Uppercase......Page 288
Capitalizing the First Letter of a String......Page 289
Converting Newline Characters to HTML Break Tags......Page 290
Converting Special Characters to Their HTML Equivalents......Page 291
Using Special HTML Characters for Other Purposes......Page 293
Converting Text into Its HTML Equivalent......Page 294
Converting HTML to Plain Text......Page 295
Tokenizing a String Based on Predefined Characters......Page 296
Exploding a String Based on a Predefined Delimiter......Page 297
Converting an Array into a String......Page 298
Performing Complex String Parsing......Page 299
Finding the Last Occurrence of a String......Page 300
Retrieving Part of a String......Page 301
Returning Part of a String Based on Predefined Offsets......Page 302
Determining the Frequency of a String’s Appearance......Page 303
Replacing a Portion of a String with Another String......Page 304
Trimming Characters from the End of a String......Page 305
Padding a String......Page 306
Counting the Number of Characters in a String......Page 307
Counting the Total Number of Words in a String......Page 308
Installing Validate_US......Page 310
Using Validate_US......Page 311
Summary......Page 312
Working with the File and Operating System......Page 313
Retrieving a Path’s Filename......Page 314
Learning More About a Path......Page 315
Identifying the Absolute Path......Page 316
Calculating a Disk’s Free Space......Page 317
Calculating Total Disk Size......Page 318
Retrieving a Directory Size......Page 319
Determining a File’s Last Access Time......Page 320
Determining a File’s Last Changed Time......Page 321
The Concept of a Resource......Page 322
Opening and Closing a File......Page 323
Opening a File......Page 324
Reading from a File......Page 325
Reading a File into an Array......Page 326
Reading File Contents into a String Variable......Page 327
Reading a CSV File into an Array......Page 328
Reading a Specific Number of Characters......Page 329
Stripping Tags from Input......Page 330
Ignoring Newline Characters......Page 331
Reading in an Entire File......Page 332
Writing a String to a File......Page 333
Moving the File Pointer to a Specific Offset......Page 334
Parsing Directory Contents......Page 335
Reading a Directory into an Array......Page 336
Removing a Directory......Page 337
Touching a File......Page 338
Sanitizing the Input......Page 339
Delimiting Input......Page 340
Executing a System-Level Command......Page 341
Retrieving a System Command’s Results......Page 342
Executing a Shell Command with Backticks......Page 343
Summary......Page 344
PEAR......Page 345
Installer-Suggested Packages......Page 346
The Power of PEAR: Converting Numeral Formats......Page 348
Installing PEAR......Page 349
Installing PEAR on Windows......Page 350
Updating PEAR......Page 351
Viewing an Installed PEAR Package......Page 352
Learning More About an Installed PEAR Package......Page 353
Installing a PEAR Package......Page 354
Manually Installing a Package from the PEAR Web Site......Page 355
Upgrading a Single Package......Page 356
Upgrading All Packages......Page 357
Summary......Page 358
The Unix Timestamp......Page 359
PHP’s Date and Time Library......Page 360
Formatting Dates and Times......Page 361
Learning More About the Current Time......Page 365
Converting a Timestamp to User-Friendly Values......Page 366
Working with Timestamps......Page 367
Creating a Timestamp Based on a Specific Date and Time......Page 368
Date Fu......Page 369
Setting the Default Locale......Page 370
Localizing Dates and Times......Page 372
Displaying the Web Page’s Most Recent Modification Date......Page 374
Determining the Number of Days in Any Given Month......Page 375
Calculating the Date X Days from the Present Date......Page 376
Installing Calendar......Page 377
Working with Calendar......Page 378
Creating a Monthly Calendar......Page 379
Validating Dates and Times......Page 380
Introducing the DateTime Constructor......Page 381
Setting the Date After Instantiation......Page 382
Modifying Dates and Times......Page 383
Summary......Page 384
PHP and Web Forms......Page 385
A Simple Example......Page 387
Passing Form Data to a Function......Page 388
Working with Multivalued Form Components......Page 390
Taking Advantage of PEAR: HTML_QuickForm......Page 391
Creating a Simple Form......Page 392
Validating Form Input......Page 394
Processing Form Values......Page 398
Using Auto-Completion......Page 399
Summary......Page 400
Authenticating Your Users......Page 401
HTTP Authentication Concepts......Page 402
Authentication Variables......Page 403
header()......Page 404
isset()......Page 405
Hard-Coded Authentication......Page 406
File-based Authentication......Page 407
Database-based Authentication......Page 409
IP-based Authentication......Page 411
Installing Auth_HTTP......Page 413
Authenticating Against a MySQL Database......Page 414
Testing Password Guessability with the CrackLib Library......Page 416
Installing PHP’s CrackLib Extension......Page 417
Using the CrackLib Extension......Page 418
One-Time URLs and Password Recovery......Page 419
Summary......Page 422
Uploading Files via HTTP......Page 423
Uploading Files with PHP......Page 424
M......Page 425
The $_FILES Array......Page 426
Determining Whether a File Was Uploaded......Page 427
Moving an Uploaded File......Page 428
Upload Error Messages......Page 429
A Simple Example......Page 430
Installing HTTP_Upload......Page 431
Uploading a File......Page 432
Learning More About an Uploaded File......Page 433
Uploading Multiple Files......Page 434
Summary......Page 435
Networking......Page 437
Checking for the Existence of DNS Records......Page 438
Retrieving DNS Resource Records......Page 440
Retrieving a Service’s Port Number......Page 443
Establishing Socket Connections......Page 444
sendmail_from =......Page 447
Sending E-mail Using a PHP Script......Page 448
Taking Advantage of PEAR: Mail and Mail_Mime......Page 449
Pinging a Server......Page 454
Creating a Port Scanner......Page 455
Creating a Subnet Converter......Page 456
Testing User Bandwidth......Page 458
Summary......Page 460
PHP and LDAP......Page 461
Connecting to an LDAP Server......Page 463
Binding to the LDAP Server......Page 464
Closing the LDAP Server Connection......Page 465
Searching for One or More Records......Page 466
Doing Something with Returned Records......Page 468
Retrieving a Specific Entry......Page 469
Sorting LDAP Records......Page 471
Adding a New Entry......Page 472
Adding to Existing Entries......Page 473
Deleting LDAP Data......Page 474
Deleting Entry Attributes......Page 475
Loading the DN into an Array......Page 476
Converting LDAP Error Numbers to Messages......Page 477
Retrieving the Most Recent Error Message......Page 478
Summary......Page 479
What Is Session Handling?......Page 481
The Session-Handling Process......Page 483
Managing the Session Storage Media......Page 484
Automatically Enabling Sessions......Page 485
Automating URL Rewriting......Page 486
Setting the Session Cookie’s Valid Domain......Page 487
Setting Caching Directions for Session-Enabled Pages......Page 488
Starting a Session......Page 489
Setting and Retrieving the Session ID......Page 490
Creating and Deleting Session Variables......Page 491
Encoding Session Data......Page 492
Decoding Session Data......Page 493
Practical Session-Handling Examples......Page 494
Automatically Logging In Returning Users......Page 495
Generating a Recently Viewed Document Index......Page 497
Creating Custom Session Handlers......Page 498
Tying Custom Session Functions into PHP’s Logic......Page 499
Using Custom MySQL-Based Session Handlers......Page 500
Summary......Page 505
Templating with Smarty......Page 507
What’s a Templating Engine?......Page 508
Introducing Smarty......Page 510
Installing Smarty......Page 511
Using Smarty......Page 513
Smarty’s Presentational Logic......Page 515
Capitalizing the First Letter......Page 516
Formatting Dates......Page 517
Removing Markup Tags......Page 518
Truncating a String......Page 519
The if Function......Page 520
The foreach Function......Page 521
The section Function......Page 523
The include Statement......Page 526
The insert Statement......Page 527
The php Statement......Page 528
Creating Configuration Files......Page 529
Referencing Configuration Variables......Page 530
Using CSS in Conjunction with Smarty......Page 531
Caching......Page 533
Working with the Cache Lifetime......Page 534
Creating Multiple Caches per Template......Page 535
Summary......Page 537
Web Services......Page 539
Why Web Services?......Page 540
Really Simple Syndication......Page 542
Understanding RSS Syntax......Page 545
Introducing MagpieRSS......Page 546
How Magpie Parses a Feed......Page 547
Retrieving and Rendering an RSS Feed......Page 551
Aggregating Feeds......Page 552
Limiting the Number of Displayed Headlines......Page 554
SimpleXML......Page 555
Loading XML......Page 556
Loading XML from a File......Page 557
Loading XML from a DOM Document......Page 558
Learning More About an Element......Page 559
Learning About a Node’s Children......Page 560
Using XPath to Retrieve Node Information......Page 561
SOAP......Page 562
Introducing SOAP Messages......Page 563
Introducing PHP’s SOAP Extension......Page 564
Prerequisites......Page 565
Creating a SOAP Server......Page 567
Understanding SOAP Client and Server Interaction......Page 572
Summary......Page 573
Secure PHP Programming......Page 575
|......Page 576
safe_mode_include_dir =......Page 578
disable_functions =......Page 579
max_execution_time =......Page 580
user_dir =......Page 581
Apache’s ServerSignature Directive......Page 582
|......Page 583
Change the Document Extension......Page 584
Hiding the Document Root......Page 585
File Deletion......Page 586
Cross-Site Scripting......Page 587
Sanitizing User Input: The Solution......Page 589
Escaping Shell Metacharacters......Page 590
Converting Input into HTML Entities......Page 591
Installing Validate......Page 592
Validating a String......Page 593
Validating an E-mail Address......Page 594
Encrypting Data with the md5() Hash Function......Page 595
Using the mhash Library......Page 596
Hashing Data with mhash......Page 597
The MCrypt Package......Page 598
Decrypting Data with MCrypt......Page 600
Summary......Page 601
Introduction to SQLite......Page 603
Installing SQLite......Page 604
Using the SQLite Command-Line Interface......Page 605
Opening an SQLite Database......Page 607
Opening a Persistent SQLite Connect......Page 608
Closing a Connection......Page 609
Executing a SQL Query......Page 610
Executing an Unbuffered SQL Query......Page 611
Returning the Result Set As an Associative Array......Page 612
Retrieving Select Result Set Columns......Page 614
Retrieving the First Column in the Result Set......Page 615
Retrieving Field Names......Page 616
Retrieving the Number of Rows in the Result Set......Page 617
Retrieving the Row Residing at the Current Pointer Position......Page 618
Moving the Result Set Pointer Forward......Page 619
Moving the Result Set Pointer to a Desired Location......Page 620
Working with Binary Data......Page 621
Creating an SQLite Function......Page 623
Decoding Binary Data......Page 624
Creating Aggregate Functions......Page 625
Summary......Page 626
Building Web Sites for the World......Page 627
Step 1: Update the Web Site Scripts......Page 628
Step 2: Create the Localization Repository......Page 630
Step 3: Create the Translation Files......Page 631
Step 4: Translate the Text......Page 632
Step 6: Set the Desired Language Within Your Scripts......Page 633
Localizing Dates, Numbers, and Times......Page 634
Summary......Page 636
Introducing MVC......Page 637
The CakePHP Framework......Page 641
The symfony Framework......Page 642
Introducing the Zend Framework......Page 643
Downloading and Installing the Zend Framework......Page 645
Creating Your First Zend Framework–Driven Web Site......Page 646
Creating the Directory Structure......Page 647
Creating the Front-End Controller......Page 648
Creating the Controllers......Page 649
Creating the Views......Page 651
Searching the Web with Zend_Service_Yahoo......Page 653
Creating the Controller......Page 654
Creating the View......Page 655
Summary......Page 656
Introducing MySQL......Page 657
Flexibility......Page 658
Enterprise-Level SQL Features......Page 659
Replication......Page 660
Security......Page 661
MySQL Open Source License......Page 662
A (Hyper) Active User Community......Page 663
MySQL 4......Page 664
MySQL 5.0......Page 665
MySQL 5.1......Page 666
craigslist......Page 667
Yahoo! Finance......Page 668
Summary......Page 669
Installing and Configuring MySQL......Page 671
Installing MySQL......Page 672
RPM, Binary, or Source?......Page 673
Installing MySQL on Windows......Page 678
Configuring MySQL on Windows......Page 679
Setting the MySQL Administrator Password......Page 681
Starting MySQL on Linux......Page 682
Starting MySQL on Windows......Page 683
Linux......Page 684
Windows......Page 686
The mysqld_safe Wrapper......Page 688
Viewing MySQL’s Configuration Parameters......Page 689
Setting the Default Storage Engine......Page 691
Setting the Maximum Allowable Simultaneous Connections......Page 692
The my.cnf File......Page 693
Reconfiguring PHP on Windows......Page 697
Summary......Page 698
The mysql Client......Page 699
Using mysql in Interactive Mode......Page 700
Using mysql in Batch Mode......Page 703
Useful mysql Tips......Page 704
Viewing Configuration Variables and System Status......Page 707
Useful mysql Options......Page 709
mysqladmin Commands......Page 712
mysqldump......Page 714
mysqlhotcopy......Page 715
myisamchk......Page 717
mysqlcheck......Page 718
Client Options......Page 719
Connection Options......Page 720
General Options......Page 721
MySQL’s GUI Client Programs......Page 722
MySQL Administrator......Page 723
MySQL Query Browser......Page 724
MySQL Migration Toolkit......Page 726
phpMyAdmin......Page 727
Summary......Page 728
Storage Engines......Page 729
MyISAM......Page 731
MyISAM Static......Page 732
MyISAM Compressed......Page 733
InnoDB......Page 734
MEMORY......Page 735
MERGE......Page 736
FEDERATED......Page 737
CSV......Page 739
BLACKHOLE......Page 740
How Do I Convert ISAM Tables to MyISAM Tables?......Page 741
Datatypes and Attributes......Page 742
Date and Time Datatypes......Page 743
Numeric Datatypes......Page 746
String Datatypes......Page 748
Datatype Attributes......Page 750
ZEROFILL......Page 753
Viewing Databases......Page 754
Using a Database......Page 755
Creating a Table......Page 756
Conditionally Creating a Table......Page 757
Viewing a Database’s Available Tables......Page 758
Viewing a Table Structure......Page 759
Altering a Table Structure......Page 760
The INFORMATION_SCHEMA......Page 761
Summary......Page 764
Securing MySQL......Page 767
What You Should Do First......Page 768
The MySQL Access Privilege System......Page 770
The Two Stages of Access Control......Page 771
Tracing Through a Real-World Connection Request......Page 772
The user Table......Page 774
The db Table......Page 781
The host Table......Page 782
The tables_priv Table......Page 783
The procs_priv Table......Page 785
Creating Users......Page 786
Renaming Users......Page 787
The GRANT and REVOKE Commands......Page 788
Granting Privileges......Page 789
Revoking Privileges......Page 792
GRANT and REVOKE Tips......Page 794
Limiting User Resources......Page 795
Secure MySQL Connections......Page 796
REQUIRE X509......Page 797
SSL Options......Page 798
--ssl-cipher......Page 799
Storing SSL Options in the my.cnf File......Page 800
Summary......Page 801
Using PHP with MySQL......Page 803
Handling Installation Prerequisites......Page 804
Working with Sample Data......Page 805
Setting Up and Tearing Down the Connection......Page 806
Retrieving Error Codes......Page 808
Retrieving Error Messages......Page 809
Storing Connection Information in a Separate File......Page 810
Sending a Query to the Database......Page 811
Retrieving Data......Page 812
Inserting, Updating, and Deleting Data......Page 813
Recuperating Query Memory......Page 814
Fetching Results into an Object......Page 815
Retrieving Results Using Indexed and Associative Arrays......Page 816
Determining the Number of Rows Returned......Page 817
Working with Prepared Statements......Page 818
Preparing the Statement for Execution......Page 819
Executing a Prepared Statement......Page 820
Binding Parameters......Page 821
Binding Variables......Page 823
Using Other Prepared Statement Methods......Page 825
Rolling Back a Transaction......Page 826
Summary......Page 827
Introducing PDO......Page 829
Another Database Abstraction Layer?......Page 831
Installing PDO......Page 832
PDO’s Database Options......Page 833
Connecting to a Database Server and Selecting a Database......Page 834
Using PDO’s Connection-Related Options......Page 835
Handling Connection Errors......Page 837
Retrieving SQL Error Codes......Page 838
Retrieving SQL Error Messages......Page 839
Retrieving Attributes......Page 840
Adding, Modifying, and Deleting Table Data......Page 841
Selecting Table Data......Page 842
Using Prepared Statements......Page 843
Executing a Prepared Query......Page 844
Binding Parameters......Page 845
Retrieving the Next Row in the Result Set......Page 847
Simultaneously Returning All Result Set Rows......Page 849
Fetching a Single Column......Page 850
Setting Bound Columns......Page 851
Working with Transactions......Page 852
Summary......Page 853
Stored Routines......Page 855
Stored Routine Disadvantages......Page 856
How MySQL Implements Stored Routines......Page 857
The proc Table......Page 858
The procs_priv Table......Page 859
Creating a Stored Routine......Page 860
Setting Input and Return Parameters......Page 861
Characteristics......Page 862
Setting Variables......Page 864
Creating and Using Multistatement Stored Routines......Page 866
Conditionals......Page 868
Iteration......Page 870
Calling a Routine from Within Another Routine......Page 876
Viewing a Routine’s Status......Page 877
Viewing a Routine’s Creation Syntax......Page 879
Handling Conditions......Page 880
Creating the Employee Bonus Interface......Page 881
Retrieving Multiple Rows......Page 882
Summary......Page 883
Introducing Triggers......Page 885
Taking Action Before an Event......Page 886
Taking Action After an Event......Page 887
Before Triggers vs. After Triggers......Page 888
MySQL’s Trigger Support......Page 889
Creating a Trigger......Page 890
The SHOW TRIGGERS Command......Page 892
The INFORMATION_SCHEMA......Page 893
Integrating Triggers into Web Applications......Page 895
Summary......Page 897
MySQL Views......Page 899
Introducing Views......Page 900
Creating and Executing Views......Page 901
Customizing View Results......Page 903
Passing in Parameters......Page 904
Using the ALGORITHM Attribute......Page 905
Using Security Options......Page 906
Using the WITH CHECK OPTION Clause......Page 907
Using the DESCRIBE Command......Page 908
Using the INFORMATION_SCHEMA Database......Page 909
Modifying a View......Page 910
Updating Views......Page 911
Incorporating Views into Web Applications......Page 912
Summary......Page 914
Practical Database Queries......Page 915
Creating Tabular Output with PEAR......Page 916
Installing HTML_Table......Page 917
Creating a Simple Table......Page 918
Creating More Readable Row Output......Page 920
Creating a Table from Database Data......Page 921
Generalizing the Output Process......Page 923
Sorting Output......Page 926
Creating Paged Output......Page 928
Listing Page Numbers......Page 931
Querying Multiple Tables with Subqueries......Page 933
Determining Existence with Subqueries......Page 935
Using Subqueries with PHP......Page 937
Iterating Result Sets with Cursors......Page 938
Cursor Basics......Page 939
Using a Cursor......Page 940
Summary......Page 942
Database Indexing......Page 943
Primary Key Indexes......Page 944
Unique Indexes......Page 946
Single-Column Normal Indexes......Page 947
Multiple-Column Normal Indexes......Page 948
Full-Text Indexes......Page 949
Stopwords......Page 952
Boolean Full-Text Searches......Page 953
Indexing Best Practices......Page 954
Performing a Simple Search......Page 955
Extending Search Capabilities......Page 957
Performing a Full-Text Search......Page 959
Summary......Page 960
What’s a Transaction?......Page 961
MySQL’s Transactional Capabilities......Page 962
Table Creation......Page 963
A Sample Project......Page 964
The trunks Table......Page 965
Executing an Example Transaction......Page 966
Backing Up and Restoring InnoDB Tables......Page 968
Building Transactional Applications with PHP......Page 969
The Swap Meet Revisited......Page 970
Summary......Page 973
Importing and Exporting Data......Page 975
Using Data Delimitation......Page 976
Importing Data with LOAD DATA INFILE......Page 977
A Simple Data Import Example......Page 980
Security and LOAD DATA INFILE......Page 981
Useful Options......Page 982
mysqlimport Data Import Examples......Page 984
Writing a mysqlimport Script......Page 985
Loading Table Data with PHP......Page 986
SELECT INTO OUTFILE......Page 987
Usage Tips......Page 988
Exporting MySQL Data to Microsoft Excel......Page 989
Summary......Page 991
Index......Page 993