Cover image for Web Database Applications with PHP and MySQL, 2nd Edition

Book description

There are many reasons for serving up dynamic content from a web site: to offer an online shopping site, create customized information pages for users, or just manage a large volume of content through a database. Anyone with a modest knowledge of HTML and web site management can learn to create dynamic content through the PHP programming language and the MySQL database. This book gives you the background and tools to do the job safely and reliably. Web Database Applications with PHP and MySQL, Second Edition thoroughly reflects the needs of real-world applications. It goes into detail on such practical issues as validating input (do you know what a proper credit card number looks like?), logging in users, and using templates to give your dynamic web pages a standard look. But this book goes even further. It shows how JavaScript and PHP can be used in tandem to make a user's experience faster and more pleasant. It shows the correct way to handle errors in user input so that a site looks professional. It introduces the vast collection of powerful tools available in the PEAR repository and shows how to use some of the most popular tools. Even while it serves as an introduction to new programmers, the book does not omit critical tasks that web sites require. For instance, every site that allows updates must handle the possibility of multiple users accessing data at the same time. This book explains how to solve the problem in detail with locking. Through a sophisticated sample application--Hugh and Dave's Wine Store--all the important techniques of dynamic content are introduced. Good design is emphasized, such as dividing logic from presentation. The book introduces PHP 5 and MySQL 4.1 features, while providing techniques that can be used on older versions of the software that are still in widespread use. This new edition has been redesigned around the rich offerings of PEAR. Several of these, including the Template package and the database-independent query API, are fully integrated into examples and thoroughly described in the text. Topics include:

  • Installation and configuration of Apache, MySQL, and PHP on Unix®, Windows®, and Mac OS® X systems

  • Introductions to PHP, SQL, and MySQL administration

  • Session management, including the use of a custom database for improved efficiency

  • User input validation, security, and authentication

  • The PEAR repository, plus details on the use of PEAR DB and Template classes

  • Production of PDF reports

Table of Contents

  1. Web Database Applications with PHP and MySQL, 2nd Edition
  2. A Note Regarding Supplemental Files
  3. Preface
    1. What This Book Is About
    2. What You Need to Know
    3. How This Book Is Organized
    4. How to Use This Book
    5. Conventions Used in This Book
    6. Using Code Examples
    7. How to Contact Us
    8. Web Site and Code Examples
    9. Acknowledgments
  4. 1. Database Applications and the Web
    1. 1.1. The Web
    2. 1.2. Three-Tier Architectures
      1. 1.2.1. HTTP: the Hypertext Transfer Protocol
        1. 1.2.1.1. HTTP example
      2. 1.2.2. State
      3. 1.2.3. Thickening the Client in the Three-Tier Model
      4. 1.2.4. The Middle Tier
        1. 1.2.4.1. Web servers
      5. 1.2.5. Web Scripting with PHP
        1. 1.2.5.1. Introducing PHP5
      6. 1.2.6. The Database Tier
      7. 1.2.7. Database Management Systems
        1. 1.2.7.1. SQL
        2. 1.2.7.2. Why use a database server?
        3. 1.2.7.3. Examples of when to use a database server
        4. 1.2.7.4. Examples of when not to use a DBMS
        5. 1.2.7.5. The MySQL server
        6. 1.2.7.6. Introducing MySQL 4
  5. 2. The PHP Scripting Language
    1. 2.1. Introducing PHP
      1. 2.1.1. PHP Basics
        1. 2.1.1.1. Creating PHP scripts
        2. 2.1.1.2. Comments
        3. 2.1.1.3. Outputting data with echo and print
      2. 2.1.2. String Literals
        1. 2.1.2.1. Variable substitution
        2. 2.1.2.2. Character encoding
      3. 2.1.3. Variables
      4. 2.1.4. Types
      5. 2.1.5. Constants
      6. 2.1.6. Expressions, Operators, and Variable Assignment
        1. 2.1.6.1. Operator precedence
    2. 2.2. Conditions and Branches
      1. 2.2.1. if...else Statement
      2. 2.2.2. switch Statement
      3. 2.2.3. Conditional Expressions
    3. 2.3. Loops
      1. 2.3.1. while
      2. 2.3.2. do...while
      3. 2.3.3. for
      4. 2.3.4. Changing Loop Behavior
    4. 2.4. Functions
    5. 2.5. Working with Types
      1. 2.5.1. Type Conversion
      2. 2.5.2. Automatic Type Conversion
      3. 2.5.3. Examining Variable Type and Content
        1. 2.5.3.1. Is-identical and is-not-identical operators
        2. 2.5.3.2. Debugging with gettype( ), print_r( ), and var_dump( )
        3. 2.5.3.3. Testing, setting, and unsetting variables
    6. 2.6. User-Defined Functions
      1. 2.6.1. Parameter Types and Return Types
      2. 2.6.2. Variable Scope
        1. 2.6.2.1. Global variables
        2. 2.6.2.2. Static variables
      3. 2.6.3. Passing Variables to Functions
        1. 2.6.3.1. Passing parameters by reference
        2. 2.6.3.2. Assigning by reference
        3. 2.6.3.3. Default parameter values
      4. 2.6.4. Reusing Functions with Include and Require Files
        1. 2.6.4.1. Managing include files
    7. 2.7. A Working Example
  6. 3. Arrays, Strings, and Advanced Data Manipulation in PHP
    1. 3.1. Arrays
      1. 3.1.1. Creating Arrays
        1. 3.1.1.1. Associative arrays
        2. 3.1.1.2. Removing elements from an array
        3. 3.1.1.3. Array order
        4. 3.1.1.4. Heterogeneous arrays
        5. 3.1.1.5. Multidimensional arrays
      2. 3.1.2. Using foreach Loops with Arrays
      3. 3.1.3. Basic Array Functions
        1. 3.1.3.1. Counting elements in arrays
        2. 3.1.3.2. Functions that create arrays
        3. 3.1.3.3. Exploding and imploding strings
        4. 3.1.3.4. Finding the maximum and minimum values in an array
        5. 3.1.3.5. Finding values in arrays with in_array( ) and array_search( )
        6. 3.1.3.6. Keys and values
        7. 3.1.3.7. Joining two or more arrays
        8. 3.1.3.8. Reordering elements with array_reverse( )
      4. 3.1.4. Sorting Arrays
        1. 3.1.4.1. Sorting with sort( ) and rsort( )
        2. 3.1.4.2. Sorting associative arrays
        3. 3.1.4.3. Sorting on keys
        4. 3.1.4.4. Sorting with user-defined element comparison
    2. 3.2. Strings
      1. 3.2.1. Length of a String
      2. 3.2.2. Printing and Formatting Strings
        1. 3.2.2.1. Creating formatted output with sprintf( ) and printf( )
        2. 3.2.2.2. Padding strings
        3. 3.2.2.3. Changing case
        4. 3.2.2.4. Trimming whitespace
      3. 3.2.3. Comparing Strings
      4. 3.2.4. Finding and Extracting Substrings
        1. 3.2.4.1. Extracting a substring from a string
        2. 3.2.4.2. Finding the position of a substring
        3. 3.2.4.3. Extracting a found portion of a string
      5. 3.2.5. Replacing Characters and Substrings
        1. 3.2.5.1. Replacing substrings
        2. 3.2.5.2. Translating characters and substrings
    3. 3.3. Regular Expressions
      1. 3.3.1. Regular Expression Syntax
        1. 3.3.1.1. Characters and wildcards
        2. 3.3.1.2. Character lists
        3. 3.3.1.3. Anchors
        4. 3.3.1.4. Optional and repeating characters
        5. 3.3.1.5. Groups
        6. 3.3.1.6. Alternative patterns
        7. 3.3.1.7. Escaping special characters
        8. 3.3.1.8. Metacharacters
      2. 3.3.2. Regular Expression Functions
        1. 3.3.2.1. Finding and extracting values
        2. 3.3.2.2. Replacing substrings
        3. 3.3.2.3. Splitting a string into an array
    4. 3.4. Dates and Times
      1. 3.4.1. Generating a Timestamp
        1. 3.4.1.1. Current time
        2. 3.4.1.2. Creating timestamps with mktime( ) and gmmktime( )
        3. 3.4.1.3. String to timestamp
        4. 3.4.1.4. Subsecond times
      2. 3.4.2. Formatting a Date
      3. 3.4.3. Validating a Date
    5. 3.5. Integers and Floats
      1. 3.5.1. Absolute Value
      2. 3.5.2. Ceiling and Floor
      3. 3.5.3. Rounding
      4. 3.5.4. Number Systems
      5. 3.5.5. Basic Trigonometry Functions
      6. 3.5.6. Powers and Logs
      7. 3.5.7. Testing Number Results
      8. 3.5.8. Random Number Generation
  7. 4. Introduction to Object-Oriented Programming with PHP 5
    1. 4.1. Classes and Objects
      1. 4.1.1. Member Variables
      2. 4.1.2. Member Functions
      3. 4.1.3. Using include Files for Class Definitions
      4. 4.1.4. Constructors
      5. 4.1.5. Destructors
      6. 4.1.6. Private Members Variables
      7. 4.1.7. Private Member Functions
      8. 4.1.8. Static Member Variables
      9. 4.1.9. Static Member Functions
      10. 4.1.10. Cloning Objects
        1. 4.1.10.1. Cloning in PHP5
        2. 4.1.10.2. Cloning in PHP4
    2. 4.2. Inheritance
      1. 4.2.1. Calling Parent Constructors
      2. 4.2.2. Redefined Functions
      3. 4.2.3. Protected Member Variables and Functions
      4. 4.2.4. Final Functions
    3. 4.3. Throwing and Catching Exceptions
      1. 4.3.1. The Exception Class
  8. 5. SQL and MySQL
    1. 5.1. Database Basics
      1. 5.1.1. Introducing Relational Databases
      2. 5.1.2. Database Terminology
      3. 5.1.3. The Winestore Database
        1. 5.1.3.1. The winestore entity-relationship model
    2. 5.2. MySQL Command Interpreter
    3. 5.3. Managing Databases and Tables
      1. 5.3.1. Creating Databases
      2. 5.3.2. Creating Tables
        1. 5.3.2.1. Tables and attributes
        2. 5.3.2.2. Modifiers
        3. 5.3.2.3. Keys
      3. 5.3.3. Deleting Databases and Tables
    4. 5.4. Inserting, Updating, and Deleting Data
      1. 5.4.1. Inserting Data
        1. 5.4.1.1. Defaults
        2. 5.4.1.2. Auto-increment
      2. 5.4.2. Deleting Data
      3. 5.4.3. Updating Data
    5. 5.5. Querying with SQL SELECT
      1. 5.5.1. Basic Querying
      2. 5.5.2. WHERE Clauses
      3. 5.5.3. Sorting and Grouping Output
        1. 5.5.3.1. ORDER BY
        2. 5.5.3.2. GROUP BY
        3. 5.5.3.3. HAVING
        4. 5.5.3.4. Combining clauses
        5. 5.5.3.5. DISTINCT
      4. 5.5.4. Limiting Output in MySQL
    6. 5.6. Join Queries
      1. 5.6.1. Beware of the Cartesian Product
      2. 5.6.2. Elementary Natural Joins
        1. 5.6.2.1. Examples
        2. 5.6.2.2. Using DISTINCT in joins
      3. 5.6.3. Joins with More than Two Tables
    7. 5.7. Case Study: Adding a New Wine
  9. 6. Querying Web Databases
    1. 6.1. Querying a MySQL Database Using PHP
      1. 6.1.1. Opening and Using a Database Connection
      2. 6.1.2. Using mysql_fetch_array( )
      3. 6.1.3. Error Handling of MySQL Database Functions
      4. 6.1.4. Working with Table Structures
      5. 6.1.5. Formatting Results
      6. 6.1.6. Using Require Files in Practice
      7. 6.1.7. Case Study: Producing a Select List
        1. 6.1.7.1. Implementing the selectDistinct( ) function
    2. 6.2. Processing User Input
      1. 6.2.1. Passing Data from the Browser to the Server
      2. 6.2.2. Passing Data with the HTML Form Environment
      3. 6.2.3. Passing Data with URLs
      4. 6.2.4. Passing Data with Embedded Links
      5. 6.2.5. More on Accessing User Data
        1. 6.2.5.1. Before PHP 4.2
      6. 6.2.6. Processing Form Data
        1. 6.2.6.1. The MULTIPLE attribute
        2. 6.2.6.2. Other form issues
      7. 6.2.7. Security and User Data
      8. 6.2.8. Querying with User Input
      9. 6.2.9. One-Component Querying
    3. 6.3. MySQL Function Reference
      1. 6.3.1. Frequently Used Functions
      2. 6.3.2. Other Functions
      3. 6.3.3. Functions to Avoid
  10. 7. PEAR
    1. 7.1. Overview
    2. 7.2. Core Components
      1. 7.2.1. What's Installed?
        1. 7.2.1.1. Unix systems—PHP 4.3.0 and later
        2. 7.2.1.2. Microsoft Windows—PHP 4.3.2 and later
      2. 7.2.2. Using PEAR DB
        1. 7.2.2.1. Should I use PEAR DB?
        2. 7.2.2.2. Getting started
        3. 7.2.2.3. Handling errors in PEAR DB
        4. 7.2.2.4. Essential functions for accessing MySQL with PEAR DB
    3. 7.3. Packages
      1. 7.3.1. Installing, Upgrading, and Understanding Packages
        1. 7.3.1.1. Finding out about packages
        2. 7.3.1.2. Using the PEAR installer
      2. 7.3.2. Using HTML Templates
        1. 7.3.2.1. Working with blocks and placeholders
        2. 7.3.2.2. Nested blocks
        3. 7.3.2.3. Preserving and removing blocks
        4. 7.3.2.4. More on nesting and optional blocks
        5. 7.3.2.5. Extended Integrated Templates (ITX)
        6. 7.3.2.6. Essential IT and ITX functions
      3. 7.3.3. Optional Packages
        1. 7.3.3.1. Authentication
        2. 7.3.3.2. Benchmarking
        3. 7.3.3.3. Caching
        4. 7.3.3.4. Console
        5. 7.3.3.5. Database
        6. 7.3.3.6. Date
        7. 7.3.3.7. Filesystem
        8. 7.3.3.8. HTML
        9. 7.3.3.9. HTTP
        10. 7.3.3.10. Internationalization
        11. 7.3.3.11. Logging
        12. 7.3.3.12. Mail
        13. 7.3.3.13. Networking
        14. 7.3.3.14. PEAR
        15. 7.3.3.15. PHP
        16. 7.3.3.16. XML
        17. 7.3.3.17. Web services
  11. 8. Writing to Web Databases
    1. 8.1. Database Inserts, Updates, and Deletes
      1. 8.1.1. Reloading Data and Relocation Techniques
        1. 8.1.1.1. Solving the reload problem in practice
      2. 8.1.2. Inserting, Updating, and Deleting Data
        1. 8.1.2.1. Inserting data
        2. 8.1.2.2. Updating data
        3. 8.1.2.3. Case study: updates in practice
        4. 8.1.2.4. Deleting data
    2. 8.2. Issues in Writing Data to Databases
      1. 8.2.1. Transactions and Concurrency
      2. 8.2.2. Locking to Achieve Concurrency in MySQL
        1. 8.2.2.1. When and how to lock tables
        2. 8.2.2.2. The LOCK TABLES and UNLOCK TABLES statements in MySQL
        3. 8.2.2.3. Locking for performance
      3. 8.2.3. Locking Tables in Web Database Applications
        1. 8.2.3.1. Locking methods that don't work in web database applications
        2. 8.2.3.2. Locking with an auxiliary table
        3. 8.2.3.3. Managing identifiers with PEAR DB
  12. 9. Validation with PHP and JavaScript
    1. 9.1. Validation and Error Reporting Principles
      1. 9.1.1. Models That Don't Work
      2. 9.1.2. Models That Do Work
    2. 9.2. Server-Side Validation with PHP
      1. 9.2.1. Mandatory Data
      2. 9.2.2. Validating Strings
        1. 9.2.2.1. Basic techniques
        2. 9.2.2.2. Validating Zip and postcodes
        3. 9.2.2.3. Validating email addresses
        4. 9.2.2.4. Validating URLs
        5. 9.2.2.5. Validating numbers
        6. 9.2.2.6. Validating credit cards
      3. 9.2.3. Validating Dates and Times
        1. 9.2.3.1. Dates
        2. 9.2.3.2. Times
        3. 9.2.3.3. Using the PEAR Date package
        4. 9.2.3.4. Logic, the date function, and MySQL
    3. 9.3. JavaScript and Client-Side Validation
      1. 9.3.1. Introducing JavaScript
      2. 9.3.2. JavaScript and PHP
        1. 9.3.2.1. Generating output
        2. 9.3.2.2. Loops and conditionals
        3. 9.3.2.3. Functions
        4. 9.3.2.4. Debugging JavaScript
        5. 9.3.2.5. Objects
        6. 9.3.2.6. Events
        7. 9.3.2.7. Methods and properties
      3. 9.3.3. JavaScript Examples
        1. 9.3.3.1. A password form validation function
        2. 9.3.3.2. Rollover presentation with mouseover events
        3. 9.3.3.3. Prefilling form data with JavaScript calculations
        4. 9.3.3.4. Interacting with the web browser
        5. 9.3.3.5. Which browser is the user using?
        6. 9.3.3.6. Drop-down menus
      4. 9.3.4. Case Study: A Generic JavaScript Validation Function
        1. 9.3.4.1. The JavaScript validation script
        2. 9.3.4.2. Using the JavaScript validation function
        3. 9.3.4.3. The PHP and template components
  13. 10. Sessions
    1. 10.1. Introducing Session Management
    2. 10.2. PHP Session Management
      1. 10.2.1. Starting a Session
      2. 10.2.2. Using Session Variables
        1. 10.2.2.1. Unsetting session variables
        2. 10.2.2.2. Session variable types
        3. 10.2.2.3. Serialization of session variables
      3. 10.2.3. Ending a Session
      4. 10.2.4. Designing Session-Based Applications
        1. 10.2.4.1. Session to track authenticated users
        2. 10.2.4.2. Sessions to track anonymous users
    3. 10.3. Case Study: Using Sessions in Validation
      1. 10.3.1. Improving the Phonebook Details Form
      2. 10.3.2. The Validation Script
        1. 10.3.2.1. Improving error messages
        2. 10.3.2.2. Saving last-entered values as a session variable
        3. 10.3.2.3. The final validation script
      3. 10.3.3. The Phonebook Entry Form Script
        1. 10.3.3.1. Displaying previously entered form values
        2. 10.3.3.2. Displaying error messages
        3. 10.3.3.3. The final phonebook entry script
    4. 10.4. When to Use Sessions
      1. 10.4.1. Reasons to Use Sessions
        1. 10.4.1.1. Performance
        2. 10.4.1.2. Sequence of interaction
        3. 10.4.1.3. Intermediate results
        4. 10.4.1.4. Personalization
      2. 10.4.2. Reasons to Avoid Sessions
        1. 10.4.2.1. Need for centralized session store
        2. 10.4.2.2. Performance
        3. 10.4.2.3. Timeouts
        4. 10.4.2.4. Bookmark restrictions
        5. 10.4.2.5. Security
    5. 10.5. PHP Session API and Configuration
      1. 10.5.1. Functions for Accessing Sessions in PHP
        1. 10.5.1.1. Functions used when register_globals is enabled
      2. 10.5.2. Session Management Without Cookies
        1. 10.5.2.1. No cookie?
        2. 10.5.2.2. Including the session ID in URLs
        3. 10.5.2.3. URL rewriting
        4. 10.5.2.4. Turning off cookies
      3. 10.5.3. Garbage Collection
      4. 10.5.4. Configuration Parameters
  14. 11. Authentication and Security
    1. 11.1. HTTP Authentication
      1. 11.1.1. How HTTP Authentication Works
      2. 11.1.2. Using Apache to Authenticate
    2. 11.2. HTTP Authentication with PHP
      1. 11.2.1. Accessing User Credentials
      2. 11.2.2. Managing HTTP Authentication with PHP
      3. 11.2.3. Limiting Access by IP Address
      4. 11.2.4. Authentication Using a Database
        1. 11.2.4.1. Creating a database and table
        2. 11.2.4.2. Protecting passwords
        3. 11.2.4.3. Authenticating
        4. 11.2.4.4. Encrypting other data in a database
    3. 11.3. Form-Based Authentication
      1. 11.3.1. Reasons to Use HTTP Authentication
      2. 11.3.2. Reasons to Avoid HTTP Authentication
      3. 11.3.3. Authentication and Session-Based Applications
        1. 11.3.3.1. Session hijacking
        2. 11.3.3.2. Recording IP addresses to detect session hijack attempts
      4. 11.3.4. Session-Based Authentication Framework
        1. 11.3.4.1. Code overview
        2. 11.3.4.2. Login page
        3. 11.3.4.3. Setup script
        4. 11.3.4.4. The authentication.inc require file
        5. 11.3.4.5. Application scripts and pages
        6. 11.3.4.6. Logout script
        7. 11.3.4.7. Password management
    4. 11.4. Protecting Data on the Web
      1. 11.4.1. The Secure Sockets Layer Protocol
        1. 11.4.1.1. SSL architecture
        2. 11.4.1.2. Cipher suites
        3. 11.4.1.3. SSL sessions
        4. 11.4.1.4. Certificates and certification authorities
  15. 12. Errors, Debugging, and Deployment
    1. 12.1. Errors
      1. 12.1.1. Accessing the PHP Manual
      2. 12.1.2. Configuring Error Reporting
    2. 12.2. Common Programming Errors
      1. 12.2.1. A Page That Produces Partial or No Output
      2. 12.2.2. Variable Problems
        1. 12.2.2.1. Variable naming
        2. 12.2.2.2. Missing output
      3. 12.2.3. Less Common Problems
        1. 12.2.3.1. Complaints about headers
        2. 12.2.3.2. Missing semicolons, braces, and quotes
        3. 12.2.3.3. Source shown in the browser
    3. 12.3. Custom Error Handlers
      1. 12.3.1. A Basic Custom Handler
      2. 12.3.2. A Production Error Handler
        1. 12.3.2.1. Including debugging information
        2. 12.3.2.2. Logging and notifying the user
        3. 12.3.2.3. Triggering your own errors
        4. 12.3.2.4. Cleaning up the application
  16. 13. Reporting
    1. 13.1. Creating a Report
      1. 13.1.1. Formats
        1. 13.1.1.1. Portable Document Format (PDF)
        2. 13.1.1.2. Rich Text Format (RTF)
        3. 13.1.1.3. PostScript
        4. 13.1.1.4. HTML and XML
        5. 13.1.1.5. Email and plain text
    2. 13.2. Producing PDF
      1. 13.2.1. Hello, world
      2. 13.2.2. A Full-Featured Document
      3. 13.2.3. A Database Example
      4. 13.2.4. Creating a Report
    3. 13.3. PDF-PHP Reference
      1. 13.3.1. EZPDF Class
      2. 13.3.2. Base Class
  17. 14. Advanced Features of Object-Oriented Programming in PHP 5
    1. 14.1. Working with Class Hierarchies
      1. 14.1.1. Polymorphism
      2. 14.1.2. Discovering Relationships
        1. 14.1.2.1. Functions
    2. 14.2. Class Type Hints
    3. 14.3. Abstract Classes and Interfaces
      1. 14.3.1. The abstract keyword
      2. 14.3.2. Interfaces
    4. 14.4. Freight Calculator Example
      1. 14.4.1. Review of the FreightCalculator
      2. 14.4.2. Deliverable Interface
      3. 14.4.3. Improving the FreightCalculator
      4. 14.4.4. Summary of Improvements
      5. 14.4.5. Using the Improved Freight Calculator
      6. 14.4.6. Class Diagram
  18. 15. Advanced SQL
    1. 15.1. Exploring with SHOW
    2. 15.2. Advanced Querying
      1. 15.2.1. Advanced Join Types
        1. 15.2.1.1. Natural and inner joins
        2. 15.2.1.2. Left and right joins
        3. 15.2.1.3. Unions
      2. 15.2.2. Aliases
      3. 15.2.3. Nested Queries
        1. 15.2.3.1. Introduction
        2. 15.2.3.2. The IN clause
        3. 15.2.3.3. The EXISTS clause
        4. 15.2.3.4. Nested queries in the FROM clause
      4. 15.2.4. User Variables
      5. 15.2.5. ROLLUP with GROUP BY
      6. 15.2.6. Other MySQL Topics
    3. 15.3. Manipulating Data and Databases
      1. 15.3.1. Altering Databases
      2. 15.3.2. More on Inserting Data
        1. 15.3.2.1. Using INSERT with SELECT
        2. 15.3.2.2. Using CREATE TABLE with SELECT
        3. 15.3.2.3. Replacing data
        4. 15.3.2.4. Bulk loading a file into a database
        5. 15.3.2.5. Delayed insertion
      3. 15.3.3. More on Deleting Data
      4. 15.3.4. More on Updating Data
    4. 15.4. Functions
      1. 15.4.1. Arithmetic and Comparison Operators
        1. 15.4.1.1. String functions
        2. 15.4.1.2. Mathematical functions
        3. 15.4.1.3. Date and time functions
        4. 15.4.1.4. Miscellaneous operators and functions
    5. 15.5. Automating Querying
      1. 15.5.1. Unix
      2. 15.5.2. Microsoft Windows
    6. 15.6. Table Types
      1. 15.6.1. Overview
      2. 15.6.2. MyISAM
      3. 15.6.3. InnoDB
        1. 15.6.3.1. Transactions using COMMIT and ROLLBACK
      4. 15.6.4. Heap
    7. 15.7. Backup and Recovery
      1. 15.7.1. Backup
      2. 15.7.2. Selective Backups
      3. 15.7.3. Restore
      4. 15.7.4. Checking and Fixing Tables
      5. 15.7.5. Exporting Data to Other Environments
    8. 15.8. Managing Users and Privileges
      1. 15.8.1. Creating Users and Privileges
        1. 15.8.1.1. Privileges and scope
        2. 15.8.1.2. Network access
      2. 15.8.2. Revoking Privileges
        1. 15.8.2.1. How MySQL manages privileges
    9. 15.9. Tuning MySQL
      1. 15.9.1. Index Design
      2. 15.9.2. Design Tips
      3. 15.9.3. Server Tuning Tips
      4. 15.9.4. Query Caching
        1. 15.9.4.1. Configuring query caching
        2. 15.9.4.2. Controlling query caching
  19. 16. Hugh and Dave's Online Wines:A Case Study
    1. 16.1. Functional and System Requirements
      1. 16.1.1. Requirements List
    2. 16.2. Application Overview
      1. 16.2.1. Winestore Scripts
        1. 16.2.1.1. Functional overview
        2. 16.2.1.2. Using and accessing the source code
    3. 16.3. Common Components
      1. 16.3.1. Application Templates
      2. 16.3.2. The winestoreTemplate Class
        1. 16.3.2.1. How the class works
        2. 16.3.2.2. The buttons and the button parameter
      3. 16.3.3. The winestoreFormTemplate Class
        1. 16.3.3.1. How the class works
      4. 16.3.4. Database Parameters
      5. 16.3.5. Validation
      6. 16.3.6. Custom Error Handler
      7. 16.3.7. General-Purpose Functions
  20. 17. Managing Customers
    1. 17.1. Code Overview
    2. 17.2. Customer Validation
    3. 17.3. The Customer Form
      1. 17.3.1. The Customer Receipt Page
  21. 18. The Shopping Cart
    1. 18.1. Code Overview
    2. 18.2. The Winestore Home Page
    3. 18.3. The Shopping Cart Implementation
      1. 18.3.1. Viewing the Shopping Cart
      2. 18.3.2. Adding Items to the Shopping Cart
      3. 18.3.3. Emptying the Shopping Cart
      4. 18.3.4. Updating the Shopping Cart Quantities
  22. 19. Ordering and Shipping at the Online Winestore
    1. 19.1. Code Overview
    2. 19.2. Credit Card and Shipping Instructions
    3. 19.3. Finalizing Orders
    4. 19.4. HTML and Email Receipts
      1. 19.4.1. Email Receipt
        1. 19.4.1.1. The PEAR Mail package
      2. 19.4.2. HTML Receipts
  23. 20. Searching and Authentication in the Online Winestore
    1. 20.1. Code Overview
      1. 20.1.1. Searching and Browsing
      2. 20.1.2. Authentication
    2. 20.2. Searching and Browsing
      1. 20.2.1. Search Criteria Form
      2. 20.2.2. Querying and Displaying Results
        1. 20.2.2.1. Finding the wines
        2. 20.2.2.2. Displaying the wines
    3. 20.3. Authentication
      1. 20.3.1. General-Purpose Functions
      2. 20.3.2. Logging In and Out
      3. 20.3.3. Changing Passwords
  24. A. Linux Installation Guide
    1. A.1. Finding Out What's Installed
      1. A.1.1. MySQL
      2. A.1.2. Apache and PHP
    2. A.2. Installation Overview
    3. A.3. Installing MySQL
      1. A.3.1. Starting MySQL
      2. A.3.2. Configuring MySQL
    4. A.4. Installing Apache
      1. A.4.1. Installing a Secure Apache Server
        1. A.4.1.1. Installing OpenSSL
        2. A.4.1.2. Creating a key and a certificate
      2. A.4.2. Installing a Regular Apache Server
    5. A.5. Installing PHP
    6. A.6. What's Needed for This Book
      1. A.6.1. Installing PEAR Packages
      2. A.6.2. Installing the Code Examples
      3. A.6.3. Installing the PDF PHP Library
      4. A.6.4. Loading the Winestore Database
      5. A.6.5. Installing the Winestore Application
  25. B. Microsoft Windows Installation Guide
    1. B.1. Installation Overview
    2. B.2. Installing with EasyPHP
    3. B.3. What's Needed for This Book
      1. B.3.1. Installing PEAR Packages
      2. B.3.2. Installing the Code Examples
      3. B.3.3. Installing the PDF PHP Library
      4. B.3.4. Loading the Winestore Database
      5. B.3.5. Installing the Winestore Application
  26. C. Mac OS X Installation Guide
    1. C.1. Getting Started
    2. C.2. Installing MySQL
      1. C.2.1. Configuring MySQL
    3. C.3. Setting Up Apache and PHP
      1. C.3.1. Starting Apache
      2. C.3.2. The Apache and PHP Setup
      3. C.3.3. Upgrading PHP
      4. C.3.4. Installing a Secure Apache Server
        1. C.3.4.1. Creating a key and certificate
    4. C.4. What's Needed for This Book
      1. C.4.1. Installing PEAR Packages
      2. C.4.2. Installing the Code Examples
      3. C.4.3. Installing the PDF PHP Library
      4. C.4.4. Loading the Winestore Database
      5. C.4.5. Installing the Winestore Application
  27. D. Web Protocols
    1. D.1. Network Basics
      1. D.1.1. TCP/IP
      2. D.1.2. IP Addresses
        1. D.1.2.1. Ports
    2. D.2. Hypertext Transfer Protocol
      1. D.2.1. Uniform Resource Locators
        1. D.2.1.1. Protocol
        2. D.2.1.2. Host and service identification
        3. D.2.1.3. Nonstandard TCP ports
        4. D.2.1.4. Resource identification
        5. D.2.1.5. Parameters and queries
        6. D.2.1.6. Fragment identifiers
        7. D.2.1.7. Absolute and relative URLs
        8. D.2.1.8. URL encoding
      2. D.2.2. HTTP Requests
        1. D.2.2.1. Request methods
        2. D.2.2.2. GET versus POST
      3. D.2.3. HTTP Responses
        1. D.2.3.1. Status codes
      4. D.2.4. Caching
  28. E. Modeling and Designing Relational Databases
    1. E.1. The Relational Model
      1. E.1.1. Case Study: Relationships in the Winestore
    2. E.2. Entity-Relationship Modeling
      1. E.2.1. Case Study: Modeling the Online Winestore
        1. E.2.1.1. Identifying entities in ER modeling
        2. E.2.1.2. Identifying relationships in ER modeling
        3. E.2.1.3. Relationships in the winestore ER model
        4. E.2.1.4. Identifying key attributes in ER modeling
        5. E.2.1.5. Other ER modeling tools
        6. E.2.1.6. Completing the ER model
      2. E.2.2. Converting an Entity-Relationship Model to SQL
        1. E.2.2.1. Step 1: Convert regular entities to tables
        2. E.2.2.2. Step 2: Convert weak entities to tables
        3. E.2.2.3. Step 3: One-to-one relationships
        4. E.2.2.4. Step 4: One-to-many relationships
        5. E.2.2.5. Step 5: Many-to-many relationships
  29. F. Managing Sessions in theDatabase Tier
    1. F.1. Using a Database to Keep State
    2. F.2. PHP Session Management
      1. F.2.1. PHP Session Management Storage Methods
      2. F.2.2. Building User-Defined Storage Handlers
    3. F.3. MySQL Session Store
      1. F.3.1. Session Table Structure
      2. F.3.2. Handler Implementations
        1. F.3.2.1. Support functions
        2. F.3.2.2. Session open handler
        3. F.3.2.3. Session read handler
        4. F.3.2.4. Session write handler
        5. F.3.2.5. Session close handler
        6. F.3.2.6. Session destroy handler
        7. F.3.2.7. Garbage collection handler
        8. F.3.2.8. Registering session handlers
      3. F.3.3. Using the User-Defined Session Handler Code
      4. F.3.4. PEAR's HTTP_Session Package
  30. G. Resources
    1. G.1. Client Tier Resources
    2. G.2. Middle-Tier Resources
      1. G.2.1. Web Server and Web Technology Resources
      2. G.2.2. Networking and Web Resources
      3. G.2.3. PHP resources
    3. G.3. Database Tier Resources
    4. G.4. Security and Cryptography Resources
  31. H. The Improved MySQL Library
    1. H.1. New Features
    2. H.2. Getting Started
      1. H.2.1. Installing the Library
      2. H.2.2. Migrating to the New Library
    3. H.3. Using the New Features
      1. H.3.1. Basic Features
      2. H.3.2. Preparing and Executing Queries
        1. H.3.2.1. Inserting data
        2. H.3.2.2. Retrieving data
      3. H.3.3. Profiling Queries
  32. Index
  33. About the Authors
  34. Colophon
  35. Copyright