Expert PHP and MySQL®

Expert PHP and MySQL®

by Andrew Curioso, Ronald Bradford, Patrick Galbraith
Released March 2010
Publisher(s): Wrox
ISBN: 9780470563120

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.

Start your free trial

Book description

Best practices and expert techniques for even the most demanding MySQL-driven PHP applications

PHP is the world's most popular, general-purpose, open-source scripting language and MySQL is the world's most popular open-source database. This expert-level book begins with a brief overview of the two technologies and quickly moves on to coverage of advanced programming techniques, as well as the exciting new features of the PHP 5.3 release.

Written by three of PHP and MySQL experts and veteran authors, this resource focuses on best practices and expert techniques that can be applied to the most difficult MySQL-driven PHP applications. The authors share design patterns and tools that they themselves have tested in order to save you valuable time usually spent on figuring things out via trial and error.

  • Reveals best practices and advanced techniques for handling challenges you may face with MySQL-driven PHP apps

  • Reviews practical data reporting techniques for separating business logic from presentation

  • Explains how to obtain improved performance and flexibility through caching, multi-tasking, PHP extensions, and MySQL user defined functions

  • Discusses security, optimization, and debugging

  • Shares author-tested design patterns and tools to save you time and effort

Packed with helpful examples and indispensable advice, this book shares tips and tricks that you can immediately apply to your projects.

Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Table of contents

  1. Copyright
  2. ABOUT THE AUTHORS
  3. ABOUT THE TECHNICAL EDITORS
  4. CREDITS
  5. ACKNOWLEDGMENTS
  6. INTRODUCTION
    1. 0.1. WHO THIS BOOK IS FOR
    2. 0.2. WHAT THIS BOOK COVERS
    3. 0.3. WHAT YOU NEED TO USE THIS BOOK
      1. 0.3.1.
        1. 0.3.1.1. Using Linux
        2. 0.3.1.2. Using Windows
    4. 0.4. CONVENTIONS
    5. 0.5. SOURCE CODE
    6. 0.6. ERRATA
    7. 0.7. P2P.WROX.COM
  7. 1. Techniques Every Expert Programmer Needs to Know
    1. 1.1. OBJECT-ORIENTED PHP
      1. 1.1.1. Instantiation and Polymorphism
        1. 1.1.1.1. Polymorphism in Action
        2. 1.1.1.2. Handling Terminal Types and Type Hinting
      2. 1.1.2. Interfaces
      3. 1.1.3. Magic Methods and Constants
        1. 1.1.3.1. Practical Use of Magic Constants
        2. 1.1.3.2. Adding Magic Functionality to Classes
      4. 1.1.4. Design Patterns
        1. 1.1.4.1. Singleton and Multiton Patterns
        2. 1.1.4.2. Proxy and Façade Patterns
        3. 1.1.4.3. Decorator Pattern
        4. 1.1.4.4. Factory Method
        5. 1.1.4.5. Observer and Publisher/Subscriber Patterns
    2. 1.2. USING MYSQL JOINS
      1. 1.2.1. INNER JOIN
        1. 1.2.1.1. The Table Alias
        2. 1.2.1.2. ON and USING
        3. 1.2.1.3. An Alternative INNER JOIN Syntax
      2. 1.2.2. OUTER JOIN
        1. 1.2.2.1. RIGHT JOIN
        2. 1.2.2.2. LEFT JOIN
      3. 1.2.3. Other JOIN Syntax
        1. 1.2.3.1. UPDATE and DELETE JOIN Syntax
        2. 1.2.3.2. Case Sensitivity
      4. 1.2.4. Complex Joins
    3. 1.3. MYSQL UNIONS
    4. 1.4. GROUP BY IN MYSQL QUERIES
      1. 1.4.1. WITH ROLLUP
    5. 1.5. HAVING
    6. 1.6. LOGICAL OPERATIONS AND FLOW CONTROL IN MYSQL
      1. 1.6.1. Logic Operators
      2. 1.6.2. Flow Control
    7. 1.7. MAINTAINING RELATIONAL INTEGRITY
      1. 1.7.1. Constraints
    8. 1.8. NOT NULL
      1. 1.8.1. UNSIGNED
      2. 1.8.2. ENUM and SET
      3. 1.8.3. UNIQUE KEY
      4. 1.8.4. FOREIGN KEY
      5. 1.8.5. Using Server SQL Modes
        1. 1.8.5.1. sql_mode=TRADITIONAL
        2. 1.8.5.2. sql_mode=NO_ENGINE_SUBSTITUTION
      6. 1.8.6. Storage Engine Integrity
      7. 1.8.7. What MySQL Does Not Tell You
      8. 1.8.8. What's Missing?
    9. 1.9. SUBQUERIES IN MYSQL
      1. 1.9.1. Subquery
      2. 1.9.2. Correlated Subquery
      3. 1.9.3. Derived Table
      4. 1.9.4. Complex Sub Queries
    10. 1.10. USING REGULAR EXPRESSIONS
      1. 1.10.1. General Patterns
        1. 1.10.1.1. Matching a Range of Characters
      2. 1.10.2. Expert Regular Expressions
        1. 1.10.2.1. Lookaheads and Lookbehinds
        2. 1.10.2.2. Capturing Data
        3. 1.10.2.3. Documenting Regular Expressions
      3. 1.10.3. Putting It All Together in PHP
        1. 1.10.3.1. Replacing Strings
      4. 1.10.4. Regular Expressions in MySQL
        1. 1.10.4.1. Using LIB_MYSQLUDF_PREG
        2. 1.10.4.2. Capturing Data
        3. 1.10.4.3. String Replacement
        4. 1.10.4.4. Filtering a Query Based on a Regular Expression
    11. 1.11. SUMMARY
  8. 2. Advanced PHP Concepts
    1. 2.1. A PROBLEM THAT NEEDS SOLVING
    2. 2.2. ITERATORS AND THE SPL
      1. 2.2.1. A Sample View for the Application
      2. 2.2.2. The Iterator Interface
        1. 2.2.2.1. Rewinding an Iterator
        2. 2.2.2.2. Validating and Returning the Current Record
      3. 2.2.3. The Countable Interface
      4. 2.2.4. The SeekableIterator Interface and Pagination
      5. 2.2.5. The ArrayAccess Interface
    3. 2.3. LAMBDA FUNCTIONS AND CLOSURES
      1. 2.3.1. The Old Way: Lambda-Style Functions
      2. 2.3.2. Understanding Closures
      3. 2.3.3. Using the Query Builder for Prototyping
    4. 2.4. SUMMARY
  9. 3. MySQL Drivers and Storage Engines
    1. 3.1. MYSQL DRIVERS
    2. 3.2. ABOUT MYSQL STORAGE ENGINES
      1. 3.2.1. Obtaining Storage Engine Information
        1. 3.2.1.1. Available Engines
        2. 3.2.1.2. Defining the Storage Engine
        3. 3.2.1.3. Confirming a Table Storage Engine
    3. 3.3. DEFAULT STORAGE ENGINES
      1. 3.3.1. MyISAM
        1. 3.3.1.1. Key Features
        2. 3.3.1.2. Limitations
        3. 3.3.1.3. Important Parameters
        4. 3.3.1.4. Examples
        5. 3.3.1.5. When to Use MyISAM
      2. 3.3.2. InnoDB
        1. 3.3.2.1. Key Features
        2. 3.3.2.2. Limitations
        3. 3.3.2.3. Important Parameters
        4. 3.3.2.4. Understanding InnoDB Table Usage
        5. 3.3.2.5. Differences from MyISAM
        6. 3.3.2.6. Optimizing SQL Using InnoDB
      3. 3.3.3. Memory
        1. 3.3.3.1. Key Features
        2. 3.3.3.2. Limitations
        3. 3.3.3.3. Important Parameters
        4. 3.3.3.4. Example Table Usage
      4. 3.3.4. Blackhole
        1. 3.3.4.1. Key Features
        2. 3.3.4.2. Limitations
        3. 3.3.4.3. Important Parameters
        4. 3.3.4.4. Uses for Blackhole
      5. 3.3.5. Archive
        1. 3.3.5.1. Key Features
        2. 3.3.5.2. Limitations
        3. 3.3.5.3. Important Parameters
        4. 3.3.5.4. Understanding Archive Tables Usages
      6. 3.3.6. Merge
      7. 3.3.7. CSV
        1. 3.3.7.1. Key Features
        2. 3.3.7.2. Limitations
        3. 3.3.7.3. Important Parameters
        4. 3.3.7.4. Understanding CSV Table Usage
      8. 3.3.8. Federated
        1. 3.3.8.1. Characteristics of the Federated Storage Engine
        2. 3.3.8.2. Creating a Federated Table
        3. 3.3.8.3. Federated Servers
        4. 3.3.8.4. Federated under the Hood
    4. 3.4. OTHER MYSQL SUPPLIED ENGINES
      1. 3.4.1. Falcon
        1. 3.4.1.1. Key Features
        2. 3.4.1.2. Limitations
        3. 3.4.1.3. Important Parameters
        4. 3.4.1.4. Falcon Table Usage
      2. 3.4.2. Maria
        1. 3.4.2.1. Key Features
    5. 3.5. PLUGGABLE ENGINES
      1. 3.5.1. InnoDB Plugin
        1. 3.5.1.1. Key Features (in Addition to Those Listed in InnoDB) Include:
        2. 3.5.1.2. Important Parameters
      2. 3.5.2. PBXT
        1. 3.5.2.1. Key features
        2. 3.5.2.2. Limitations
        3. 3.5.2.3. Important Parameters
      3. 3.5.3. XtraDB
    6. 3.6. ENGINES AS STANDALONE PRODUCTS
      1. 3.6.1. InfiniDB
      2. 3.6.2. TokuDB
      3. 3.6.3. Infobright
    7. 3.7. OTHER MYSQL OFFERINGS
      1. 3.7.1. Storage Engine Patch Products
        1. 3.7.1.1. Percona Performance Builds
        2. 3.7.1.2. Our Delta
      2. 3.7.2. MySQL-Related Products
        1. 3.7.2.1. Drizzle
        2. 3.7.2.2. MariaDB
      3. 3.7.3. Other Engines
        1. 3.7.3.1. NDB
        2. 3.7.3.2. eBay Memory Engine
        3. 3.7.3.3. NitroEDB
        4. 3.7.3.4. Solid
      4. 3.7.4. Integrated Hardware Engines
        1. 3.7.4.1. Kickfire
        2. 3.7.4.2. Virident
      5. 3.7.5. Other Solutions
      6. 3.7.6. Wafflegrid
    8. 3.8. SUMMARY
  10. 4. Improving Performance through Caching
    1. 4.1. EACCELERATOR AND APC
      1. 4.1.1. Installing and Configuring APC
      2. 4.1.2. Installing and Configuring eAccelerator
      3. 4.1.3. User Cache
      4. 4.1.4. Checking the Cache Status
      5. 4.1.5. When to Use APC and eAccelerator
    2. 4.2. MEMCACHED
      1. 4.2.1. What Is memcached?
      2. 4.2.2. What memcached Does for You
      3. 4.2.3. How Does memcached Work?
      4. 4.2.4. How to Use memcached
        1. 4.2.4.1. Types of Caching
      5. 4.2.5. What Is Gearman?
      6. 4.2.6. Caching Strategies
    3. 4.3. INSTALLING MEMCACHED
      1. 4.3.1. CentOS
        1. 4.3.1.1. Ubuntu
        2. 4.3.1.2. OpenSolaris
        3. 4.3.1.3. Installing Memcached from Source
    4. 4.4. STARTING MEMCACHED
      1. 4.4.1. Startup Scripts
        1. 4.4.1.1. Debian-Based Startup Scripts
        2. 4.4.1.2. Redhat-Based Startup Scripts
        3. 4.4.1.3. OpenSolaris
      2. 4.4.2. Testing Your memcached Installation
      3. 4.4.3. memcached Clients
    5. 4.5. LIBMEMCACHED
      1. 4.5.1. Libmemcached Features
      2. 4.5.2. Libmemcache Utility Programs
      3. 4.5.3. Installing libmemcached
    6. 4.6. LIBMEMCACHED UTILITY PROGRAMS
      1. 4.6.1. memcat
      2. 4.6.2. memflush
      3. 4.6.3. memcp
      4. 4.6.4. memstat
      5. 4.6.5. memrm
      6. 4.6.6. memslap
      7. 4.6.7. memerror
    7. 4.7. PECL/MEMCACHED
      1. 4.7.1. Connecting, Instantiation
      2. 4.7.2. Setting Client Behavior
      3. 4.7.3. Putting and Retrieving Data
      4. 4.7.4. Append and Prepend
      5. 4.7.5. Delete
      6. 4.7.6. Increment and Decrement
      7. 4.7.7. Multi-get
      8. 4.7.8. Multi-set
      9. 4.7.9. Cache Locality Using byKey Methods and Multi get/set
      10. 4.7.10. getDelayed
      11. 4.7.11. CAS
      12. 4.7.12. Statistics
      13. 4.7.13. Server List
      14. 4.7.14. Error Handling
    8. 4.8. PRACTICAL CACHING
      1. 4.8.1. memcached Proxy: moxi
      2. 4.8.2. Other "memcapable" Key-Value Stores
      3. 4.8.3. Tokyo Tyrant
    9. 4.9. SUMMARY
  11. 5. memcached and MySQL
    1. 5.1. THE MEMCACHED FUNCTIONS FOR MYSQL
    2. 5.2. HOW THE MEMCACHED FUNCTIONS FOR MYSQL WORK
    3. 5.3. INSTALLING THE MEMCACHED FUNCTIONS FOR MYSQL
      1. 5.3.1. Prerequisites
      2. 5.3.2. Configure the Source
      3. 5.3.3. Build the Source
      4. 5.3.4. Install the UDF
        1. 5.3.4.1. Using SQL Script Install
        2. 5.3.4.2. Using the Perl Install Utility
      5. 5.3.5. Checking Installation
    4. 5.4. USING THE MEMCACHED FUNCTIONS FOR MYSQL
      1. 5.4.1. Establishing a Connection to the memcached Server
        1. 5.4.1.1. memc_servers_set
        2. 5.4.1.2. memc_server_count
      2. 5.4.2. Data Setting Functions
        1. 5.4.2.1. memc_set
        2. 5.4.2.2. memc_set_by_key
        3. 5.4.2.3. memc_add
        4. 5.4.2.4. memc_replace
        5. 5.4.2.5. memc_cas
        6. 5.4.2.6. memc_prepend
        7. 5.4.2.7. memc_append
        8. 5.4.2.8. memc_delete
      3. 5.4.3. Data Fetching Functions
        1. 5.4.3.1. memc_get
      4. 5.4.4. Increment and Decrement
        1. 5.4.4.1. memc_increment
        2. 5.4.4.2. memc_decrement
      5. 5.4.5. Behavioral Functions
        1. 5.4.5.1. memc_list_behaviors
        2. 5.4.5.2. memc_behavior_get
        3. 5.4.5.3. memc_behavior_set
        4. 5.4.5.4. memc_list_hash_types
        5. 5.4.5.5. memc_list_distribution_types
      6. 5.4.6. Statistical Functions
        1. 5.4.6.1. memc_stats
        2. 5.4.6.2. memc_stat_get_value
        3. 5.4.6.3. memc_stat_get_keys
      7. 5.4.7. Version Functions
        1. 5.4.7.1. memc_libmemcached_version
        2. 5.4.7.2. memc_udf_version
      8. 5.4.8. Fun with Triggers (and UDFs)
        1. 5.4.8.1. The Source Distribution Directory
        2. 5.4.8.2. Trigger Execution
      9. 5.4.9. Read-Through Caching with Simple Select Statements
      10. 5.4.10. Updates
    5. 5.5. SUMMARY
  12. 6. Advanced MySQL
    1. 6.1. VIEWS
      1. 6.1.1. Access Permissions
      2. 6.1.2. Additional Information about Views
    2. 6.2. STORED PROCEDURES AND FUNCTIONS
      1. 6.2.1. General Attributes
        1. 6.2.1.1. Parameters
        2. 6.2.1.2. Characteristics
      2. 6.2.2. Stored Routine Logic
        1. 6.2.2.1. Variables
        2. 6.2.2.2. Cursors
        3. 6.2.2.3. Handlers and Conditions
        4. 6.2.2.4. Flow Control
      3. 6.2.3. Using Stored Routines Privileges and Meta Data
      4. 6.2.4. Extending Stored Routines
      5. 6.2.5. Stored Routine Disadvantages
      6. 6.2.6. User Defined Functions
    3. 6.3. TRIGGERS
      1. 6.3.1. No Triggers
      2. 6.3.2. Trigger Syntax
      3. 6.3.3. Insert Triggers
      4. 6.3.4. Update Triggers
      5. 6.3.5. Delete Triggers
      6. 6.3.6. Replace Triggers
      7. 6.3.7. Trigger Permissions
    4. 6.4. TRANSACTIONS
      1. 6.4.1. Atomicity
        1. 6.4.1.1. Non-transactional Tables
        2. 6.4.1.2. Transactional Tables
      2. 6.4.2. Consistency
      3. 6.4.3. Isolation
        1. 6.4.3.1. Repeatable Read
        2. 6.4.3.2. Read Committed
        3. 6.4.3.3. Read Uncommitted
        4. 6.4.3.4. Serializable
        5. 6.4.3.5. Isolation Levels and Replication
      4. 6.4.4. Durability
      5. 6.4.5. Implied Commit
    5. 6.5. REPLICATION
      1. 6.5.1. Replication Purposes
      2. 6.5.2. Replication Setup
        1. 6.5.2.1. Master Configuration
        2. 6.5.2.2. Slave Configuration
        3. 6.5.2.3. Slave Operation
      3. 6.5.3. Testing MySQL Replication
      4. 6.5.4. How Does MySQL Replication Work?
        1. 6.5.4.1. Master Analysis
        2. 6.5.4.2. Binary Log File Analysis
        3. 6.5.4.3. Slave Analysis
      5. 6.5.5. Testing MySQL Replication
      6. 6.5.6. Important Configuration Options
      7. 6.5.7. Important Replication Commands
      8. 6.5.8. Breaking Replication
      9. 6.5.9. Using Replication Selectively
      10. 6.5.10. The Issues with MySQL Replication
      11. 6.5.11. The Benefits of MySQL Replication
    6. 6.6. EVENTS
      1. 6.6.1. Creating Events
      2. 6.6.2. Enabling the Events Scheduler
      3. 6.6.3. Altering Events
      4. 6.6.4. Event Privileges
      5. 6.6.5. Event Meta Data
    7. 6.7. SUMMARY
  13. 7. Extending MySQL with User-Defined Functions
    1. 7.1. INTRODUCTION TO UDFS
    2. 7.2. DEVELOPING A UDF
      1. 7.2.1. UDF Development Requirements
      2. 7.2.2. UDF Required Functions
    3. 7.3. A PRACTICAL UDF EXAMPLE
      1. 7.3.1. UDF High-Level Design
      2. 7.3.2. Designing an Algorithm to Use for Your UDF
      3. 7.3.3. Implementing the Program
        1. 7.3.3.1. Coding the UDF
        2. 7.3.3.2. Project Directory Organization — Creating an Open Source Project
        3. 7.3.3.3. Source Code Implementation
        4. 7.3.3.4. Checking That the UDF Is Installed
      4. 7.3.4. Building the UDF
      5. 7.3.5. Installing the UDF
      6. 7.3.6. Running Your New UDF
    4. 7.4. USING A UDF WITH PHP
      1. 7.4.1. Connecting and Disconnecting to MySQL
      2. 7.4.2. Returning the Result Set Array
    5. 7.5. OTHER UDF SQL STATEMENTS
    6. 7.6. DEBUGGING A UDF
      1. 7.6.1. Attaching gdb to an Already Running Process
      2. 7.6.2. Setting a BreakPoint and Stepping through Code
      3. 7.6.3. Dealing with Literal Values
      4. 7.6.4. Debugging Summary
    7. 7.7. SUMMARY
  14. 8. Writing PHP Extensions
    1. 8.1. SETTING UP THE BUILD ENVIRONMENT
    2. 8.2. CREATING AN EXTENSION WITH EXT_SKEL
      1. 8.2.1. Creating and Compiling Skeleton Code
      2. 8.2.2. Using a Function Definitions File
      3. 8.2.3. Generating Help Files
    3. 8.3. CREATING AN EXTENSION WITH CODEGEN_PECL
      1. 8.3.1. Creating the Basic XML File
      2. 8.3.2. Defining Functions
        1. 8.3.2.1. Defining a Public Function
        2. 8.3.2.2. Defining Internal Functions
      3. 8.3.3. Defining Constants, INI Directives, and Globals
        1. 8.3.3.1. Defining Constants
        2. 8.3.3.2. Defining INI Directives and Globals
      4. 8.3.4. Defining Objects, Methods, and Properties
        1. 8.3.4.1. Defining Methods
        2. 8.3.4.2. Defining Properties
        3. 8.3.4.3. An Example Class
        4. 8.3.4.4. Interfaces
    4. 8.4. VARIABLES IN PHP EXTENSIONS
      1. 8.4.1. Setting and Testing zvals
      2. 8.4.2. Reading and Comparing zvals
      3. 8.4.3. Dealing with Strings as zvals
        1. 8.4.3.1. Manipulating and Comparing Strings
        2. 8.4.3.2. Converting to Strings
      4. 8.4.4. Advanced Memory Management
    5. 8.5. USING FUNCTIONS IN EXTENSIONS
      1. 8.5.1. Basic Definitions
      2. 8.5.2. Using Arguments
      3. 8.5.3. Defining Argument Information
      4. 8.5.4. Returning Values
      5. 8.5.5. Built-In Functions
      6. 8.5.6. Creating and Consuming PHP API Functions
        1. 8.5.6.1. Consuming PHP APIs
        2. 8.5.6.2. Providing an API
      7. 8.5.7. More Notes and Creating Helper Functions
    6. 8.6. INPUT/OUTPUT
      1. 8.6.1. Standard Out
      2. 8.6.2. Files and Streams
        1. 8.6.2.1. Basic Streams
        2. 8.6.2.2. Using Context
      3. 8.6.3. Networking
        1. 8.6.3.1. Building a Client
        2. 8.6.3.2. Building a Server
      4. 8.6.4. Errors and Warnings
    7. 8.7. ARRAYS AND HASH TABLES
      1. 8.7.1. Building and Accessing Arrays
      2. 8.7.2. Accessing and Modifying Hash Tables
    8. 8.8. OBJECTS AND INTERFACES
      1. 8.8.1. Creating a Class
        1. 8.8.1.1. Class Definition, Inheritance, and Namespaces
        2. 8.8.1.2. Using Methods
        3. 8.8.1.3. Using Properties
        4. 8.8.1.4. Using Class Constants
        5. 8.8.1.5. Creating an Interface
      2. 8.8.2. Interacting with Objects
        1. 8.8.2.1. Identifying Objects
        2. 8.8.2.2. Calling Methods
    9. 8.9. CONSTANTS, INI DIRECTIVES, AND GLOBALS
      1. 8.9.1. Constants
      2. 8.9.2. Globals and INI Directives
        1. 8.9.2.1. Defining Globals and INI Directives
        2. 8.9.2.2. Accessing Globals
        3. 8.9.2.3. True Globals and Thread Safety
    10. 8.10. DESCRIBING AN EXTENSION
      1. 8.10.1. Printing a Description
      2. 8.10.2. Building a Table
      3. 8.10.3. Specifying a Logo
    11. 8.11. SUMMARY
  15. 9. Full-Text Searching
    1. 9.1. MYSQL FULLTEXT INDEXES
      1. 9.1.1. Using MySQL FULLTEXT Indexes
        1. 9.1.1.1. Natural Language Mode
        2. 9.1.1.2. Boolean Mode
      2. 9.1.2. MySQL FULLTEXT Index Issues
    2. 9.2. A BETTER SOLUTION: THE SPHINX FULL-TEXT SEARCH ENGINE
      1. 9.2.1. Sphinx Configuration and Installation
      2. 9.2.2. Sphinx.conf Settings
        1. 9.2.2.1. Sphinx Data Sources
        2. 9.2.2.2. Sphinx Indexes
        3. 9.2.2.3. Indexer Options
        4. 9.2.2.4. searchd Options
        5. 9.2.2.5. Data Sources
        6. 9.2.2.6. Defining the sakila_main Data Source
        7. 9.2.2.7. Defining the Indexes
        8. 9.2.2.8. Defining the film_main and Its Inherited Indexes
        9. 9.2.2.9. Specifying the Indexer Options
        10. 9.2.2.10. Specifying the searchd Options
      3. 9.2.3. Starting Sphinx
        1. 9.2.3.1. Running the Indexer
        2. 9.2.3.2. Starting the Search Daemon
      4. 9.2.4. Searching Sphinx
        1. 9.2.4.1. Search Modes
        2. 9.2.4.2. Sort Modes
        3. 9.2.4.3. Boolean Query Syntax
        4. 9.2.4.4. Extended Query Syntax
        5. 9.2.4.5. The Search Utility
        6. 9.2.4.6. SphinxQL
        7. 9.2.4.7. One Last Step to Using Sphinx
      5. 9.2.5. Delta Indexes
      6. 9.2.6. Merging Indexes
    3. 9.3. DEVELOPING APPLICATIONS THAT USE SPHINX
      1. 9.3.1. Sphinx and PHP
    4. 9.4. SUMMARY
  16. 10. Multi-tasking in PHP and MySQL
    1. 10.1. GEARMAN
    2. 10.2. WHAT IS GEARMAN?
      1. 10.2.1. Installing and Running Gearman
        1. 10.2.1.1. gearmand Job Server Install
        2. 10.2.1.2. PECL/Gearman
        3. 10.2.1.3. Gearman MySQL UDF Install
        4. 10.2.1.4. Running the Gearman Job Server
      2. 10.2.2. Using the Gearman MySQL UDFs
        1. 10.2.2.1. gman_severs_set()
        2. 10.2.2.2. gman_do()
        3. 10.2.2.3. gman_sum()
        4. 10.2.2.4. Usage Examples
      3. 10.2.3. PHP and Gearman
    3. 10.3. NARADA: A SEARCH ENGINE APPLICATION
      1. 10.3.1. Obtaining Narada
      2. 10.3.2. Narada Components
      3. 10.3.3. Database Tables for the Search Engine Application
      4. 10.3.4. Sphinx Setup
        1. 10.3.4.1. The Narada Configuration File
        2. 10.3.4.2. The Narada Class
      5. 10.3.5. Gearman Workers
        1. 10.3.5.1. Insert Worker
        2. 10.3.5.2. Fetch Worker
        3. 10.3.5.3. Search Worker
        4. 10.3.5.4. Index Worker
      6. 10.3.6. Index Page
      7. 10.3.7. One Other Tidbit of Code
      8. 10.3.8. The Big Picture
      9. 10.3.9. Running Narada
      10. 10.3.10. To-Do List for Narada
      11. 10.3.11. Other Job Server Systems
    4. 10.4. SUMMARY
  17. 11. Rewrite Rules
    1. 11.1. USING REWRITE RULES
      1. 11.1.1. Understanding the Purpose and Structure of Rewrite Rules
      2. 11.1.2. Understanding and Controlling Rewrite Rule Flow
      3. 11.1.3. Conditional Rules
      4. 11.1.4. Logging and Optimization
    2. 11.2. REWRITE MAPS
      1. 11.2.1. Built-in Maps
      2. 11.2.2. Random and Text Lookups
      3. 11.2.3. Using PHP and MySQL
    3. 11.3. SUMMARY
  18. 12. User Authentication
    1. 12.1. DESIGNING THE DATABASE
    2. 12.2. HTTP-BASED AUTHENTICATION
      1. 12.2.1. Basic Authentication
      2. 12.2.2. Digest Authentication
    3. 12.3. PURE PHP AUTHENTICATION
      1. 12.3.1. Using PHP Sessions
      2. 12.3.2. Building Secure Cookies
    4. 12.4. ACCESS CONTROL LISTS
    5. 12.5. SUMMARY
  19. 13. Understanding the INFORMATION_SCHEMA
    1. 13.1. USING THE INFORMATION_SCHEMA
    2. 13.2. TABLE OBJECTS TABLES
    3. 13.3. OTHER DATABASE OBJECTS TABLES
    4. 13.4. MYSQL STATUS TABLES
    5. 13.5. PROCESSLIST (5.1)
    6. 13.6. SESSION_STATUS/GLOBAL_STATUS (5.1)
    7. 13.7. SESSION_VARIABLES/GLOBAL_VARIABLES (5.1)
    8. 13.8. MYSQL META DATA TABLES
    9. 13.9. CHARACTER_SETS
    10. 13.10. COLLATIONS
    11. 13.11. COLLATION_CHARACTER_SET_APPLICABILITY
    12. 13.12. MYSQL ACL PERMISSIONS TABLES
    13. 13.13. USER_PRIVILEGES
    14. 13.14. SCHEMA_PRIVILEGES
    15. 13.15. TABLE_PRIVILEGES
    16. 13.16. COLUMN_PRIVILEGES
    17. 13.17. INFORMATION_SCHEMA EXTENSIONS
    18. 13.18. SHOW CROSS REFERENCE
    19. 13.19. SUMMARY
  20. 14. Security
    1. 14.1. HARDENING YOUR MYSQL SERVER
      1. 14.1.1. Installation Defaults
      2. 14.1.2. Operating System Security
      3. 14.1.3. MySQL Security Permissions
        1. 14.1.3.1. About the MySQL Security Model
        2. 14.1.3.2. GRANT
        3. 14.1.3.3. REVOKE
        4. 14.1.3.4. DROP USER
        5. 14.1.3.5. The Security Backdoor
        6. 14.1.3.6. Change Default 'root' User
        7. 14.1.3.7. Using Privileges Appropriately
        8. 14.1.3.8. Defining User Host Permissions
        9. 14.1.3.9. Ideal Application Security
      4. 14.1.4. Additional Database Security
      5. 14.1.5. Auditing
    2. 14.2. ENCODING DATA
      1. 14.2.1. Bi-directional Encoding
        1. 14.2.1.1. The XOR Cipher
        2. 14.2.1.2. Using the mcrypt Extension
      2. 14.2.2. Single-Directional Encoding
    3. 14.3. PHP SECURITY RECIPES
      1. 14.3.1. Protecting against SQL Injection
      2. 14.3.2. Protecting against Replay Attacks
        1. 14.3.2.1. What Do Replay Attacks Look Like?
        2. 14.3.2.2. The Code
      3. 14.3.3. Protecting against XSS
        1. 14.3.3.1. What Does XXS Look Like?
        2. 14.3.3.2. The Code
      4. 14.3.4. Protecting against CSRF
        1. 14.3.4.1. What Does a CSRF Look Like?
        2. 14.3.4.2. The Code
      5. 14.3.5. Automation Attacks
    4. 14.4. SUMMARY
  21. 15. Command-Line and Web Services
    1. 15.1. CREATING COMMAND-LINE SCRIPTS
      1. 15.1.1. Reading Command-Line Input
      2. 15.1.2. Prompting for Input
      3. 15.1.3. Completed Classes
      4. 15.1.4. Setting Up Cron Jobs
      5. 15.1.5. Bonus: Output in Color
    2. 15.2. CREATING WEB SERVICES
      1. 15.2.1. RESTful Web Services
        1. 15.2.1.1. Defining Resources
        2. 15.2.1.2. Defining the Controller
        3. 15.2.1.3. Testing Using cURL
        4. 15.2.1.4. Making REST Requests from PHP
      2. 15.2.2. SOAP Web Services
      3. 15.2.3. Getting Started with the SOAP Library
        1. 15.2.3.1. Defining the WSDL
        2. 15.2.3.2. WSDL Types
        3. 15.2.3.3. Defining the Message
        4. 15.2.3.4. Specifying Ports
        5. 15.2.3.5. Defining Bindings
        6. 15.2.3.6. Defining the Endpoint
        7. 15.2.3.7. Setting up a SOAP Server
        8. 15.2.3.8. Setting Up a Soap Client
    3. 15.3. SUMMARY
  22. 16. Optimization and Debugging
    1. 16.1. DEBUGGING PHP
      1. 16.1.1. Creating Stack Traces
    2. 16.2. OPTIMIZING QUERIES
      1. 16.2.1. Reducing SQL
      2. 16.2.2. Identifying SQL Statements
      3. 16.2.3. Optimizing SELECT
        1. 16.2.3.1. The EXPLAIN syntax
        2. 16.2.3.2. Example Queries
        3. 16.2.3.3. Identifying Indexes
        4. 16.2.3.4. About Cardinality
        5. 16.2.3.5. Better Index Types
        6. 16.2.3.6. Optimizing Indexes
        7. 16.2.3.7. When Indexes Are Not Used
        8. 16.2.3.8. Common INDEX Mistakes
        9. 16.2.3.9. Other Commands
      4. 16.2.4. Optimizing UPDATE
      5. 16.2.5. Optimizing DELETE
      6. 16.2.6. Optimizing INSERT
      7. 16.2.7. Optimizing REPLACE
      8. 16.2.8. INSERT ... ON DUPLICATE KEY UPDATE
    3. 16.3. DEBUGGING MYSQL
      1. 16.3.1. Error Log
      2. 16.3.2. Slow Query Log
      3. 16.3.3. Processlist
      4. 16.3.4. Other Commands
      5. 16.3.5. Using a Debugger with MySQL
    4. 16.4. SUMMARY

Product information

  • Title: Expert PHP and MySQL®
  • Author(s): Andrew Curioso, Ronald Bradford, Patrick Galbraith
  • Release date: March 2010
  • Publisher(s): Wrox
  • ISBN: 9780470563120