Web Database Applications with PHP, and MySQL

Web Database Applications with PHP, and MySQL

by Hugh E. Williams, David Lane
Released March 2002
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596000417

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon
Start your free trial

Book description

What do eBay, Amazon.com and CNN.com have in common? They're all applications that integrate large databases with the Web. The popularity (and power) of these applications stems from their accessibility and usability: thousands of users can access the same data at the same time without theneed to install any additional software on their computers. Web Database Applications with PHP and MySQL offers web developers a mixture of theoretical and practical information on creating web database applications. Using PHP, and MySQL, two open source technologies that are often combined to develop web applications, the book offers detailed information on designing relational databases and on web application architecture, both of which will be useful to readers who have never dealt with these issues before. The book also introduces Hugh and Dave's Online Wines, a complete (but fictional) online retail site that allows users to browse, search a database, add items to a shopping cart, manage their membership, and purchase wines. Using this site as an example, the book shows you how to implement searching and browsing, store user data, validate user input, manage transactions, and maintain security. If you want to build small to medium-scale web database applications that can run on modest hardware and process more than a million hits a day from users, this book will show you how.

Table of contents

  1. Web Database Applications with PHP & MySQL
    1. 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. How to Contact Us
      7. Web Site and Code Examples
      8. Acknowledgments
    2. 1. Database Applications and the Web
      1. Three-Tier Architectures
        1. Hypertext Transfer Protocol
          1. HTTP example
          2. State
        2. Thin Clients
      2. The Client Tier
      3. The Middle Tier
        1. Web Servers
          1. The Apache HTTP server, Version 1.3
          2. The Apache HTTP server, Version 2.0
        2. Web Scripting with PHP
      4. The Database Tier
        1. Database Management Systems
        2. Why Use a DBMS?
          1. Examples of when to use a DBMS
          2. Examples of when not to use a DBMS
        3. The MySQL DBMS
        4. SQL
          1. History
          2. SQL components
      5. Our Case Study
        1. What Is Hugh and Dave’s Online Wines?
          1. System requirements
        2. Components of the Winestore
          1. Database-driven querying
          2. User-driven querying and browsing
          3. Data entry and saving records to a database
          4. Validation in the client and middle tiers
          5. User tracking and session management
          6. Authentication
          7. The complete application
    3. 2. PHP
      1. Introducing PHP
        1. PHP Basics
          1. Creating PHP scripts
          2. Comments
          3. Outputting data with echo and print
          4. String literals
        2. Variables
        3. Types
        4. Constants
        5. Expressions, Operators, and Variable Assignment
          1. Expressions
          2. Operator precedence
        6. Type Conversion
          1. Automatic type conversion
        7. Examining Variable Type and Content
          1. Debugging with print_r( ) and var_dump( )
          2. Testing, setting, and unsetting variables
      2. Conditions and Branches
        1. if...else Statement
        2. switch Statement
        3. Conditional Expressions
      3. Loops
        1. while
        2. do...while
        3. for
        4. foreach
        5. Changing Loop Behavior
      4. A Working Example
        1. Comments on Example 2.3
      5. Arrays
        1. Creating Arrays
          1. Associative arrays
          2. Heterogeneous arrays
          3. Multidimensional arrays
        2. Using foreach Loops with Arrays
        3. Using Array Pointers
        4. Basic Array Functions
          1. Counting elements in arrays
          2. Finding the maximum and minimum values in an array
          3. Finding values in arrays with in_array( ) and array_search( )
          4. Reordering elements in arrays with array_reverse( )
        5. Sorting Arrays
          1. Sorting with sort( ) and rsort( )
          2. Sorting associative arrays
          3. Sorting on keys
          4. Sorting with user-defined element comparison
      6. Strings
        1. String Literals
          1. Variable substitution
          2. Length of a string
        2. Printing and Formatting Strings
          1. Creating formatted output with sprintf( ) and printf( )
          2. Padding strings
          3. Changing case
          4. Trimming whitespace
          5. Rendering newline characters with <br>
        3. Comparing Strings
        4. Finding and Extracting Substrings
          1. Extracting a substring from a string
          2. Finding the position of a substring
          3. Extracting a found portion of a string
          4. Extracting multiple values from a string
        5. Replacing Characters and Substrings
          1. Replacing substrings
          2. Translating characters and substrings
      7. Regular Expressions
        1. Regular Expression Syntax
          1. Characters and wildcards
          2. Character lists
          3. Anchors
          4. Optional and repeating characters
          5. Groups
          6. Alternative patterns
          7. Escaping special characters
          8. Metacharacters
        2. Regular Expression Functions
          1. Finding and extracting values
          2. Replacing substrings
          3. Splitting a string into an array
      8. Date and Time Functions
        1. Generating a Timestamp
          1. Current time
          2. Creating timestamps with mktime( ) and gmmktime( )
          3. String to timestamp
          4. Subsecond times
        2. Formatting a Date
        3. Validating a Date
      9. Integer and Float Functions
        1. Absolute Value
        2. Ceiling and Floor
        3. Rounding
        4. Number Systems
        5. Basic Trigonometry Functions
        6. Powers and Logs
        7. Random Number Generation
      10. User-Defined Functions
        1. Argument Types and Return Types
        2. Variable Scope
          1. Global variables
        3. How Variables Are Passed to Functions
          1. Passing arguments by reference
          2. Assigning by reference
          3. Default argument values
        4. Reusing Functions with Include and Require Files
      11. Objects
        1. Classes and Objects
        2. Inheritance
      12. Common Mistakes
        1. A Page That Produces Partial or No Output
        2. Variable Problems
          1. Variable naming
          2. Missing output
        3. Complaints About Headers
        4. Other Common Problems
    4. 3. MySQL and SQL
      1. Database Basics
        1. Introducing Relational Databases
        2. Terminology
      2. Quick Start Guide
        1. Loading the Winestore Database
        2. The Winestore Database
          1. The winestore entity-relationship model
          2. Creating the winestore with SQL
      3. MySQL Command Interpreter
      4. Managing Databases, Tables, and Indexes
        1. Creating Databases
        2. Creating Tables
        3. Altering Tables and Indexes
        4. Displaying Database Structure with SHOW
      5. Inserting, Updating, and Deleting Data
        1. Inserting Data
          1. Bulk loading into a database
          2. Transferring data between databases and DBMSs
        2. Deleting Data
        3. Updating Data
      6. Querying with SQL SELECT
        1. Basic Querying
        2. WHERE Clauses
        3. Sorting and Grouping Output
          1. ORDER BY
          2. GROUP BY
          3. HAVING
          4. DISTINCT
        4. Limiting Result Sets in MySQL
      7. Join Queries
        1. Beware of the Cartesian Product
        2. Elementary Natural Joins
          1. Examples
          2. Table aliases in SQL queries
          3. Using DISTINCT in joins
        3. Joins with More than Two Tables
      8. Modifying the Database
        1. Adding a New Wine to the Winestore
        2. Buying a Bottle of Wine from the Winestore
        3. INSERTing with a SELECT Statement
      9. Functions
        1. Arithmetic and comparison operators
          1. String-comparison operators and functions
          2. Mathematical functions
          3. Date and time functions
          4. Miscellaneous operators and functions
      10. More on SQL and MySQL
        1. Keys, Primary Keys, and Indexes
        2. Tuning the Database System
        3. Adding and Deleting Users
          1. Permissions
        4. Limitations of MySQL
    5. 4. Querying Web Databases
      1. Connecting to a MySQL Database
        1. Opening and Using a Database Connection
        2. Essential Functions for Accessing MySQL with PHP
        3. More MySQL Functions in PHP
          1. Frequently used functions
          2. Other functions
          3. Functions to avoid
        4. Error Handling of MySQL Database Functions
      2. Formatting Results
        1. Using Include Files in Practice
      3. Case Study: The Front-Page Panel
        1. Step 1: Producing Visually Appealing Tables
          1. Limitations of Step 1
        2. Step 2: Adding Varieties to the Panel
          1. Adding a second or subsequent query
          2. Adding calculations to the result presentation
        3. Step 3: Finishing the Panel
          1. Fixing the queries
      4. Interacting with Other DBMSs Using PHP
        1. Microsoft SQL Server
        2. Open DataBase Connectivity (ODBC)
        3. Oracle 7 and 8 Through the OCI8 Interface
        4. PostgreSQL
    6. 5. User-Driven Querying
      1. User Input
        1. Passing Data with URLs
        2. Passing Data with the HTML <form> Environment
        3. Passing Data with Embedded Links
        4. Security and User Data
        5. How PHP Initializes Variables
      2. Querying with User Input
        1. Combined Scripts
        2. Adding Links to Results
        3. One-Component Querying
      3. Case Study: Previous and Next Browsing
        1. Step 1: Using the Generic browse Function
        2. Step 2: Implementing the Generic browse Function
        3. Step 3: Adding Page Numbers
        4. What’s Missing from the Previous and Next Browser
      4. Case Study: Producing a select List
        1. Implementing the selectDistinct Function
    7. 6. Writing to Web Databases
      1. Database Inserts, Updates, and Deletes
        1. Reloading Data and Relocation Techniques
          1. Solving the reload problem in practice
        2. Uploading and Inserting Files into Databases
        3. Inserting, Updating, and Deleting Data
          1. PHP DML functions for database modifications
          2. Inserting data
          3. Updating data
          4. Case study: Inserts and updates in practice
          5. Deleting data
      2. Issues in Writing Data to Databases
        1. Transactions and Concurrency
        2. Locking for Concurrency in MySQL
          1. When and how to lock tables
          2. The LOCK TABLES and UNLOCK TABLES statements in MySQL
          3. Locking for performance
        3. Locking Tables in Web Database Applications
          1. Locking methods that don’t work in web database applications
          2. Locking with an auxiliary table
          3. The table-level locking paradigm in MySQL
          4. Other locking paradigms
          5. What isn’t covered here
    8. 7. Validation on the Server and Client
      1. Validation and Error Reporting for Web Database Applications
        1. Models That Don’t Work
        2. Models That Do Work
      2. Server-Side Validation
        1. Case Study: Customer Validation in the Winestore
          1. Validating dates
          2. Validating numeric fields
          3. Validating email addresses
        2. Processing <form> Data on the Server Side
          1. Processing <form> controls with the MULTIPLE attribute
          2. Other <form> issues
      3. Client-Side Validation with JavaScript
        1. Validating <form> Data with JavaScript
          1. Case study: A generic JavaScript validation function
          2. Case study: A password <form> validation function
        2. JavaScript Tips and Tricks
          1. Rollover presentation with mouseOver events
          2. Prefilling <form> data with JavaScript calculations
          3. Interacting with the web browser
          4. Which browser is the user using?
          5. Comments
    9. 8. Sessions
      1. Building Applications That Keep State
        1. Managing State in the Client Tier
        2. Cookies
      2. Session Management Over the Web
      3. PHP Session Management
        1. Overview
        2. Starting a Session
        3. Using Session Variables
        4. Ending a Session
        5. Functions for Accessing Sessions in PHP
        6. Session Management Without Cookies
          1. Encoding the session ID as a GET variable
          2. Turning off cookies
        7. Garbage Collection
        8. Configuration of PHP Session Management
      4. Case Study: Adding Sessions to the Winestore
        1. Improving the Client Entry <form>
        2. The Validation Script
          1. Improving error messages
          2. Saving last-entered values as a session variable
          3. The final validation script
        3. The Client Entry <form> Script
          1. Displaying previously entered values
          2. Displaying error messages
          3. The final client entry script
      5. When to Use Sessions
        1. Reasons to Use Sessions
          1. Performance
          2. Sequence of interaction
          3. Intermediate results
          4. Personalization
        2. Reasons to Avoid Sessions
          1. Need for centralized session store
          2. Performance
          3. Timeouts
          4. Bookmark restrictions
          5. Security
    10. 9. Authentication and Security
      1. HTTP Authentication
        1. How HTTP Authentication Works
        2. Using Apache to Authenticate
      2. HTTP Authentication with PHP
        1. Access to User Credentials from PHP
        2. Managing HTTP Authentication with PHP
        3. Authorizing User Access
          1. Limits placed on IP addresses
      3. Authentication Using a Database
        1. MySQL encryption
          1. Encrypting other data in a database
      4. Web Database Applications and Authentication
        1. Building Stateless Applications
        2. Building Session-Based Applications
          1. Forcing users to a login page
          2. Authenticating without HTTP
          3. Session hijacking
          4. Recording IP addresses to detect session hijack attempts
        3. Case Study: Customer Authentication
          1. Case study overview
          2. Login page
          3. Authentication script
          4. Logout script
          5. Authorizing other requests
      5. Protecting Data on the Web
        1. The Secure Sockets Layer Protocol
          1. SSL architecture
          2. Cipher suites
          3. SSL sessions
          4. Certificates and Certification Authorities
    11. 10. Winestore Customer Management
      1. Overview of the Winestore Application
        1. Winestore Scripts
      2. Customer Management
        1. Customer Validation
        2. The Customer <form>
        3. The Customer Receipt Page
      3. Authenticating Users
      4. The Winestore Include Files
        1. Custom Error Handlers
    12. 11. The Winestore Shopping Cart
      1. The Winestore Home Page
      2. The Shopping Cart Architecture
        1. Viewing the Shopping Cart
        2. Adding Items to the Shopping Cart
        3. Emptying the Shopping Cart
        4. Updating the Shopping Cart Quantities
      3. Managing Redirection
    13. 12. Ordering and Shipping at the Winestore
      1. Finalizing Orders
      2. HTML and Email Receipts
    14. 13. Related Topics
      1. Automated Housekeeping
        1. cron Jobs
      2. Templates
        1. Templates in the Shipping Module
          1. The application logic
          2. The template
      3. Searching and Browsing
    15. A. Installation Guide
      1. Installing MySQL, Apache, and PHP
        1. Installing MySQL
        2. Configuring MySQL
        3. Installing Apache
        4. Installing PHP
      2. Installing the Winestore Examples
      3. Installing Apache to Use SSL
        1. Installing OpenSSL
        2. Installing Apache and ApacheSSL
        3. Creating a Key and Certificate
      4. Installation Resources
    16. B. Internet and Web Protocols
      1. The Internet
        1. An Analogy
        2. TCP/IP
          1. IP addresses
          2. Ports
      2. Hypertext Transfer Protocol
        1. Request and Response Model
          1. Simulating an HTTP request
        2. Uniform Resource Locators
          1. Protocol
          2. Host and service identification
          3. Nonstandard TCP ports
          4. Resource identification
          5. Parameters and queries
          6. Fragment identifiers
          7. Absolute and relative URLs
          8. URL encoding
        3. HTTP Requests
          1. Request methods
          2. GET versus POST
        4. HTTP Responses
          1. Status codes
        5. Caching
          1. HTTP/1.0 cache control
          2. HTTP/1.1 cache control
        6. HTTP and TCP/IP
          1. Simultaneous request model
          2. Persistent connections
        7. MIME
    17. C. Modeling and Designing Relational Databases
      1. The Relational Model
        1. Case Study: Relations in the Winestore
      2. Entity-Relationship Modeling
        1. Case Study: Modeling the Online Winestore
          1. System requirements analysis
          2. Identifying entities in ER modeling
          3. Identifying relationships in ER modeling
          4. Relationships in the winestore ER model
          5. Identifying key attributes in ER modeling
          6. Other ER modeling tools
          7. Completing the ER model
        2. Converting an Entity-Relationship Model to SQL
          1. Step 1: Convert regular entities to tables
          2. Step 2: Convert weak entities to tables
          3. Step 3: One-to-one relationships
          4. Step 4: Regular one-to-many relationships
          5. Step 5: Many-to-many relationships
    18. D. Managing Sessions in the Database Tier
      1. Using a Database to Keep State
      2. PHP Session Management
        1. PHP Session Management Storage Methods
        2. Building User-Defined Session Handlers
      3. MySQL Session Store
        1. Session Table Structure
        2. Handler Implementations
          1. Support functions
          2. sessionOpen
          3. sessionRead
          4. sessionWrite
          5. sessionClose
          6. sessionDestroy
          7. Garbage collection
          8. Registering session handlers
        3. Using the User-Defined Session Handler Code
    19. E. Resources
      1. Client Tier Resources
      2. Middle Tier Resources
        1. Web Server and Web Technology Resources
        2. Networking and Web Resources
        3. More About PHP
          1. Books
          2. Web resources
          3. Libraries
          4. Third-party products
          5. Open source PHP applications
      3. Database Tier Resources
      4. Security and Cryptography Resources
    20. Index
    21. Colophon

Product information

  • Title: Web Database Applications with PHP, and MySQL
  • Author(s): Hugh E. Williams, David Lane
  • Release date: March 2002
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596000417