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

Temporarily Out of Stock Online

Eligible for FREE SHIPPING

Overview

MySQL Cookbook by Paul DuBois

Along with MySQL's popularity has come a flood of questions about solving specific problems, and that's where this Cookbook is essential. Designed as a handy resource when you need quick solutions or techniques, the book offers dozens of short, focused pieces of code and hundreds of worked-out examples for programmers of all levels who don't have the time (or expertise) to solve MySQL problems from scratch.

The new edition covers MySQL 5.0 and its powerful new features, as well as the older but still widespread MySQL 4.1. One major emphasis of this book is how to use SQL to formulate queries for particular kinds of questions, using the mysql client program included in MySQL distributions. The other major emphasis is how to write programs that interact with the MySQL server through an API. You'll find plenty of examples using several language APIs in multiple scenarios and situations, including the use of Ruby to retrieve and format data. There are also many new examples for using Perl, PHP, Python, and Java as well.

Other recipes in the book teach you to:

  • Access data from multiple tables at the same time
  • Use SQL to select, sort, and summarize rows
  • Find matches or mismatches between rows in two tables
  • Determine intervals between dates or times, including age calculations
  • Store images into MySQL and retrieve them for display in web pages
  • Get LOAD DATA to read your data files properly or find which values in the file are invalid
  • Use strict mode to prevent entry of bad data into your database
  • Copy a table or a database to another server
  • Generate sequence numbers to use as unique row identifiers
  • Create database events that execute according to a schedule
  • And a lot more

MySQL Cookbook doesn't attempt to develop full-fledged, complex applications. Instead, it's intended to assist you in developing applications yourself by helping you get past problems that have you stumped.

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.