Web Database Applications with PHP & MySQL, 2nd Edition

Table of Contents......Page 7
What This Book Is About......Page 11
What You Need to Know......Page 12
How This Book Is Organized......Page 13
How to Use This Book......Page 17
Conventions Used in This Book......Page 18
How to Contact Us......Page 19
Acknowledgments......Page 20
Database Applications and the Web......Page 21
The Web......Page 22
Three-Tier Architectures......Page 23
HTTP example......Page 24
State......Page 25
Thickening the Client in the Three-Tier Model......Page 26
Web servers......Page 27
Web Scripting with PHP......Page 28
Introducing PHP5......Page 29
The Database Tier......Page 30
SQL......Page 31
Why use a database server?......Page 32
Examples of when to use a database server......Page 33
Introducing MySQL 4......Page 34
Introducing PHP......Page 36
PHP Basics......Page 37
Creating PHP scripts......Page 39
Outputting data with echo and print......Page 40
String Literals......Page 41
Variable substitution......Page 42
Character encoding......Page 43
Types......Page 44
Constants......Page 45
Expressions, Operators, and Variable Assignment......Page 46
if...else Statement......Page 48
switch Statement......Page 50
Conditional Expressions......Page 51
while......Page 53
for......Page 54
Changing Loop Behavior......Page 55
Functions......Page 56
Type Conversion......Page 57
Automatic Type Conversion......Page 59
Is-identical and is-not-identical operators......Page 60
Debugging with gettype(), print_r(), and var_dump()......Page 61
Testing, setting, and unsetting variables......Page 62
User-Defined Functions......Page 63
Parameter Types and Return Types......Page 65
Variable Scope......Page 66
Static variables......Page 67
Passing parameters by reference......Page 68
Assigning by reference......Page 69
Reusing Functions with Include and Require Files......Page 70
Managing include files......Page 72
A Working Example......Page 73
Arrays......Page 77
Creating Arrays......Page 78
Associative arrays......Page 79
Array order......Page 80
Multidimensional arrays......Page 81
Using foreach Loops with Arrays......Page 83
Functions that create arrays......Page 85
Exploding and imploding strings......Page 86
Finding values in arrays with in_array() and array_search()......Page 87
Keys and values......Page 89
Joining two or more arrays......Page 90
Sorting with sort() and rsort()......Page 91
Sorting on keys......Page 94
Sorting with user-defined element comparison......Page 95
Length of a String......Page 96
Creating formatted output with sprintf() and printf()......Page 97
Padding strings......Page 99
Trimming whitespace......Page 100
Finding and Extracting Substrings......Page 101
Finding the position of a substring......Page 102
Extracting a found portion of a string......Page 104
Replacing substrings......Page 105
Translating characters and substrings......Page 106
Regular Expression Syntax......Page 107
Character lists......Page 108
Anchors......Page 109
Optional and repeating characters......Page 110
Groups......Page 111
Escaping special characters......Page 112
Metacharacters......Page 113
Finding and extracting values......Page 114
Replacing substrings......Page 115
Splitting a string into an array......Page 116
Creating timestamps with mktime() and gmmktime()......Page 117
String to timestamp......Page 118
Subsecond times......Page 119
Formatting a Date......Page 120
Validating a Date......Page 122
Absolute Value......Page 123
Number Systems......Page 124
Powers and Logs......Page 125
Random Number Generation......Page 126
Classes and Objects......Page 128
Member Functions......Page 131
Using include Files for Class Definitions......Page 132
Constructors......Page 133
Destructors......Page 135
Private Members Variables......Page 136
Private Member Functions......Page 137
Static Member Variables......Page 138
Static Member Functions......Page 141
Cloning in PHP5......Page 142
Cloning in PHP4......Page 143
Inheritance......Page 144
Calling Parent Constructors......Page 146
Redefined Functions......Page 147
Protected Member Variables and Functions......Page 149
Final Functions......Page 150
Throwing and Catching Exceptions......Page 151
The Exception Class......Page 152
SQL and MySQL......Page 154
Introducing Relational Databases......Page 155
Database Terminology......Page 157
The Winestore Database......Page 158
The winestore entity-relationship model......Page 159
MySQL Command Interpreter......Page 160
Creating Databases......Page 162
Creating Tables......Page 163
Tables and attributes......Page 165
Keys......Page 166
Deleting Databases and Tables......Page 167
Inserting Data......Page 168
Defaults......Page 169
Auto-increment......Page 170
Updating Data......Page 171
Basic Querying......Page 172
WHERE Clauses......Page 173
ORDER BY......Page 175
GROUP BY......Page 176
HAVING......Page 177
Combining clauses......Page 178
DISTINCT......Page 179
Join Queries......Page 180
Beware of the Cartesian Product......Page 181
Elementary Natural Joins......Page 182
Examples......Page 184
Joins with More than Two Tables......Page 185
Case Study: Adding a New Wine......Page 188
Querying Web Databases......Page 191
Opening and Using a Database Connection......Page 192
Using mysql_fetch_array()......Page 195
Error Handling of MySQL Database Functions......Page 196
Working with Table Structures......Page 198
Formatting Results......Page 200
Using Require Files in Practice......Page 203
Case Study: Producing a Select List......Page 204
Implementing the selectDistinct( ) function......Page 207
Processing User Input......Page 208
Passing Data from the Browser to the Server......Page 209
Passing Data with the HTML Form Environment......Page 210
Passing Data with URLs......Page 212
Passing Data with Embedded Links......Page 213
More on Accessing User Data......Page 214
Before PHP 4.2......Page 215
The MULTIPLE attribute......Page 216
Other form issues......Page 217
Security and User Data......Page 218
Querying with User Input......Page 223
One-Component Querying......Page 225
Frequently Used Functions......Page 228
Other Functions......Page 234
Functions to Avoid......Page 238
Overview......Page 239
Core Components......Page 240
Unix systems—PHP 4.3.0 and later......Page 241
Using PEAR DB......Page 242
Getting started......Page 243
Handling errors in PEAR DB......Page 245
Essential functions for accessing MySQL with PEAR DB......Page 246
Using the PEAR installer......Page 250
Using HTML Templates......Page 252
Working with blocks and placeholders......Page 253
Nested blocks......Page 257
Preserving and removing blocks......Page 260
More on nesting and optional blocks......Page 261
Extended Integrated Templates (ITX)......Page 263
Essential IT and ITX functions......Page 265
Console......Page 267
HTML......Page 268
Networking......Page 269
Web services......Page 270
Database Inserts, Updates, and Deletes......Page 271
Reloading Data and Relocation Techniques......Page 275
Solving the reload problem in practice......Page 277
Inserting data......Page 280
Updating data......Page 285
Case study: updates in practice......Page 286
Issues in Writing Data to Databases......Page 290
Transactions and Concurrency......Page 291
When and how to lock tables......Page 293
The LOCK TABLES and UNLOCK TABLES statements in MySQL......Page 295
Locking Tables in Web Database Applications......Page 296
Locking methods that don’t work in web database applications......Page 298
Locking with an auxiliary table......Page 299
Managing identifiers with PEAR DB......Page 302
Validation and Error Reporting Principles......Page 305
Models That Don’t Work......Page 306
Server-Side Validation with PHP......Page 307
Basic techniques......Page 308
Validating Zip and postcodes......Page 310
Validating email addresses......Page 312
Validating URLs......Page 315
Validating numbers......Page 316
Validating credit cards......Page 318
Dates......Page 321
Times......Page 324
Using the PEAR Date package......Page 325
Logic, the date function, and MySQL......Page 326
JavaScript and Client-Side Validation......Page 327
Introducing JavaScript......Page 328
JavaScript and PHP......Page 330
Generating output......Page 331
Loops and conditionals......Page 332
Debugging JavaScript......Page 333
Objects......Page 334
Events......Page 336
Methods and properties......Page 337
A password form validation function......Page 340
Rollover presentation with mouseover events......Page 341
Prefilling form data with JavaScript calculations......Page 342
Interacting with the web browser......Page 343
Which browser is the user using?......Page 344
Drop-down menus......Page 345
The JavaScript validation script......Page 346
Using the JavaScript validation function......Page 353
The PHP and template components......Page 355
Sessions......Page 358
Introducing Session Management......Page 359
PHP Session Management......Page 360
Using Session Variables......Page 361
Session variable types......Page 363
Ending a Session......Page 364
Session to track authenticated users......Page 365
Sessions to track anonymous users......Page 366
Case Study: Using Sessions in Validation......Page 367
Improving error messages......Page 368
The final validation script......Page 371
Displaying previously entered form values......Page 373
The final phonebook entry script......Page 374
Performance......Page 377
Need for centralized session store......Page 378
Bookmark restrictions......Page 379
Functions for Accessing Sessions in PHP......Page 380
Functions used when register_globals is enabled......Page 381
No cookie?......Page 382
URL rewriting......Page 383
Garbage Collection......Page 384
Configuration Parameters......Page 385
HTTP Authentication......Page 389
How HTTP Authentication Works......Page 390
Using Apache to Authenticate......Page 392
HTTP Authentication with PHP......Page 393
Accessing User Credentials......Page 394
Managing HTTP Authentication with PHP......Page 395
Limiting Access by IP Address......Page 397
Protecting passwords......Page 399
Authenticating......Page 403
Encrypting other data in a database......Page 404
Reasons to Avoid HTTP Authentication......Page 405
Authentication and Session-Based Applications......Page 406
Recording IP addresses to detect session hijack attempts......Page 407
Code overview......Page 408
Setup script......Page 409
The authentication.inc require file......Page 410
Application scripts and pages......Page 412
Logout script......Page 413
Password management......Page 414
Protecting Data on the Web......Page 417
The Secure Sockets Layer Protocol......Page 418
SSL architecture......Page 419
SSL sessions......Page 420
Certificates and certification authorities......Page 421
Errors......Page 422
Accessing the PHP Manual......Page 425
Configuring Error Reporting......Page 426
A Page That Produces Partial or No Output......Page 427
Variable Problems......Page 428
Variable naming......Page 429
Complaints about headers......Page 430
Source shown in the browser......Page 431
A Basic Custom Handler......Page 432
Including debugging information......Page 435
Logging and notifying the user......Page 437
Triggering your own errors......Page 439
Cleaning up the application......Page 441
Creating a Report......Page 442
Portable Document Format (PDF)......Page 443
HTML and XML......Page 445
Email and plain text......Page 446
Hello, world......Page 447
A Full-Featured Document......Page 448
A Database Example......Page 452
Creating a Report......Page 455
EZPDF Class......Page 460
Base Class......Page 470
Working with Class Hierarchies......Page 477
Discovering Relationships......Page 480
Functions......Page 481
The abstract keyword......Page 482
Interfaces......Page 485
Review of the FreightCalculator......Page 489
Deliverable Interface......Page 490
Improving the FreightCalculator......Page 491
Summary of Improvements......Page 494
Using the Improved Freight Calculator......Page 495
Class Diagram......Page 497
Advanced SQL......Page 499
Exploring with SHOW......Page 500
Advanced Join Types......Page 501
Natural and inner joins......Page 502
Left and right joins......Page 503
Unions......Page 504
Aliases......Page 505
Introduction......Page 507
The IN clause......Page 508
The EXISTS clause......Page 509
Nested queries in the FROM clause......Page 512
User Variables......Page 513
ROLLUP with GROUP BY......Page 515
Other MySQL Topics......Page 516
Altering Databases......Page 517
More on Inserting Data......Page 518
Using INSERT with SELECT......Page 519
Using CREATE TABLE with SELECT......Page 520
Replacing data......Page 521
Bulk loading a file into a database......Page 522
More on Deleting Data......Page 523
Functions......Page 525
Arithmetic and Comparison Operators......Page 526
String functions......Page 527
Mathematical functions......Page 529
Date and time functions......Page 531
Miscellaneous operators and functions......Page 532
Automating Querying......Page 533
Unix......Page 534
Microsoft Windows......Page 535
Overview......Page 536
MyISAM......Page 538
InnoDB......Page 539
Transactions using COMMIT and ROLLBACK......Page 540
Backup and Recovery......Page 542
Backup......Page 543
Restore......Page 544
Checking and Fixing Tables......Page 545
Managing Users and Privileges......Page 547
Privileges and scope......Page 548
Network access......Page 550
How MySQL manages privileges......Page 551
Index Design......Page 552
Design Tips......Page 555
Server Tuning Tips......Page 557
Query Caching......Page 558
Configuring query caching......Page 559
Controlling query caching......Page 560
Hugh and Dave’s Online Wines: A Case Study......Page 561
Requirements List......Page 562
Application Overview......Page 564
Functional overview......Page 565
Using and accessing the source code......Page 570
Application Templates......Page 571
The winestoreTemplate Class......Page 572
How the class works......Page 574
The buttons and the button parameter......Page 580
The winestoreFormTemplate Class......Page 581
How the class works......Page 583
Validation......Page 589
Custom Error Handler......Page 594
General-Purpose Functions......Page 596
Managing Customers......Page 603
Code Overview......Page 604
Customer Validation......Page 607
The Customer Form......Page 611
The Customer Receipt Page......Page 613
The Shopping Cart......Page 616
Code Overview......Page 617
The Winestore Home Page......Page 618
The Shopping Cart Implementation......Page 624
Viewing the Shopping Cart......Page 626
Adding Items to the Shopping Cart......Page 629
Emptying the Shopping Cart......Page 633
Updating the Shopping Cart Quantities......Page 634
Code Overview......Page 638
Credit Card and Shipping Instructions......Page 640
Finalizing Orders......Page 644
Email Receipt......Page 650
The PEAR Mail package......Page 656
HTML Receipts......Page 657
Searching and Authentication in the Online Winestore......Page 662
Searching and Browsing......Page 663
Authentication......Page 665
Search Criteria Form......Page 668
Querying and Displaying Results......Page 669
Finding the wines......Page 674
Displaying the wines......Page 675
General-Purpose Functions......Page 678
Logging In and Out......Page 681
Changing Passwords......Page 684
Finding Out What’s Installed......Page 687
MySQL......Page 688
Apache and PHP......Page 689
Installing MySQL......Page 691
Configuring MySQL......Page 694
Installing Apache......Page 695
Installing OpenSSL......Page 696
Creating a key and a certificate......Page 697
Installing a Regular Apache Server......Page 698
Installing PHP......Page 700
Installing the Code Examples......Page 703
Loading the Winestore Database......Page 705
Installing the Winestore Application......Page 706
Installation Overview......Page 708
Installing with EasyPHP......Page 709
Installing PEAR Packages......Page 713
Installing the Code Examples......Page 714
Loading the Winestore Database......Page 715
Installing the Winestore Application......Page 716
Getting Started......Page 718
Installing MySQL......Page 719
Configuring MySQL......Page 721
Setting Up Apache and PHP......Page 722
The Apache and PHP Setup......Page 723
Installing a Secure Apache Server......Page 725
Creating a key and certificate......Page 726
Installing PEAR Packages......Page 728
Installing the Code Examples......Page 729
Installing the PDF PHP Library......Page 730
Loading the Winestore Database......Page 731
Installing the Winestore Application......Page 732
TCP/IP......Page 734
IP Addresses......Page 735
Hypertext Transfer Protocol......Page 736
Nonstandard TCP ports......Page 737
Fragment identifiers......Page 738
Absolute and relative URLs......Page 739
HTTP Requests......Page 740
Request methods......Page 741
HTTP Responses......Page 742
Status codes......Page 743
Caching......Page 744
Case Study: Relationships in the Winestore......Page 746
Entity-Relationship Modeling......Page 748
Identifying entities in ER modeling......Page 749
Identifying relationships in ER modeling......Page 752
Relationships in the winestore ER model......Page 753
Identifying key attributes in ER modeling......Page 756
Other ER modeling tools......Page 757
Converting an Entity-Relationship Model to SQL......Page 758
Step 1: Convert regular entities to tables......Page 759
Step 2: Convert weak entities to tables......Page 762
Step 4: One-to-many relationships......Page 763
Step 5: Many-to-many relationships......Page 765
Using a Database to Keep State......Page 766
PHP Session Management......Page 767
PHP Session Management Storage Methods......Page 769
Building User-Defined Storage Handlers......Page 770
Session Table Structure......Page 771
Handler Implementations......Page 772
Session open handler......Page 773
Session read handler......Page 774
Session write handler......Page 775
Garbage collection handler......Page 777
Using the User-Defined Session Handler Code......Page 778
PEAR’s HTTP_Session Package......Page 780
Client Tier Resources......Page 781
Networking and Web Resources......Page 782
PHP resources......Page 783
Security and Cryptography Resources......Page 784
New Features......Page 786
Migrating to the New Library......Page 788
Basic Features......Page 791
Inserting data......Page 792
Retrieving data......Page 795
Profiling Queries......Page 797
Index......Page 799