MySQL Cookbook

Book description

While MySQL has turned up among high profile users such as Yahoo!, NASA and the U.S. Census Bureau, the rising popularity of this open source database is especially keen among users with little database experience. These days, even a small organization or web site has uses for a database, and MySQL is an obvious choice. Affordable and easy to use, MySQL packs the power, speed and efficiency that enable it to rival expensive, proprietary database solutions. Yet, even if you know the basics, anyone without practical MySQL experience--novices and skilled DBAs alike--might stumble over common database-related tasks. Fortunately, there's a sensible shortcut. MySQL Cookbook provides a unique problem-and-solution format that offers practical examples for everyday programming dilemmas. For every problem addressed in the book, there's a worked-out solution or "recipe"--short, focused pieces of code that you can insert directly into your applications. But MySQL Cookbook is more than a collection of cut-and-paste code. You also get explanations of how and why the code works, so you can learn to adapt the techniques to similar situations. The book covers a lot of ground. Solutions for typical MySQL dilemmas range from simple ways to find all records that contain a given string, to more difficult problems, such as finding matching/non-matching records in two tables. Whether you use MySQL on Unix, Linux, Windows or the Mac OS X platform, the book will show you how to:

  • Import data from external sources

  • Export data for use by external programs

  • Access MySQL from your web server

  • Use scripts with MySQL to read queries from a file

  • Access MySQL from within client programs that use Perl, PHP, Java, Python and other languages

  • Construct queries that solve commonly-occurring questions

  • Interact with the server

This learn-as-you-go resource will help users of all levels exploit MySQL more fully. MySQL Cookbook supplies you with an armory of ready-made techniques for specific problems so that, even if you're an experienced MySQL user, you don't have to write everything from scratch.

Table of contents

  1. MySQL Cookbook
    1. Preface
      1. MySQL APIs Used in This Book
      2. Who This Book Is For
      3. What’s in This Book
      4. Platform Notes
      5. Conventions Used in This Book
      6. The Companion Web Site
      7. Comments and Questions
      8. Additional Resources
      9. Acknowledgments
    2. 1. Using the mysql Client Program
      1. Introduction
      2. Setting Up a MySQL User Account
        1. Problem
        2. Solution
        3. Discussion
      3. Creating a Database and a Sample Table
        1. Problem
        2. Solution
        3. Discussion
      4. Starting and Terminating mysql
        1. Problem
        2. Solution
        3. Discussion
      5. Specifying Connection Parameters by Using Option Files
        1. Problem
        2. Solution
        3. Discussion
      6. Protecting Option Files
        1. Problem
        2. Solution
        3. Discussion
      7. Mixing Command-Line and Option File Parameters
        1. Problem
        2. Solution
        3. Discussion
      8. What to Do if mysql Cannot Be Found
        1. Problem
        2. Solution
        3. Discussion
      9. Setting Environment Variables
        1. Problem
        2. Solution
        3. Discussion
      10. Issuing Queries
        1. Problem
        2. Solution
        3. Discussion
      11. Selecting a Database
        1. Problem
        2. Solution
        3. Discussion
      12. Canceling a Partially Entered Query
        1. Problem
        2. Solution
        3. Discussion
      13. Repeating and Editing Queries
        1. Problem
        2. Solution
        3. Discussion
      14. Using Auto-Completion for Database and Table Names
        1. Problem
        2. Solution
        3. Discussion
      15. Using SQL Variables in Queries
        1. Problem
        2. Solution
        3. Discussion
      16. Telling mysql to Read Queries from a File
        1. Problem
        2. Solution
        3. Discussion
      17. Telling mysql to Read Queries from Other Programs
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      18. Specifying Queries on the Command Line
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      19. Using Copy and Paste as a mysql Input Source
        1. Problem
        2. Solution
        3. Discussion
      20. Preventing Query Output from Scrolling off the Screen
        1. Problem
        2. Solution
        3. Discussion
      21. Sending Query Output to a File or to a Program
        1. Problem
        2. Solution
        3. Discussion
      22. Selecting Tabular or Tab-Delimited Query Output Format
        1. Problem
        2. Solution
        3. Discussion
      23. Specifying Arbitrary Output Column Delimiters
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      24. Producing HTML Output
        1. Problem
        2. Solution
        3. Discussion
      25. Producing XML Output
        1. Problem
        2. Solution
        3. Discussion
      26. Suppressing Column Headings in Query Output
        1. Problem
        2. Solution
        3. Discussion
      27. Numbering Query Output Lines
        1. Problem
        2. Solution
        3. Discussion
      28. Making Long Output Lines More Readable
        1. Problem
        2. Solution
        3. Discussion
      29. Controlling mysql’s Verbosity Level
        1. Problem
        2. Solution
        3. Discussion
      30. Logging Interactive mysql Sessions
        1. Problem
        2. Solution
        3. Discussion
      31. Creating mysql Scripts from Previously Executed Queries
        1. Problem
        2. Solution
        3. Discussion
      32. Using mysql as a Calculator
        1. Problem
        2. Solution
        3. Discussion
      33. Using mysql in Shell Scripts
        1. Problem
        2. Solution
        3. Discussion
        4. Writing Shell Scripts Under Unix
        5. Writing Shell Scripts Under Windows
    3. 2. Writing MySQL-Based Programs
      1. Introduction
        1. MySQL Client Application Programming Interfaces
        2. Assumptions
      2. Connecting to the MySQL Server, Selecting a Database, and Disconnecting
        1. Problem
        2. Solution
        3. Discussion
        4. Perl
          1. Additional connection parameters
        5. PHP
          1. Additional connection parameters
        6. Python
          1. Additional connection parameters
        7. Java
          1. Additional connection parameters
      3. Checking for Errors
        1. Problem
        2. Solution
        3. Discussion
        4. Perl
        5. PHP
        6. Python
        7. Java
      4. Writing Library Files
        1. Problem
        2. Solution
        3. Discussion
        4. Perl
        5. PHP
        6. Python
        7. Java
      5. Issuing Queries and Retrieving Results
        1. Problem
        2. Solution
        3. Discussion
        4. SQL Statement Categories
        5. Perl
        6. PHP
        7. Python
        8. Java
      6. Moving Around Within a Result Set
        1. Problem
        2. Solution
        3. Discussion
      7. Using Prepared Statements and Placeholders in Queries
        1. Problem
        2. Solution
        3. Discussion
        4. Perl
        5. PHP
        6. Python
        7. Java
      8. Including Special Characters and NULL Values in Queries
        1. Problem
        2. Solution
        3. Discussion
        4. Perl
        5. PHP
        6. Python
        7. Java
      9. Handling NULL Values in Result Sets
        1. Problem
        2. Solution
        3. Discussion
        4. Perl
        5. PHP
        6. Python
        7. Java
      10. Writing an Object-Oriented MySQL Interface for PHP
        1. Problem
        2. Solution
        3. Discussion
        4. Class Overview
        5. Connecting and Disconnecting
        6. Error Handling
        7. Issuing Queries and Processing the Results
        8. Quoting and Placeholder Support
      11. Ways of Obtaining Connection Parameters
        1. Problem
        2. Solution
        3. Discussion
        4. Getting Parameters from the Command Line
          1. Perl
          2. PHP
          3. Python
          4. Java
        5. Getting Parameters from Option Files
          1. Perl
          2. PHP
          3. Python
          4. Java
      12. Conclusion and Words of Advice
    4. 3. Record Selection Techniques
      1. Introduction
      2. Specifying Which Columns to Display
        1. Problem
        2. Solution
        3. Discussion
      3. Avoiding Output Column Order Problems When Writing Programs
        1. Problem
        2. Solution
        3. Discussion
      4. Giving Names to Output Columns
        1. Problem
        2. Solution
        3. Discussion
      5. Using Column Aliases to Make Programs Easier to Write
        1. Problem
        2. Solution
        3. Discussion
      6. Combining Columns to Construct Composite Values
        1. Problem
        2. Solution
        3. Discussion
      7. Specifying Which Rows to Select
        1. Problem
        2. Solution
        3. Discussion
      8. WHERE Clauses and Column Aliases
        1. Problem
        2. Solution
        3. Discussion
      9. Displaying Comparisons to Find Out How Something Works
        1. Problem
        2. Solution
        3. Discussion
      10. Reversing or Negating Query Conditions
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      11. Removing Duplicate Rows
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      12. Working with NULL Values
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      13. Negating a Condition on a Column That Contains NULL Values
        1. Problem
        2. Solution
        3. Discussion
      14. Writing Comparisons Involving NULL in Programs
        1. Problem
        2. Solution
        3. Discussion
      15. Mapping NULL Values to Other Values for Display
        1. Problem
        2. Solution
        3. Discussion
      16. Sorting a Result Set
        1. Problem
        2. Solution
        3. Discussion
      17. Selecting Records from the Beginning or End of a Result Set
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      18. Pulling a Section from the Middle of a Result Set
        1. Problem
        2. Solution
        3. Discussion
      19. Choosing Appropriate LIMIT Values
        1. Problem
        2. Solution
        3. Discussion
      20. Calculating LIMIT Values from Expressions
        1. Problem
        2. Solution
        3. Discussion
      21. What to Do When LIMIT Requires the “Wrong” Sort Order
        1. Problem
        2. Solution
        3. Discussion
      22. Selecting a Result Set into an Existing Table
        1. Problem
        2. Solution
        3. Discussion
      23. Creating a Destination Table on the Fly from a Result Set
        1. Problem
        2. Solution
        3. Discussion
      24. Moving Records Between Tables Safely
        1. Problem
        2. Solution
        3. Discussion
      25. Creating Temporary Tables
        1. Problem
        2. Solution
        3. Discussion
      26. Cloning a Table Exactly
        1. Problem
        2. Solution
        3. Discussion
      27. Generating Unique Table Names
        1. Problem
        2. Solution
        3. Discussion
    5. 4. Working with Strings
      1. Introduction
        1. Types of Strings
      2. Writing Strings That Include Quotes or Special Characters
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      3. Preserving Trailing Spaces in String Columns
        1. Problem
        2. Solution
        3. Discussion
      4. Testing String Equality or Relative Ordering
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      5. Decomposing or Combining Strings
        1. Problem
        2. Solution
        3. Discussion
      6. Checking Whether a String Contains a Substring
        1. Problem
        2. Solution
        3. Discussion
      7. Pattern Matching with SQL Patterns
        1. Problem
        2. Solution
        3. Discussion
      8. Pattern Matching with Regular Expressions
        1. Problem
        2. Solution
        3. Discussion
      9. Matching Pattern Metacharacters Literally
        1. Problem
        2. Solution
        3. Discussion
      10. Controlling Case Sensitivity in String Comparisons
        1. Problem
        2. Solution
        3. Discussion
      11. Controlling Case Sensitivity in Pattern Matching
        1. Problem
        2. Solution
        3. Discussion
      12. Using FULLTEXT Searches
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      13. Using a FULLTEXT Search with Short Words
        1. Problem
        2. Solution
        3. Discussion
      14. Requiring or Excluding FULLTEXT Search Words
        1. Problem
        2. Solution
        3. Discussion
      15. Performing Phrase Searches with a FULLTEXT Index
        1. Problem
        2. Solution
        3. Discussion
    6. 5. Working with Dates and Times
      1. Introduction
        1. MySQL’s Date and Time Formats
      2. Changing MySQL’s Date Format
        1. Problem
        2. Solution
        3. Discussion
      3. Telling MySQL How to Display Dates or Times
        1. Problem
        2. Solution
        3. Discussion
      4. Determining the Current Date or Time
        1. Problem
        2. Solution
        3. Discussion
      5. Decomposing Dates and Times Using Formatting Functions
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      6. Decomposing Dates or Times Using Component-Extraction Functions
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      7. Decomposing Dates or Times Using String Functions
        1. Problem
        2. Solution
        3. Discussion
      8. Synthesizing Dates or Times Using Formatting Functions
        1. Problem
        2. Solution
        3. Discussion
      9. Synthesizing Dates or Times Using Component-Extraction Functions
        1. Problem
        2. Solution
        3. Discussion
      10. Combining a Date and a Time into a Date-and-Time Value
        1. Problem
        2. Solution
        3. Discussion
      11. Converting Between Times and Seconds
        1. Problem
        2. Solution
        3. Discussion
      12. Converting Between Dates and Days
        1. Problem
        2. Solution
        3. Discussion
      13. Converting Between Date-and-Time Values and Seconds
        1. Problem
        2. Solution
        3. Discussion
      14. Adding a Temporal Interval to a Time
        1. Problem
        2. Solution
        3. Discussion
      15. Calculating Intervals Between Times
        1. Problem
        2. Solution
        3. Discussion
      16. Breaking Down Time Intervals into Components
        1. Problem
        2. Solution
        3. Discussion
      17. Adding a Temporal Interval to a Date
        1. Problem
        2. Solution
        3. Discussion
      18. Calculating Intervals Between Dates
        1. Problem
        2. Solution
        3. Discussion
      19. Canonizing Not-Quite-ISO Date Strings
        1. Problem
        2. Solution
        3. Discussion
      20. Calculating Ages
        1. Problem
        2. Solution
        3. Discussion
        4. Determining Ages in Years
        5. Determining Ages in Months
      21. Shifting Dates by a Known Amount
        1. Problem
        2. Solution
        3. Discussion
        4. Calculating Anniversary Dates
        5. Time Zone Adjustments
      22. Finding First and Last Days of Months
        1. Problem
        2. Solution
        3. Discussion
      23. Finding the Length of a Month
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      24. Calculating One Date from Another by Substring Replacement
        1. Problem
        2. Solution
        3. Discussion
      25. Finding the Day of the Week for a Date
        1. Problem
        2. Solution
        3. Discussion
      26. Finding Dates for Days of the Current Week
        1. Problem
        2. Solution
        3. Discussion
      27. Finding Dates for Weekdays of Other Weeks
        1. Problem
        2. Solution
        3. Discussion
      28. Performing Leap Year Calculations
        1. Problem
        2. Solution
        3. Discussion
        4. Determining Whether a Date Occurs in a Leap Year
        5. Using Leap Year Tests for Year-Length Calculations
        6. Using Leap Year Tests for Month-Length Calculations
      29. Treating Dates or Times as Numbers
        1. Problem
        2. Solution
        3. Discussion
      30. Forcing MySQL to Treat Strings as Temporal Values
        1. Problem
        2. Solution
        3. Discussion
      31. Selecting Records Based on Their Temporal Characteristics
        1. Problem
        2. Solution
        3. Discussion
        4. Comparing Dates to One Another
        5. Comparing Times to One Another
        6. Comparing Dates to Calendar Days
      32. Using TIMESTAMP Values
        1. Problem
        2. Solution
        3. Discussion
      33. Recording a Row’s Last Modification Time
        1. Problem
        2. Solution
        3. Discussion
      34. Recording a Row’s Creation Time
        1. Problem
        2. Solution
        3. Discussion
      35. Performing Calculations with TIMESTAMP Values
        1. Problem
        2. Solution
        3. Discussion
      36. Displaying TIMESTAMP Values in Readable Form
        1. Problem
        2. Solution
        3. Discussion
    7. 6. Sorting Query Results
      1. Introduction
      2. Using ORDER BY to Sort Query Results
        1. Problem
        2. Solution
        3. Discussion
        4. Naming the Sort Columns and Specifying Sorting Direction
        5. More Ways to Refer to Sort Columns
      3. Sorting Subsets of a Table
        1. Problem
        2. Solution
        3. Discussion
      4. Sorting Expression Results
        1. Problem
        2. Solution
        3. Discussion
      5. Displaying One Set of Values While Sorting by Another
        1. Problem
        2. Solution
        3. Discussion
      6. Sorting and NULL Values
        1. Problem
        2. Solution
        3. Discussion
      7. Controlling Case Sensitivity of String Sorts
        1. Problem
        2. Solution
        3. Discussion
      8. Date-Based Sorting
        1. Problem
        2. Solution
        3. Discussion
      9. Sorting by Calendar Day
        1. Problem
        2. Solution
        3. Discussion
      10. Sorting by Day of Week
        1. Problem
        2. Solution
        3. Discussion
      11. Sorting by Time of Day
        1. Problem
        2. Solution
        3. Discussion
      12. Sorting Using Substrings of Column Values
        1. Problem
        2. Solution
        3. Discussion
      13. Sorting by Fixed-Length Substrings
        1. Problem
        2. Solution
        3. Discussion
      14. Sorting by Variable-Length Substrings
        1. Problem
        2. Solution
        3. Discussion
      15. Sorting Hostnames in Domain Order
        1. Problem
        2. Solution
        3. Discussion
      16. Sorting Dotted-Quad IP Values in Numeric Order
        1. Problem
        2. Solution
        3. Discussion
      17. Floating Specific Values to the Head or Tail of the Sort Order
        1. Problem
        2. Solution
        3. Discussion
      18. Sorting in User-Defined Orders
        1. Problem
        2. Solution
        3. Discussion
      19. Sorting ENUM Values
        1. Problem
        2. Solution
        3. Discussion
    8. 7. Generating Summaries
      1. Introduction
      2. Summarizing with COUNT( )
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      3. Summarizing with MIN( ) and MAX( )
        1. Problem
        2. Solution
        3. Discussion
      4. Summarizing with SUM( ) and AVG( )
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      5. Using DISTINCT to Eliminate Duplicates
        1. Problem
        2. Solution
        3. Discussion
      6. Finding Values Associated with Minimum and Maximum Values
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      7. Controlling String Case Sensitivity for MIN( ) and MAX( )
        1. Problem
        2. Solution
        3. Discussion
      8. Dividing a Summary into Subgroups
        1. Problem
        2. Solution
        3. Discussion
      9. Summaries and NULL Values
        1. Problem
        2. Solution
        3. Discussion
      10. Selecting Only Groups with Certain Characteristics
        1. Problem
        2. Solution
        3. Discussion
      11. Determining Whether Values are Unique
        1. Problem
        2. Solution
        3. Discussion
      12. Grouping by Expression Results
        1. Problem
        2. Solution
        3. Discussion
      13. Categorizing Non-Categorical Data
        1. Problem
        2. Solution
        3. Discussion
      14. Controlling Summary Display Order
        1. Problem
        2. Solution
        3. Discussion
      15. Finding Smallest or Largest Summary Values
        1. Problem
        2. Solution
        3. Discussion
      16. Date-Based Summaries
        1. Problem
        2. Solution
        3. Discussion
      17. Working with Per-Group and Overall Summary Values Simultaneously
        1. Problem
        2. Solution
        3. Discussion
      18. Generating a Report That Includes a Summary and a List
        1. Problem
        2. Solution
        3. Discussion
    9. 8. Modifying Tables with ALTER TABLE
      1. Introduction
      2. Dropping, Adding, or Repositioning a Column
        1. Problem
        2. Solution
        3. Discussion
      3. Changing a Column Definition or Name
        1. Problem
        2. Solution
        3. Discussion
      4. The Effect of ALTER TABLE on Null and Default Value Attributes
        1. Problem
        2. Solution
        3. Discussion
      5. Changing a Column’s Default Value
        1. Problem
        2. Solution
        3. Discussion
      6. Changing a Table Type
        1. Problem
        2. Solution
        3. Discussion
      7. Renaming a Table
        1. Problem
        2. Solution
        3. Discussion
      8. Adding or Dropping Indexes
        1. Problem
        2. Solution
        3. Discussion
        4. Adding Indexes
        5. Dropping Indexes
        6. See Also
      9. Eliminating Duplicates by Adding an Index
        1. Problem
        2. Solution
        3. Discussion
      10. Using ALTER TABLE to Normalize a Table
        1. Problem
        2. Solution
        3. Discussion
    10. 9. Obtaining and Using Metadata
      1. Introduction
      2. Obtaining the Number of Rows Affected by a Query
        1. Problem
        2. Solution
        3. Discussion
        4. Perl
        5. PHP
        6. Python
        7. Java
      3. Obtaining Result Set Metadata
        1. Problem
        2. Solution
        3. Discussion
        4. Perl
        5. PHP
        6. Python
        7. Java
      4. Determining Presence or Absence of a Result Set
        1. Problem
        2. Solution
        3. Discussion
      5. Formatting Query Results for Display
        1. Problem
        2. Solution
        3. Discussion
      6. Getting Table Structure Information
        1. Problem
        2. Solution
        3. Discussion
        4. Using SHOW COLUMNS to Get Table Structure
        5. Using Result Set Metadata to Get Table Structure
        6. Using CREATE TABLE to Get Table Structure
      7. Getting ENUM and SET Column Information
        1. Problem
        2. Solution
        3. Discussion
      8. Database-Independent Methods of Obtaining Table Information
        1. Problem
        2. Solution
        3. Discussion
      9. Applying Table Structure Information
        1. Problem
        2. Solution
        3. Discussion
        4. Displaying Column Lists
        5. Interactive Record Editing
        6. Mapping Column Types onto Web Page Elements
        7. Adding Elements to ENUM or SET Column Definitions
        8. Retrieving Dates in Non-ISO Format
        9. Converting Character Columns Between Fixed-Length and Variable-Length Types
        10. Selecting All Except Certain Columns
      10. Listing Tables and Databases
        1. Problem
        2. Solution
        3. Discussion
      11. Testing Whether a Table Exists
        1. Problem
        2. Solution
        3. Discussion
      12. Testing Whether a Database Exists
        1. Problem
        2. Solution
        3. Discussion
      13. Getting Server Metadata
        1. Problem
        2. Solution
        3. Discussion
      14. Writing Applications That Adapt to the MySQL Server Version
        1. Problem
        2. Solution
        3. Discussion
      15. Determining the Current Database
        1. Problem
        2. Solution
        3. Discussion
      16. Determining the Current MySQL User
        1. Problem
        2. Solution
        3. Discussion
      17. Monitoring the MySQL Server
        1. Problem
        2. Solution
        3. Discussion
      18. Determining Which Table Types the Server Supports
        1. Problem
        2. Solution
        3. Discussion
    11. 10. Importing and Exporting Data
      1. Introduction
        1. General Import and Export Issues
        2. File Formats
        3. Notes on Invoking Shell Commands
      2. Importing Data with LOAD DATA and mysqlimport
        1. Problem
        2. Solution
        3. Discussion
      3. Specifying the Datafile Location
        1. Problem
        2. Solution
        3. Discussion
      4. Specifying the Datafile Format
        1. Problem
        2. Solution
        3. Discussion
      5. Dealing with Quotes and Special Characters
        1. Problem
        2. Solution
        3. Discussion
      6. Importing CSV Files
        1. Problem
        2. Solution
        3. Discussion
      7. Reading Files from Different Operating Systems
        1. Problem
        2. Solution
        3. Discussion
      8. Handling Duplicate Index Values
        1. Problem
        2. Solution
        3. Discussion
      9. Getting LOAD DATA to Cough Up More Information
        1. Problem
        2. Solution
        3. Discussion
      10. Don’t Assume LOAD DATA Knows More than It Does
        1. Problem
        2. Solution
        3. Discussion
      11. Skipping Datafile Lines
        1. Problem
        2. Solution
        3. Discussion
      12. Specifying Input Column Order
        1. Problem
        2. Solution
        3. Discussion
      13. Skipping Datafile Columns
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      14. Exporting Query Results from MySQL
        1. Problem
        2. Solution
        3. Discussion
        4. Exporting with the SELECT ... INTO OUTFILE Statement
        5. Using the mysql Client to Export Data
        6. See Also
      15. Exporting Tables as Raw Data
        1. Problem
        2. Solution
        3. Discussion
      16. Exporting Table Contents or Definitions in SQL Format
        1. Problem
        2. Solution
        3. Discussion
      17. Copying Tables or Databases to Another Server
        1. Problem
        2. Solution
        3. Discussion
      18. Writing Your Own Export Programs
        1. Problem
        2. Solution
        3. Discussion
      19. Converting Datafiles from One Format to Another
        1. Problem
        2. Solution
        3. Discussion
      20. Extracting and Rearranging Datafile Columns
        1. Problem
        2. Solution
        3. Discussion
      21. Validating and Transforming Data
        1. Problem
        2. Solution
        3. Discussion
        4. Writing an Input-Processing Loop
        5. Putting Common Tests in Libraries
      22. Validation by Direct Comparison
        1. Problem
        2. Solution
        3. Discussion
      23. Validation by Pattern Matching
        1. Problem
        2. Solution
        3. Discussion
      24. Using Patterns to Match Broad Content Types
        1. Problem
        2. Solution
        3. Discussion
      25. Using Patterns to Match Numeric Values
        1. Problem
        2. Solution
        3. Discussion
      26. Using Patterns to Match Dates or Times
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      27. Using Patterns to Match Email Addresses and URLs
        1. Problem
        2. Solution
        3. Discussion
      28. Validation Using Table Metadata
        1. Problem
        2. Solution
        3. Discussion
      29. Validation Using a Lookup Table
        1. Problem
        2. Solution
        3. Discussion
        4. Issue Individual Queries
        5. Construct a Hash from the Entire Lookup Table
        6. Use a Hash as a Cache of Already-Seen Lookup Values
      30. Converting Two-Digit Year Values to Four-Digit Form
        1. Problem
        2. Solution
        3. Discussion
      31. Performing Validity Checking on Date or Time Subparts
        1. Problem
        2. Solution
        3. Discussion
      32. Writing Date-Processing Utilities
        1. Problem
        2. Solution
        3. Discussion
      33. Using Dates with Missing Components
        1. Problem
        2. Solution
        3. Discussion
      34. Performing Date Conversion Using SQL
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      35. Using Temporary Tables for Data Transformation
        1. Problem
        2. Solution
        3. Discussion
      36. Dealing with NULL Values
        1. Problem
        2. Solution
        3. Discussion
      37. Guessing Table Structure from a Datafile
        1. Problem
        2. Solution
        3. Discussion
      38. A LOAD DATA Diagnostic Utility
        1. Problem
        2. Solution
        3. Discussion
      39. Exchanging Data Between MySQL and Microsoft Access
        1. Problem
        2. Solution
        3. Discussion
      40. Exchanging Data Between MySQL and Microsoft Excel
        1. Problem
        2. Solution
        3. Discussion
      41. Exchanging Data Between MySQL and FileMaker Pro
        1. Problem
        2. Solution
        3. Discussion
      42. Exporting Query Results as XML
        1. Problem
        2. Solution
        3. Discussion
      43. Importing XML into MySQL
        1. Problem
        2. Solution
        3. Discussion
      44. Epilog
    12. 11. Generating and Using Sequences
      1. Introduction
      2. Using AUTO_INCREMENT To Set Up a Sequence Column
        1. Problem
        2. Solution
        3. Discussion
      3. Generating Sequence Values
        1. Problem
        2. Solution
        3. Discussion
      4. Choosing the Type for a Sequence Column
        1. Problem
        2. Solution
        3. Discussion
      5. The Effect of Record Deletions on Sequence Generation
        1. Problem
        2. Solution
        3. Discussion
      6. Retrieving Sequence Values
        1. Problem
        2. Solution
        3. Discussion
        4. Using LAST_INSERT_ID( ) to Obtain AUTO_INCREMENT Values
        5. Using API-Specific Methods to Obtain AUTO_INCREMENT Values
          1. Perl
          2. PHP
          3. Python
          4. Java
        6. Server-Side and Client-Side SequenceValue Retrieval Compared
      7. Determining Whether to Resequence a Column
        1. Problem
        2. Solution
        3. Discussion
        4. Reasons to Avoid Resequencing
      8. Extending the Range of a Sequence Column
        1. Problem
        2. Solution
        3. Discussion
      9. Renumbering an Existing Sequence
        1. Problem
        2. Solution
        3. Discussion
      10. Reusing Values at the Top of a Sequence
        1. Problem
        2. Solution
        3. Discussion
      11. Ensuring That Rows Are Renumbered in a Particular Order
        1. Problem
        2. Solution
        3. Discussion
      12. Starting a Sequence at a Particular Value
        1. Problem
        2. Solution
        3. Discussion
      13. Sequencing an Unsequenced Table
        1. Problem
        2. Solution
        3. Discussion
      14. Using an AUTO_INCREMENT Column to Create Multiple Sequences
        1. Problem
        2. Solution
        3. Discussion
      15. Managing Multiple SimultaneousAUTO_INCREMENT Values
        1. Problem
        2. Solution
        3. Discussion
      16. Using AUTO_INCREMENT Valuesto Relate Tables
        1. Problem
        2. Solution
        3. Discussion
      17. Using Single-Row Sequence Generators
        1. Problem
        2. Solution
        3. Discussion
      18. Generating Repeating Sequences
        1. Problem
        2. Solution
        3. Discussion
      19. Numbering Query Output Rows Sequentially
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
    13. 12. Using Multiple Tables
      1. Introduction
      2. Combining Rows in One Table with Rows in Another
        1. Problem
        2. Solution
        3. Discussion
      3. Performing a Join Between Tables in Different Databases
        1. Problem
        2. Solution
        3. Discussion
      4. Referring to Join Output Column Names in Programs
        1. Problem
        2. Solution
        3. Discussion
      5. Finding Rows in One Table That Match Rows in Another
        1. Problem
        2. Solution
        3. Discussion
      6. Finding Rows with No Match in Another Table
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      7. Finding Rows Containing Per-Group Minimum or Maximum Values
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      8. Computing Team Standings
        1. Problem
        2. Solution
        3. Discussion
      9. Producing Master-Detail Lists and Summaries
        1. Problem
        2. Solution
        3. Discussion
      10. Using a Join to Fill in Holes in a List
        1. Problem
        2. Solution
        3. Discussion
      11. Enumerating a Many-to-Many Relationship
        1. Problem
        2. Solution
        3. Discussion
      12. Comparing a Table to Itself
        1. Problem
        2. Solution
        3. Discussion
      13. Calculating Differences Between Successive Rows
        1. Problem
        2. Solution
        3. Discussion
      14. Finding Cumulative Sums and Running Averages
        1. Problem
        2. Solution
        3. Discussion
      15. Using a Join to Control Query Output Order
        1. Problem
        2. Solution
        3. Discussion
      16. Converting Subselects to Join Operations
        1. Problem
        2. Solution
        3. Discussion
      17. Selecting Records in Parallel from Multiple Tables
        1. Problem
        2. Solution
        3. Discussion
      18. Inserting Records in One Table That Include Values from Another
        1. Problem
        2. Solution
        3. Discussion
      19. Updating One Table Based on Values in Another
        1. Problem
        2. Solution
        3. Discussion
        4. Performing a Related-Table Update Using Table Replacement
        5. Performing a Related-Table Update by Writing a Program
        6. Performing a Related-Table Update Using mysql
      20. Using a Join to Create a Lookup Table from Descriptive Labels
        1. Problem
        2. Solution
        3. Discussion
      21. Deleting Related Rows in Multiple Tables
        1. Problem
        2. Solution
        3. Discussion
        4. Performing a Cascaded Delete with a Multiple-Table DELETE Statement
        5. Performing a Multiple-Table Delete Using Table Replacement
        6. Performing a Multiple-Table Delete by Writing a Program
        7. Performing a Multiple-Table Delete Using mysql
      22. Identifying and Removing Unattached Records
        1. Problem
        2. Solution
        3. Discussion
      23. Using Different MySQL Servers Simultaneously
        1. Problem
        2. Solution
        3. Discussion
    14. 13. Statistical Techniques
      1. Introduction
      2. Calculating Descriptive Statistics
        1. Problem
        2. Solution
        3. Discussion
      3. Per-Group Descriptive Statistics
        1. Problem
        2. Solution
        3. Discussion
      4. Generating Frequency Distributions
        1. Problem
        2. Solution
        3. Discussion
      5. Counting Missing Values
        1. Problem
        2. Solution
        3. Discussion
      6. Calculating Linear Regressions or Correlation Coefficients
        1. Problem
        2. Solution
        3. Discussion
      7. Generating Random Numbers
        1. Problem
        2. Solution
        3. Discussion
      8. Randomizing a Set of Rows
        1. Problem
        2. Solution
        3. Discussion
      9. Selecting Random Items from a Set of Rows
        1. Problem
        2. Solution
        3. Discussion
      10. Assigning Ranks
        1. Problem
        2. Solution
        3. Discussion
    15. 14. Handling Duplicates
      1. Introduction
      2. Preventing Duplicates from Occurring in a Table
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      3. Dealing with Duplicates at Record-Creation Time
        1. Problem
        2. Solution
        3. Discussion
      4. Counting and Identifying Duplicates
        1. Problem
        2. Solution
        3. Discussion
      5. Eliminating Duplicates from a Query Result
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      6. Eliminating Duplicates from a Self-Join Result
        1. Problem
        2. Solution
        3. Discussion
      7. Eliminating Duplicates from a Table
        1. Problem
        2. Solution
        3. Discussion
        4. Removing Duplicates Using Table Replacement
        5. Removing Duplicates by Adding an Index
        6. Removing Duplicates of a Particular Row
    16. 15. Performing Transactions
      1. Introduction
      2. Verifying Transaction Support Requirements
        1. Problem
        2. Solution
        3. Discussion
      3. Performing Transactions Using SQL
        1. Problem
        2. Solution
        3. Discussion
      4. Performing Transactions from Within Programs
        1. Problem
        2. Solution
        3. Discussion
      5. Using Transactions in Perl Programs
        1. Problem
        2. Solution
        3. Discussion
      6. Using Transactions in PHP Programs
        1. Problem
        2. Solution
        3. Discussion
      7. Using Transactions in Python Programs
        1. Problem
        2. Solution
        3. Discussion
      8. Using Transactions in Java Programs
        1. Problem
        2. Solution
        3. Discussion
      9. Using Alternatives to Transactions
        1. Problem
        2. Solution
        3. Discussion
        4. Grouping Statements Using Locks
        5. Rewriting Queries to Avoid Transactions
    17. 16. Introduction to MySQL on the Web
      1. Introduction
      2. Basic Web Page Generation
        1. Problem
        2. Solution
        3. Discussion
      3. Using Apache to Run Web Scripts
        1. Problem
        2. Solution
        3. Discussion
          1. Perl
          2. PHP
          3. Python
      4. Using Tomcat to Run Web Scripts
        1. Problem
        2. Solution
        3. Discussion
        4. Installing the mcb Application
        5. Installing the JDBC Driver
        6. Installing the JSTL Distribution
        7. Writing JSP Pages with JSTL
        8. Writing a MySQL Script using JSP and JSTL
      5. Encoding Special Characters in Web Output
        1. Problem
        2. Solution
        3. Discussion
        4. General Encoding Principles
          1. Encoding characters that are special in HTML
          2. Encoding characters that are special in URLs
          3. Encoding interactions
        5. Encoding Special Characters Using Web APIs
          1. Perl
          2. PHP
          3. Python
          4. Java
    18. 17. Incorporating Query Resultsinto Web Pages
      1. Introduction
      2. Displaying Query Results as Paragraph Text
        1. Problem
        2. Solution
        3. Discussion
      3. Displaying Query Results as Lists
        1. Problem
        2. Solution
        3. Discussion
        4. Ordered Lists
        5. Unordered Lists
        6. Definition Lists
        7. Unmarked Lists
        8. Nested Lists
      4. Displaying Query Results as Tables
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      5. Displaying Query Results as Hyperlinks
        1. Problem
        2. Solution
        3. Discussion
      6. Creating a Navigation Index from Database Content
        1. Problem
        2. Solution
        3. Discussion
        4. Creating a Single-Page Navigation Index
        5. Creating a Multiple-Page Navigation Index
        6. See Also
      7. Storing Images or Other Binary Data
        1. Problem
        2. Solution
        3. Discussion
        4. Storing Images with LOAD_FILE( )
        5. Storing Images Using a Script
        6. See Also
      8. Retrieving Images or Other Binary Data
        1. Problem
        2. Solution
        3. Discussion
      9. Serving Banner Ads
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      10. Serving Query Results for Download
        1. Problem
        2. Solution
        3. Discussion
    19. 18. Processing Web Input with MySQL
      1. Introduction
      2. Creating Forms in Scripts
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      3. Creating Single-Pick Form Elements from Database Content
        1. Problem
        2. Solution
        3. Discussion
      4. Creating Multiple-Pick Form Elements from Database Content
        1. Problem
        2. Solution
        3. Discussion
      5. Loading a Database Record into a Form
        1. Problem
        2. Solution
        3. Discussion
      6. Collecting Web Input
        1. Problem
        2. Solution
        3. Discussion
        4. Web Input Extraction Conventions
        5. Perl
        6. PHP
        7. Python
        8. Java
      7. Validating Web Input
        1. Problem
        2. Solution
        3. Discussion
      8. Using Web Input to Construct Queries
        1. Problem
        2. Solution
        3. Discussion
        4. See Also
      9. Processing File Uploads
        1. Problem
        2. Solution
        3. Discussion
        4. Perl
        5. PHP
        6. Python
      10. Performing Searches and Presenting the Results
        1. Problem
        2. Solution
        3. Discussion
      11. Generating Previous-Page and Next-Page Links
        1. Problem
        2. Solution
        3. Discussion
        4. Paged Displays with Previous-Page and Next-Page Links
        5. Paged Displays with Links to Each Page
      12. Generating “Click to Sort” Table Headings
        1. Problem
        2. Solution
        3. Discussion
      13. Web Page Access Counting
        1. Problem
        2. Solution
        3. Discussion
      14. Web Page Access Logging
        1. Problem
        2. Solution
        3. Discussion
      15. Using MySQL for Apache Logging
        1. Problem
        2. Solution
        3. Discussion
        4. Setting Up Database Logging
        5. Analyzing the Log File
        6. Other Logging Issues
    20. 19. Using MySQL-Based Web Session Management
      1. Introduction
        1. Session Management Issues
      2. Using MySQL-Based Sessions in Perl Applications
        1. Problem
        2. Solution
        3. Discussion
        4. Installing Apache::Session
        5. The Apache::Session Interface
        6. A Sample Application
        7. Session Expiration
      3. Using MySQL-Based Storage with the PHP Session Manager
        1. Problem
        2. Solution
        3. Discussion
        4. The PHP 4 Session Management Interface
        5. Specifying a User-Defined Storage Module
          1. Creating the session table
          2. Writing the storage management routines
          3. Using the storage module
      4. Using MySQL for Session BackingStore with Tomcat
        1. Problem
        2. Solution
        3. Discussion
        4. The Servlet and JSP Session Interface
        5. A Sample JSP Session Application
        6. Telling Tomcat to Save Session Records in MySQL
          1. Create the Tomcat session table
          2. Place the JDBC driver where Tomcat can find It
          3. Modify the Tomcat Configuration File
        7. Session Expiration in Tomcat
        8. Session Tracking in Tomcat
    21. A. Obtaining MySQL Software
      1. Obtaining Sample Source Code and Data
      2. Obtaining MySQL and Related Software
        1. MySQL
        2. Perl Support
        3. PHP Support
        4. Python Support
        5. Java Support
        6. Web Servers
        7. Miscellaneous Software
    22. B. JSP and Tomcat Primer
      1. Servlet and JavaServer Pages Overview
        1. JSP Pages—An Alternative to Servlets
        2. Custom Actions and Tag Libraries
      2. Setting Up a Tomcat Server
        1. Installing a Tomcat Distribution
        2. Starting and Stopping Tomcat
        3. Tomcat’s Directory Structure
          1. Application directories
          2. Configuration and control directories
          3. Class directories
          4. Operational directories
        4. Restarting Applications Without Restarting Tomcat
      3. Web Application Structure
      4. Elements of JSP Pages
        1. Scripting Elements
        2. JSP Directives
        3. Action Elements
        4. Using a Tag Library
        5. Implicit JSP Objects
        6. Levels of Scope in JSP Pages
    23. C. References
      1. MySQL Resources
      2. Perl Resources
      3. PHP Resources
      4. Python Resources
      5. Java Resources
      6. Apache Resources
      7. Other Resources
    24. Index
    25. Colophon

Product information

  • Title: MySQL Cookbook
  • Author(s):
  • Release date: October 2002
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596001452