MySQL Cookbook

MySQL Cookbook

by Paul DuBois

Paperback(Second Edition)

$41.76 $49.99 Save 16% Current price is $41.76, Original price is $49.99. You Save 16%. View All Available Formats & Editions

Product Details

ISBN-13: 9780596527082
Publisher: O'Reilly Media, Incorporated
Publication date: 12/28/2006
Edition description: Second Edition
Pages: 980
Product dimensions: 7.00(w) x 9.20(h) x 2.10(d)

About the Author

Paul DuBois is one of the primary contributors to the MySQL Reference Manual, a renowned online manual that has supported MySQL administrators and database developers for years. He is a member of the MySQL documentation team at Oracle Corp and is also the author of MySQL (Addison-Wesley Professional); Using csh & tcsh; Software Portability with imake; and MySQL and Perl for the Web (New Riders).

Table of Contents

Preface;
Who This Book Is For;
What’s in This Book;
MySQL APIs Used in This Book;
Conventions Used in This Book;
The MySQL Cookbook Companion Web Site;
Version and Platform Notes;
Upgrade Note for First Edition Readers;
Additional Resources;
Using Code Examples;
Safari® Enabled;
How to Contact Us;
Acknowledgments;
Second Edition;
First Edition;
Chapter 1: Using the mysql Client Program;
Introduction;
Setting Up a MySQL User Account;
Problem;
Solution;
Discussion;
Creating a Database and a Sample Table;
Problem;
Solution;
Discussion;
Starting and Stopping mysql;
Problem;
Solution;
Discussion;
Specifying Connection Parameters Using Option Files;
Problem;
Solution;
Discussion;
Protecting Option Files from Other Users;
Problem;
Solution;
Discussion;
Mixing Command-Line and Option File Parameters;
Problem;
Solution;
Discussion;
What to Do if mysql Cannot Be Found;
Problem;
Solution;
Discussion;
Issuing SQL Statements;
Problem;
Solution;
Discussion;
See Also;
Canceling a Partially Entered Statement;
Problem;
Solution;
Discussion;
Repeating and Editing SQL Statements;
Problem;
Solution;
Discussion;
Using Auto-Completion for Database and Table Names;
Problem;
Solution;
Discussion;
Telling mysql to Read Statements from a File;
Problem;
Solution;
Discussion;
Telling mysql to Read Statements from Other Programs;
Problem;
Solution;
Discussion;
See Also;
Entering an SQL One-Liner;
Problem;
Solution;
Discussion;
Using Copy and Paste as a mysql Input Source;
Problem;
Solution;
Discussion;
Preventing Query Output from Scrolling off the Screen;
Problem;
Solution;
Discussion;
Sending Query Output to a File or to a Program;
Problem;
Solution;
Discussion;
Selecting Tabular or Tab-Delimited Query Output Format;
Problem;
Solution;
Discussion;
Specifying Arbitrary Output Column Delimiters;
Problem;
Solution;
Discussion;
See Also;
Producing HTML or XML Output;
Problem;
Solution;
Discussion;
See Also;
Suppressing Column Headings in Query Output;
Problem;
Solution;
Discussion;
Making Long Output Lines More Readable;
Problem;
Solution;
Discussion;
Controlling mysql’s Verbosity Level;
Problem;
Solution;
Discussion;
Logging Interactive mysql Sessions;
Problem;
Solution;
Discussion;
Creating mysql Scripts from Previously Executed Statements;
Problem;
Solution;
Discussion;
Using User-Defined Variables in SQL Statements;
Problem;
Solution;
Discussion;
Numbering Query Output Lines;
Problem;
Solution;
Discussion;
Using mysql as a Calculator;
Problem;
Solution;
Discussion;
Using mysql in Shell Scripts;
Problem;
Solution;
Discussion;
Writing shell scripts under Unix;
Writing shell scripts under Windows;
Chapter 2: Writing MySQL-Based Programs;
Introduction;
Assumptions;
MySQL Client API Architecture;
Connecting, Selecting a Database, and Disconnecting;
Problem;
Solution;
Discussion;
Perl;
Ruby;
PHP;
Python;
Java;
Checking for Errors;
Problem;
Solution;
Discussion;
Perl;
Ruby;
PHP;
Python;
Java;
Writing Library Files;
Problem;
Solution;
Discussion;
Choosing a library file installation location;
Setting library file access privileges;
Perl;
Ruby;
PHP;
Python;
Java;
Issuing Statements and Retrieving Results;
Problem;
Solution;
Discussion;
SQL statement categories;
Perl;
Ruby;
PHP;
Python;
Java;
Handling Special Characters and NULL Values in Statements;
Problem;
Solution;
Discussion;
Using placeholders;
Using a quoting function;
Perl;
Ruby;
PHP;
Python;
Java;
Handling Special Characters in Identifiers;
Problem;
Solution;
Discussion;
Identifying NULL Values in Result Sets;
Problem;
Solution;
Discussion;
Perl;
Ruby;
PHP;
Python;
Java;
Techniques for Obtaining Connection Parameters;
Problem;
Solution;
Discussion;
Getting parameters from the command line;
Getting parameters from option files;
Conclusion and Words of Advice;
Chapter 3: Selecting Data from Tables;
Introduction;
Specifying Which Columns to Select;
Problem;
Solution;
Discussion;
Specifying Which Rows to Select;
Problem;
Solution;
Discussion;
Giving Better Names to Query Result Columns;
Problem;
Solution;
Discussion;
Using Column Aliases to Make Programs Easier to Write;
Problem;
Solution;
Discussion;
See Also;
Combining Columns to Construct Composite Values;
Problem;
Solution;
Discussion;
WHERE Clauses and Column Aliases;
Problem;
Solution;
Discussion;
Debugging Comparison Expressions;
Problem;
Solution;
Discussion;
Removing Duplicate Rows;
Problem;
Solution;
Discussion;
See Also;
Working with NULL Values;
Problem;
Solution;
Discussion;
See Also;
Writing Comparisons Involving NULL in Programs;
Problem;
Solution;
Discussion;
Sorting a Result Set;
Problem;
Solution;
Discussion;
Using Views to Simplify Table Access;
Problem;
Solution;
Discussion;
Selecting Data from More Than One Table;
Problem;
Solution;
Discussion;
Selecting Rows from the Beginning or End of a Result Set;
Problem;
Solution;
Discussion;
See Also;
Selecting Rows from the Middle of a Result Set;
Problem;
Solution;
Discussion;
Choosing Appropriate LIMIT Values;
Problem;
Solution;
Discussion;
What to Do When LIMIT Requires the Wrong Sort Order;
Problem;
Solution;
Discussion;
Calculating LIMIT Values from Expressions;
Problem;
Solution;
Discussion;
Chapter 4: Table Management;
Introduction;
Cloning a Table;
Problem;
Solution;
Discussion;
Saving a Query Result in a Table;
Problem;
Solution;
Discussion;
Creating Temporary Tables;
Problem;
Solution;
Discussion;
Checking or Changing a Table’s Storage Engine;
Problem;
Solution;
Discussion;
See Also;
Generating Unique Table Names;
Problem;
Solution;
Discussion;
Chapter 5: Working with Strings;
Introduction;
String Properties;
Choosing a String Data Type;
Problem;
Solution;
Discussion;
Setting the Client Connection Character Set Properly;
Problem;
Solution;
Discussion;
Writing String Literals;
Problem;
Solution;
Discussion;
See Also;
Checking a String’s Character Set or Collation;
Problem;
Solution;
Discussion;
Changing a String’s Character Set or Collation;
Problem;
Solution;
Discussion;
Converting the Lettercase of a String;
Problem;
Solution;
Discussion;
Converting the Lettercase of a Stubborn String;
Problem;
Solution;
Discussion;
Controlling Case Sensitivity in String Comparisons;
Problem;
Solution;
Discussion;
Pattern Matching with SQL Patterns;
Problem;
Solution;
Discussion;
Pattern Matching with Regular Expressions;
Problem;
Solution;
Discussion;
Controlling Case Sensitivity in Pattern Matching;
Problem;
Solution;
Discussion;
Breaking Apart or Combining Strings;
Problem;
Solution;
Discussion;
Searching for Substrings;
Problem;
Solution;
Discussion;
Using FULLTEXT Searches;
Problem;
Solution;
Discussion;
See Also;
Using a FULLTEXT Search with Short Words;
Problem;
Solution;
Discussion;
Requiring or Excluding FULLTEXT Search Words;
Problem;
Solution;
Discussion;
Performing Phrase Searches with a FULLTEXT Index;
Problem;
Solution;
Discussion;
Chapter 6: Working with Dates and Times;
Introduction;
Choosing a Temporal Data Type;
Problem;
Solution;
Discussion;
Changing MySQL’s Date Format;
Problem;
Solution;
Discussion;
Setting the Client Time Zone;
Problem;
Solution;
Discussion;
See Also;
Determining the Current Date or Time;
Problem;
Solution;
Discussion;
Using TIMESTAMP to Track Row Modification Times;
Problem;
Solution;
Discussion;
See Also;
Extracting Parts of Dates or Times;
Problem;
Solution;
Discussion;
Decomposing dates or times using component-extraction functions;
Decomposing dates or times using formatting functions;
Decomposing dates or times using string functions;
Synthesizing Dates or Times from Component Values;
Problem;
Solution;
Discussion;
Converting Between Temporal Data Types and Basic Units;
Problem;
Solution;
Discussion;
Converting between times and seconds;
Converting between dates and days;
Converting between date-and-time values and seconds;
Calculating the Interval Between Two Dates or Times;
Problem;
Solution;
Discussion;
Calculating intervals with temporal-difference functions;
Calculating intervals using basic units;
Time interval calculation using basic units;
Date or date-and-time interval calculation using basic units;
Adding Date or Time Values;
Problem;
Solution;
Discussion;
Adding temporal values using temporal-addition functions or operators;
Adding temporal values using basic units;
Calculating Ages;
Problem;
Solution;
Discussion;
Shifting a Date-and-Time Value to a Different Time Zone;
Problem;
Solution;
Discussion;
Finding the First Day, Last Day, or Length of a Month;
Problem;
Solution;
Discussion;
See Also;
Calculating Dates by Substring Replacement;
Problem;
Solution;
Discussion;
Finding the Day of the Week for a Date;
Problem;
Solution;
Discussion;
Finding Dates for Any Weekday of a Given Week;
Problem;
Solution;
Discussion;
Performing Leap Year Calculations;
Problem;
Solution;
Discussion;
Determining whether a date occurs in a leap year;
Using leap year tests for year-length calculations;
Using leap year tests for month-length calculations;
Canonizing Not-Quite-ISO Date Strings;
Problem;
Solution;
Discussion;
See Also;
Treating Dates or Times as Numbers;
Problem;
Solution;
Discussion;
Forcing MySQL to Treat Strings as Temporal Values;
Problem;
Solution;
Discussion;
Selecting Rows Based on Their Temporal Characteristics;
Problem;
Solution;
Discussion;
Comparing dates to one another;
Comparing times to one another;
Comparing dates to calendar days;
Chapter 7: Sorting Query Results;
Introduction;
Using ORDER BY to Sort Query Results;
Problem;
Solution;
Discussion;
Using Expressions for Sorting;
Problem;
Solution;
Discussion;
Displaying One Set of Values While Sorting by Another;
Problem;
Solution;
Discussion;
Controlling Case Sensitivity of String Sorts;
Problem;
Solution;
Discussion;
Date-Based Sorting;
Problem;
Solution;
Discussion;
Sorting by Calendar Day;
Problem;
Solution;
Discussion;
Sorting by Day of Week;
Problem;
Solution;
Discussion;
Sorting by Time of Day;
Problem;
Solution;
Discussion;
Sorting Using Substrings of Column Values;
Problem;
Solution;
Discussion;
Sorting by Fixed-Length Substrings;
Problem;
Solution;
Discussion;
Sorting by Variable-Length Substrings;
Problem;
Solution;
Discussion;
Sorting Hostnames in Domain Order;
Problem;
Solution;
Discussion;
Sorting Dotted-Quad IP Values in Numeric Order;
Problem;
Solution;
Discussion;
Floating Values to the Head or Tail of the Sort Order;
Problem;
Solution;
Discussion;
See Also;
Sorting in User-Defined Orders;
Problem;
Solution;
Discussion;
Sorting ENUM Values;
Problem;
Solution;
Discussion;
Chapter 8: Generating Summaries;
Introduction;
Summarizing with COUNT⁠(⁠ ⁠ ⁠);
Problem;
Solution;
Discussion;
See Also;
Summarizing with MIN⁠(⁠ ⁠ ⁠) and MAX⁠(⁠ ⁠ ⁠);
Problem;
Solution;
Discussion;
Summarizing with SUM⁠(⁠ ⁠ ⁠) and AVG⁠(⁠ ⁠ ⁠);
Problem;
Solution;
Discussion;
See Also;
Using DISTINCT to Eliminate Duplicates;
Problem;
Solution;
Discussion;
Finding Values Associated with Minimum and Maximum Values;
Problem;
Solution;
Discussion;
See Also;
Controlling String Case Sensitivity for MIN⁠(⁠ ⁠ ⁠) and MAX⁠(⁠ ⁠ ⁠);
Problem;
Solution;
Discussion;
Dividing a Summary into Subgroups;
Problem;
Solution;
Discussion;
Summaries and NULL Values;
Problem;
Solution;
Discussion;
Selecting Only Groups with Certain Characteristics;
Problem;
Solution;
Discussion;
Using Counts to Determine Whether Values Are Unique;
Problem;
Solution;
Discussion;
Grouping by Expression Results;
Problem;
Solution;
Discussion;
Categorizing Noncategorical Data;
Problem;
Solution;
Discussion;
Controlling Summary Display Order;
Problem;
Solution;
Discussion;
Finding Smallest or Largest Summary Values;
Problem;
Solution;
Discussion;
Date-Based Summaries;
Problem;
Solution;
Discussion;
Working with Per-Group and Overall Summary Values Simultaneously;
Problem;
Solution;
Discussion;
Generating a Report That Includes a Summary and a List;
Problem;
Solution;
Discussion;
Chapter 9: Obtaining and Using Metadata;
Introduction;
Obtaining the Number of Rows Affected by a Statement;
Problem;
Solution;
Discussion;
Perl;
Ruby;
PHP;
Python;
Java;
Obtaining Result Set Metadata;
Problem;
Solution;
Discussion;
Perl;
Ruby;
PHP;
Python;
Java;
Determining Whether a Statement Produced a Result Set;
Problem;
Solution;
Discussion;
Using Metadata to Format Query Output;
Problem;
Solution;
Discussion;
See Also;
Listing or Checking Existence of Databases or Tables;
Problem;
Solution;
Discussion;
Accessing Table Column Definitions;
Problem;
Solution;
Discussion;
Using INFORMATION_SCHEMA to get table structure;
Using SHOW COLUMNS to get table structure;
Using CREATE TABLE to get table structure;
Getting ENUM and SET Column Information;
Problem;
Solution;
Discussion;
Using Table Structure Information in Applications;
Problem;
Solution;
Discussion;
Displaying column lists;
Interactive record editing;
Mapping column definitions onto web page elements;
Adding elements to ENUM or SET column definitions;
Selecting all except certain columns;
Getting Server Metadata;
Problem;
Solution;
Discussion;
Writing Applications That Adapt to the MySQL Server Version;
Problem;
Solution;
Discussion;
Determining the Default Database;
Problem;
Solution;
Discussion;
Monitoring the MySQL Server;
Problem;
Solution;
Discussion;
Determining Which Storage Engines the Server Supports;
Problem;
Solution;
Discussion;
Chapter 10: Importing and Exporting Data;
Introduction;
General Import and Export Issues;
File Formats;
Notes on Invoking Shell Commands;
Importing Data with LOAD DATA and mysqlimport;
Problem;
Solution;
Discussion;
Specifying the Datafile Location;
Problem;
Solution;
Discussion;
Specifying the Structure of the Datafile;
Problem;
Solution;
Discussion;
Dealing with Quotes and Special Characters;
Problem;
Solution;
Discussion;
Importing CSV Files;
Problem;
Solution;
Discussion;
Reading Files from Different Operating Systems;
Problem;
Solution;
Discussion;
Handling Duplicate Key Values;
Problem;
Solution;
Discussion;
Obtaining Diagnostics About Bad Input Data;
Problem;
Solution;
Discussion;
Skipping Datafile Lines;
Problem;
Solution;
Discussion;
Specifying Input Column Order;
Problem;
Solution;
Discussion;
Preprocessing Input Values Before Inserting Them;
Problem;
Solution;
Discussion;
Ignoring Datafile Columns;
Problem;
Solution;
Discussion;
See Also;
Exporting Query Results from MySQL;
Problem;
Solution;
Discussion;
Exporting with the SELECT ... INTO OUTFILE statement;
Using the mysql client to export data;
See Also;
Exporting Tables as Text Files;
Problem;
Solution;
Discussion;
Exporting Table Contents or Definitions in SQL Format;
Problem;
Solution;
Discussion;
Copying Tables or Databases to Another Server;
Problem;
Solution;
Discussion;
Writing Your Own Export Programs;
Problem;
Solution;
Discussion;
Converting Datafiles from One Format to Another;
Problem;
Solution;
Discussion;
Extracting and Rearranging Datafile Columns;
Problem;
Solution;
Discussion;
Using the SQL Mode to Control Bad Input Data Handling;
Problem;
Solution;
Discussion;
Validating and Transforming Data;
Problem;
Solution;
Discussion;
Writing an input-processing loop;
Putting common tests in libraries;
Using Pattern Matching to Validate Data;
Problem;
Solution;
Discussion;
Using Patterns to Match Broad Content Types;
Problem;
Solution;
Discussion;
Using Patterns to Match Numeric Values;
Problem;
Solution;
Discussion;
Using Patterns to Match Dates or Times;
Problem;
Solution;
Discussion;
See Also;
Using Patterns to Match Email Addresses or URLs;
Problem;
Solution;
Discussion;
Using Table Metadata to Validate Data;
Problem;
Solution;
Discussion;
Using a Lookup Table to Validate Data;
Problem;
Solution;
Discussion;
Issue individual statements;
Construct a hash from the entire lookup table;
Use a hash as a cache of already-seen lookup values;
Converting Two-Digit Year Values to Four-Digit Form;
Problem;
Solution;
Discussion;
Performing Validity Checking on Date or Time Subparts;
Problem;
Solution;
Discussion;
Writing Date-Processing Utilities;
Problem;
Solution;
Discussion;
Using Dates with Missing Components;
Problem;
Solution;
Discussion;
Importing Non-ISO Date Values;
Problem;
Solution;
Discussion;
Exporting Dates Using Non-ISO Formats;
Problem;
Solution;
Discussion;
Importing and Exporting NULL Values;
Problem;
Solution;
Discussion;
Guessing Table Structure from a Datafile;
Problem;
Solution;
Discussion;
Exchanging Data Between MySQL and Microsoft Access;
Problem;
Solution;
Discussion;
Exchanging Data Between MySQL and Microsoft Excel;
Problem;
Solution;
Discussion;
Exporting Query Results as XML;
Problem;
Solution;
Discussion;
Importing XML into MySQL;
Problem;
Solution;
Discussion;
Epilogue;
Chapter 11: Generating and Using Sequences;
Introduction;
Creating a Sequence Column and Generating Sequence Values;
Problem;
Solution;
Discussion;
Choosing the Data Type for a Sequence Column;
Problem;
Solution;
Discussion;
The Effect of Row Deletions on Sequence Generation;
Problem;
Solution;
Discussion;
Retrieving Sequence Values;
Problem;
Solution;
Discussion;
Using LAST_INSERT_ID⁠(⁠ ⁠ ⁠) to obtain AUTO_INCREMENT values;
Using API-specific methods to obtain AUTO_INCREMENT values;
Server-side and client-side sequence value retrieval compared;
Renumbering an Existing Sequence;
Problem;
Solution;
Discussion;
Extending the Range of a Sequence Column;
Problem;
Solution;
Discussion;
Reusing Values at the Top of a Sequence;
Problem;
Solution;
Discussion;
Ensuring That Rows Are Renumbered in a Particular Order;
Problem;
Solution;
Discussion;
Starting a Sequence at a Particular Value;
Problem;
Solution;
Discussion;
Sequencing an Unsequenced Table;
Problem;
Solution;
Discussion;
Using an AUTO_INCREMENT Column to Create Multiple Sequences;
Problem;
Solution;
Discussion;
Managing Multiple Simultaneous AUTO_INCREMENT Values;
Problem;
Solution;
Discussion;
Using AUTO_INCREMENT Values to Relate Tables;
Problem;
Solution;
Discussion;
Using Sequence Generators as Counters;
Problem;
Solution;
Discussion;
See Also;
Generating Repeating Sequences;
Problem;
Solution;
Discussion;
Numbering Query Output Rows Sequentially;
Problem;
Solution;
Discussion;
See Also;
Chapter 12: Using Multiple Tables;
Introduction;
Finding Rows in One Table That Match Rows in Another;
Problem;
Solution;
Discussion;
Finding Rows with No Match in Another Table;
Problem;
Solution;
Discussion;
See Also;
Comparing a Table to Itself;
Problem;
Solution;
Discussion;
Producing Master-Detail Lists and Summaries;
Problem;
Solution;
Discussion;
Enumerating a Many-to-Many Relationship;
Problem;
Solution;
Discussion;
Finding Rows Containing Per-Group Minimum or Maximum Values;
Problem;
Solution;
Discussion;
See Also;
Computing Team Standings;
Problem;
Solution;
Discussion;
Using a Join to Fill or Identify Holes in a List;
Problem;
Solution;
Discussion;
Calculating Successive-Row Differences;
Problem;
Solution;
Discussion;
Finding Cumulative Sums and Running Averages;
Problem;
Solution;
Discussion;
Using a Join to Control Query Output Order;
Problem;
Solution;
Discussion;
Combining Several Result Sets in a Single Query;
Problem;
Solution;
Discussion;
Identifying and Removing Mismatched or Unattached Rows;
Problem;
Solution;
Discussion;
Performing a Join Between Tables in Different Databases;
Problem;
Solution;
Discussion;
Using Different MySQL Servers Simultaneously;
Problem;
Solution;
Discussion;
Referring to Join Output Column Names in Programs;
Problem;
Solution;
Discussion;
Chapter 13: Statistical Techniques;
Introduction;
Calculating Descriptive Statistics;
Problem;
Solution;
Discussion;
Per-Group Descriptive Statistics;
Problem;
Solution;
Discussion;
Generating Frequency Distributions;
Problem;
Solution;
Discussion;
Counting Missing Values;
Problem;
Solution;
Discussion;
Calculating Linear Regressions or Correlation Coefficients;
Problem;
Solution;
Discussion;
Generating Random Numbers;
Problem;
Solution;
Discussion;
Randomizing a Set of Rows;
Problem;
Solution;
Discussion;
Selecting Random Items from a Set of Rows;
Problem;
Solution;
Discussion;
Assigning Ranks;
Problem;
Solution;
Discussion;
Chapter 14: Handling Duplicates;
Introduction;
Preventing Duplicates from Occurring in a Table;
Problem;
Solution;
Discussion;
See Also;
Dealing with Duplicates When Loading Rows into a Table;
Problem;
Solution;
Discussion;
See Also;
Counting and Identifying Duplicates;
Problem;
Solution;
Discussion;
Eliminating Duplicates from a Table;
Problem;
Solution;
Discussion;
Removing duplicates using table replacement;
Removing duplicates by adding an index;
Removing duplicates of a particular row;
Eliminating Duplicates from a Self-Join Result;
Problem;
Solution;
Discussion;
Chapter 15: Performing Transactions;
Introduction;
Choosing a Transactional Storage Engine;
Problem;
Solution;
Discussion;
Performing Transactions Using SQL;
Problem;
Solution;
Discussion;
Performing Transactions from Within Programs;
Problem;
Solution;
Discussion;
Using Transactions in Perl Programs;
Problem;
Solution;
Discussion;
Using Transactions in Ruby Programs;
Problem;
Solution;
Discussion;
Using Transactions in PHP Programs;
Problem;
Solution;
Discussion;
Using Transactions in Python Programs;
Problem;
Solution;
Discussion;
Using Transactions in Java Programs;
Problem;
Solution;
Discussion;
Using Alternatives to Transactions;
Problem;
Solution;
Discussion;
Grouping statements using locks;
Rewriting statements to avoid transactions;
Chapter 16: Using Stored Routines, Triggers, and Events;
Introduction;
Creating Compound-Statement Objects;
Problem;
Solution;
Discussion;
Using a Stored Function to Encapsulate a Calculation;
Problem;
Solution;
Discussion;
Using a Stored Procedure to Return Multiple Values;
Problem;
Solution;
Discussion;
Using a Trigger to Define Dynamic Default Column Values;
Problem;
Solution;
Discussion;
Simulating TIMESTAMP Properties for Other Date and Time Types;
Problem;
Solution;
Discussion;
Using a Trigger to Log Changes to a Table;
Problem;
Solution;
Discussion;
Using Events to Schedule Database Actions;
Problem;
Solution;
Discussion;
Chapter 17: Introduction to MySQL on the Web;
Introduction;
Basic Principles of Web Page Generation;
Problem;
Solution;
Discussion;
Using Apache to Run Web Scripts;
Problem;
Solution;
Discussion;
Perl;
Ruby;
PHP;
Python;
Using Tomcat to Run Web Scripts;
Problem;
Solution;
Discussion;
Installing the mcb application;
Installing the JDBC driver;
Installing the JSTL distribution;
Writing JSP pages with JSTL;
Writing a MySQL script using JSP and JSTL;
Encoding Special Characters in Web Output;
Problem;
Solution;
Discussion;
General encoding principles;
Encoding special characters using web APIs;
Chapter 18: Incorporating Query Results into Web Pages;
Introduction;
Displaying Query Results as Paragraph Text;
Problem;
Solution;
Discussion;
See Also;
Displaying Query Results as Lists;
Problem;
Solution;
Discussion;
Ordered lists;
Unordered lists;
Definition lists;
Unmarked lists;
Nested lists;
See Also;
Displaying Query Results as Tables;
Problem;
Solution;
Discussion;
See Also;
Displaying Query Results as Hyperlinks;
Problem;
Solution;
Discussion;
Creating a Navigation Index from Database Content;
Problem;
Solution;
Discussion;
Creating a single-page navigation index;
Creating a multiple-page navigation index;
See Also;
Storing Images or Other Binary Data;
Problem;
Solution;
Discussion;
Storing images with LOAD_FILE⁠(⁠ ⁠ ⁠);
Storing images using a script;
See Also;
Retrieving Images or Other Binary Data;
Problem;
Solution;
Discussion;
Serving Banner Ads;
Problem;
Solution;
Discussion;
Serving Query Results for Download;
Problem;
Solution;
Discussion;
Using a Template System to Generate Web Pages;
Problem;
Solution;
Discussion;
Using page template for web page generation in Ruby;
Using Smarty for web page generation in PHP;
Chapter 19: Processing Web Input with MySQL;
Introduction;
Writing Scripts That Generate Web Forms;
Problem;
Solution;
Discussion;
Perl;
Ruby;
PHP;
Python;
Java;
See Also;
Creating Single-Pick Form Elements from Database Content;
Problem;
Solution;
Discussion;
Creating Multiple-Pick Form Elements from Database Content;
Problem;
Solution;
Discussion;
Loading a Database Record into a Form;
Problem;
Solution;
Discussion;
Collecting Web Input;
Problem;
Solution;
Discussion;
Web input extraction conventions;
Validating Web Input;
Problem;
Solution;
Discussion;
Storing Web Input in a Database;
Problem;
Solution;
Discussion;
See Also;
Processing File Uploads;
Problem;
Solution;
Discussion;
Uploads in Perl;
Uploads in PHP;
Uploads in Python;
Performing Searches and Presenting the Results;
Problem;
Solution;
Discussion;
Generating Previous-Page and Next-Page Links;
Problem;
Solution;
Discussion;
Paged displays with previous-page and next-page links;
Paged displays with links to each page;
Generating Click to Sort Table Headings;
Problem;
Solution;
Discussion;
Web Page Access Counting;
Problem;
Solution;
Discussion;
Web Page Access Logging;
Problem;
Solution;
Discussion;
Using MySQL for Apache Logging;
Problem;
Solution;
Discussion;
Setting up database logging;
Analyzing the logfile;
Other logging issues;
Chapter 20: Using MySQL-Based Web Session Management;
Introduction;
Session Management Issues;
Using MySQL-Based Sessions in Perl Applications;
Problem;
Solution;
Discussion;
Installing Apache::Session;
The Apache::Session interface;
A sample application;
Session expiration;
Using MySQL-Based Storage in Ruby Applications;
Problem;
Solution;
Discussion;
Using MySQL-Based Storage with the PHP Session Manager;
Problem;
Solution;
Discussion;
The PHP session management interface;
Specifying a user-defined storage module;
Using MySQL for Session-Backing Store with Tomcat;
Problem;
Solution;
Discussion;
The Servlet and JSP Session Interface;
A sample JSP session application;
Telling Tomcat to save session records in MySQL;
Session expiration in Tomcat;
Session tracking in Tomcat;
Obtaining MySQL Software;
Obtaining Sample Source Code and Data;
Obtaining MySQL and Related Software;
MySQL;
Perl Support;
Ruby Support;
PHP Support;
Python Support;
Java Support;
Web Servers;
Executing Programs from the Command Line;
Setting Environment Variables;
Setting the PATH Variable on Unix;
Setting the PATH Variable on Windows;
Executing Programs;
Executing Perl, Ruby, PHP, or Python Scripts;
Compiling and Executing Java Programs;
JSP and Tomcat Primer;
Servlet and JavaServer Pages Overview;
JSP Pages: An Alternative to Servlets;
Custom Actions and Tag Libraries;
Setting Up a Tomcat Server;
Installing a Tomcat Distribution;
Starting and Stopping Tomcat;
Tomcat’s Directory Structure;
Application Directories;
Configuration and Control Directories;
Class Directories;
Operational Directories;
Restarting Applications Without Restarting Tomcat;
Web Application Structure;
Elements of JSP Pages;
Scripting Elements;
JSP Directives;
Action Elements;
Using a Tag Library;
Implicit JSP Objects;
Levels of Scope in JSP Pages;
References;
MySQL Resources;
Perl Resources;
Ruby Resources;
PHP Resources;
Python Resources;
Java Resources;
Other Resources;
Colophon;

Customer Reviews

Most Helpful Customer Reviews

See All Customer Reviews

MySQL Cookbook 5 out of 5 based on 0 ratings. 1 reviews.
Guest More than 1 year ago
MySQL Cookbook is not a beginner's 'how-to' instruction manual, but a comprehensive problem solving tool for constructing, optimizing and troubleshooting a wide variety of MySQL queries. A must have for anyone who gets stumped from time to time with an odd situation, or who wants to tweak their code.