Producing HTML Output
1.24.1 Problem
You'd like to turn a query result into HTML.
1.24.2 Solution
mysql can do that for you.
1.24.3 Discussion
mysql generates result set output as HTML tables if you use -H (or --html) option. This gives you a quick way to produce sample output for inclusion into a web page that shows what the result of a query looks like.[8] Here's an example that shows the difference between tabular format and HTML table output (a few line breaks have been added to the HTML output to make it easier to read):
[8] I'm referring to writing static HTML pages here. If you're writing a script that produces web pages on the fly, there are better ways to generate HTML output from a query. For more information on writing web scripts, see Chapter 16.
% mysql -e "SELECT * FROM limbs WHERE legs=0" cookbook +------------+------+------+ | thing | legs | arms | +------------+------+------+ | squid | 0 | 10 | | octopus | 0 | 8 | | fish | 0 | 0 | | phonograph | 0 | 1 | +------------+------+------+ % mysql -H -e "SELECT * FROM limbs WHERE legs=0" cookbook
| thing | legs | arms |
|---|---|---|
| squid | 0 | 10 |
| octopus | 0 | 8 |
| fish | 0 | 0 |
| phonograph | 0 | 1 |
The first line of the table contains column headings. If you don't want a header row, see Recipe 1.26.
The -H and --html options produce output only for queries that generate a result set. No output is written for queries such as INSERT or UPDATE statements.
-H and --html may be used as of MySQL 3.22.26. (They actually were introduced in an earlier version, but the output was not quite correct.)
Using the mysql Client Program
- Introduction
- Setting Up a MySQL User Account
- Creating a Database and a Sample Table
- Starting and Terminating mysql
- Specifying Connection Parameters by Using Option Files
- Protecting Option Files
- Mixing Command-Line and Option File Parameters
- What to Do if mysql Cannot Be Found
- Setting Environment Variables
- Issuing Queries
- Selecting a Database
- Canceling a Partially Entered Query
- Repeating and Editing Queries
- Using Auto-Completion for Database and Table Names
- Using SQL Variables in Queries
- Telling mysql to Read Queries from a File
- Telling mysql to Read Queries from Other Programs
- Specifying Queries on the Command Line
- Using Copy and Paste as a mysql Input Source
- Preventing Query Output from Scrolling off the Screen
- Sending Query Output to a File or to a Program
- Selecting Tabular or Tab-Delimited Query Output Format
- Specifying Arbitrary Output Column Delimiters
- Producing HTML Output
- Producing XML Output
- Suppressing Column Headings in Query Output
- Numbering Query Output Lines
- Making Long Output Lines More Readable
- Controlling mysqls Verbosity Level
- Logging Interactive mysql Sessions
- Creating mysql Scripts from Previously Executed Queries
- Using mysql as a Calculator
- Using mysql in Shell Scripts
Writing MySQL-Based Programs
- Introduction
- Connecting to the MySQL Server, Selecting a Database, and Disconnecting
- Checking for Errors
- Writing Library Files
- Issuing Queries and Retrieving Results
- Moving Around Within a Result Set
- Using Prepared Statements and Placeholders in Queries
- Including Special Characters and NULL Values in Queries
- Handling NULL Values in Result Sets
- Writing an Object-Oriented MySQL Interface for PHP
- Ways of Obtaining Connection Parameters
- Conclusion and Words of Advice
Record Selection Techniques
- Introduction
- Specifying Which Columns to Display
- Avoiding Output Column Order Problems When Writing Programs
- Giving Names to Output Columns
- Using Column Aliases to Make Programs Easier to Write
- Combining Columns to Construct Composite Values
- Specifying Which Rows to Select
- WHERE Clauses and Column Aliases
- Displaying Comparisons to Find Out How Something Works
- Reversing or Negating Query Conditions
- Removing Duplicate Rows
- Working with NULL Values
- Negating a Condition on a Column That Contains NULL Values
- Writing Comparisons Involving NULL in Programs
- Mapping NULL Values to Other Values for Display
- Sorting a Result Set
- Selecting Records from the Beginning or End of a Result Set
- 18 Pulling a Section from the Middle of a Result Set
- Choosing Appropriate LIMIT Values
- Calculating LIMIT Values from Expressions
- What to Do When LIMIT Requires the Wrong Sort Order
- Selecting a Result Set into an Existing Table
- Creating a Destination Table on the Fly from a Result Set
- Moving Records Between Tables Safely
- Creating Temporary Tables
- Cloning a Table Exactly
- Generating Unique Table Names
Working with Strings
- Introduction
- Writing Strings That Include Quotes or Special Characters
- Preserving Trailing Spaces in String Columns
- Testing String Equality or Relative Ordering
- Decomposing or Combining Strings
- Checking Whether a String Contains a Substring
- Pattern Matching with SQL Patterns
- Pattern Matching with Regular Expressions
- Matching Pattern Metacharacters Literally
- Controlling Case Sensitivity in String Comparisons
- Controlling Case Sensitivity in Pattern Matching
- Using FULLTEXT Searches
- Using a FULLTEXT Search with Short Words
- Requiring or Excluding FULLTEXT Search Words
- Performing Phrase Searches with a FULLTEXT Index
Working with Dates and Times
- Introduction
- Changing MySQLs Date Format
- Telling MySQL How to Display Dates or Times
- Determining the Current Date or Time
- Decomposing Dates and Times Using Formatting Functions
- Decomposing Dates or Times Using Component-Extraction Functions
- Decomposing Dates or Times Using String Functions
- Synthesizing Dates or Times Using Formatting Functions
- Synthesizing Dates or Times Using Component-Extraction Functions
- Combining a Date and a Time into a Date-and-Time Value
- Converting Between Times and Seconds
- Converting Between Dates and Days
- Converting Between Date-and-Time Values and Seconds
- Adding a Temporal Interval to a Time
- Calculating Intervals Between Times
- Breaking Down Time Intervals into Components
- Adding a Temporal Interval to a Date
- Calculating Intervals Between Dates
- Canonizing Not-Quite-ISO Date Strings
- Calculating Ages
- Shifting Dates by a Known Amount
- Finding First and Last Days of Months
- Finding the Length of a Month
- Calculating One Date from Another by Substring Replacement
- Finding the Day of the Week for a Date
- Finding Dates for Days of the Current Week
- Finding Dates for Weekdays of Other Weeks
- Performing Leap Year Calculations
- Treating Dates or Times as Numbers
- Forcing MySQL to Treat Strings as Temporal Values
- Selecting Records Based on Their Temporal Characteristics
- Using TIMESTAMP Values
- Recording a Rows Last Modification Time
- Recording a Rows Creation Time
- Performing Calculations with TIMESTAMP Values
- Displaying TIMESTAMP Values in Readable Form
Sorting Query Results
- Introduction
- Using ORDER BY to Sort Query Results
- Sorting Subsets of a Table
- Sorting Expression Results
- Displaying One Set of Values While Sorting by Another
- Sorting and NULL Values
- Controlling Case Sensitivity of String Sorts
- Date-Based Sorting
- Sorting by Calendar Day
- Sorting by Day of Week
- Sorting by Time of Day
- Sorting Using Substrings of Column Values
- Sorting by Fixed-Length Substrings
- Sorting by Variable-Length Substrings
- Sorting Hostnames in Domain Order
- Sorting Dotted-Quad IP Values in Numeric Order
- Floating Specific Values to the Head or Tail of the Sort Order
- Sorting in User-Defined Orders
- Sorting ENUM Values
Generating Summaries
- Introduction
- Summarizing with COUNT( )
- Summarizing with MIN( ) and MAX( )
- Summarizing with SUM( ) and AVG( )
- Using DISTINCT to Eliminate Duplicates
- Finding Values Associated with Minimum and Maximum Values
- Controlling String Case Sensitivity for MIN( ) and MAX( )
- Dividing a Summary into Subgroups
- Summaries and NULL Values
- Selecting Only Groups with Certain Characteristics
- Determining Whether Values are Unique
- Grouping by Expression Results
- Categorizing Non-Categorical Data
- Controlling Summary Display Order
- Finding Smallest or Largest Summary Values
- Date-Based Summaries
- Working with Per-Group and Overall Summary Values Simultaneously
- Generating a Report That Includes a Summary and a List
Modifying Tables with ALTER TABLE
- Introduction
- Dropping, Adding, or Repositioning a Column
- Changing a Column Definition or Name
- The Effect of ALTER TABLE on Null and Default Value Attributes
- Changing a Columns Default Value
- Changing a Table Type
- Renaming a Table
- Adding or Dropping Indexes
- Eliminating Duplicates by Adding an Index
- Using ALTER TABLE to Normalize a Table
Obtaining and Using Metadata
- Introduction
- Obtaining the Number of Rows Affected by a Query
- Obtaining Result Set Metadata
- Determining Presence or Absence of a Result Set
- Formatting Query Results for Display
- Getting Table Structure Information
- Getting ENUM and SET Column Information
- Database-Independent Methods of Obtaining Table Information
- Applying Table Structure Information
- Listing Tables and Databases
- Testing Whether a Table Exists
- Testing Whether a Database Exists
- Getting Server Metadata
- Writing Applications That Adapt to the MySQL Server Version
- Determining the Current Database
- Determining the Current MySQL User
- Monitoring the MySQL Server
- Determining Which Table Types the Server Supports
Importing and Exporting Data
- Introduction
- Importing Data with LOAD DATA and mysqlimport
- Specifying the Datafile Location
- Specifying the Datafile Format
- Dealing with Quotes and Special Characters
- Importing CSV Files
- Reading Files from Different Operating Systems
- Handling Duplicate Index Values
- Getting LOAD DATA to Cough Up More Information
- Dont Assume LOAD DATA Knows More than It Does
- Skipping Datafile Lines
- Specifying Input Column Order
- Skipping Datafile Columns
- Exporting Query Results from MySQL
- Exporting Tables as Raw Data
- Exporting Table Contents or Definitions in SQL Format
- Copying Tables or Databases to Another Server
- Writing Your Own Export Programs
- Converting Datafiles from One Format to Another
- Extracting and Rearranging Datafile Columns
- Validating and Transforming Data
- Validation by Direct Comparison
- Validation by Pattern Matching
- Using Patterns to Match Broad Content Types
- Using Patterns to Match Numeric Values
- Using Patterns to Match Dates or Times
- Using Patterns to Match Email Addresses and URLs
- Validation Using Table Metadata
- Validation Using a Lookup Table
- Converting Two-Digit Year Values to Four-Digit Form
- Performing Validity Checking on Date or Time Subparts
- Writing Date-Processing Utilities
- Using Dates with Missing Components
- Performing Date Conversion Using SQL
- Using Temporary Tables for Data Transformation
- Dealing with NULL Values
- Guessing Table Structure from a Datafile
- A LOAD DATA Diagnostic Utility
- Exchanging Data Between MySQL and Microsoft Access
- Exchanging Data Between MySQL and Microsoft Excel
- Exchanging Data Between MySQL and FileMaker Pro
- Exporting Query Results as XML
- Importing XML into MySQL
- Epilog
Generating and Using Sequences
- Introduction
- Using AUTO_INCREMENT To Set Up a Sequence Column
- Generating Sequence Values
- Choosing the Type for a Sequence Column
- The Effect of Record Deletions on Sequence Generation
- Retrieving Sequence Values
- Determining Whether to Resequence a Column
- Extending the Range of a Sequence Column
- Renumbering an Existing Sequence
- Reusing Values at the Top of a Sequence
- Ensuring That Rows Are Renumbered in a Particular Order
- Starting a Sequence at a Particular Value
- Sequencing an Unsequenced Table
- Using an AUTO_INCREMENT Column to Create Multiple Sequences
- Managing Multiple SimultaneousAUTO_INCREMENT Values
- Using AUTO_INCREMENT Valuesto Relate Tables
- Using Single-Row Sequence Generators
- Generating Repeating Sequences
- Numbering Query Output Rows Sequentially
Using Multiple Tables
- Introduction
- Combining Rows in One Table with Rows in Another
- Performing a Join Between Tables in Different Databases
- Referring to Join Output Column Names in Programs
- Finding Rows in One Table That Match Rows in Another
- Finding Rows with No Match in Another Table
- Finding Rows Containing Per-Group Minimum or Maximum Values
- Computing Team Standings
- Producing Master-Detail Lists and Summaries
- Using a Join to Fill in Holes in a List
- Enumerating a Many-to-Many Relationship
- Comparing a Table to Itself
- Calculating Differences Between Successive Rows
- Finding Cumulative Sums and Running Averages
- Using a Join to Control Query Output Order
- Converting Subselects to Join Operations
- Selecting Records in Parallel from Multiple Tables
- Inserting Records in One Table That Include Values from Another
- Updating One Table Based on Values in Another
- Using a Join to Create a Lookup Table from Descriptive Labels
- Deleting Related Rows in Multiple Tables
- Identifying and Removing Unattached Records
- Using Different MySQL Servers Simultaneously
Statistical Techniques
- Introduction
- Calculating Descriptive Statistics
- Per-Group Descriptive Statistics
- Generating Frequency Distributions
- Counting Missing Values
- Calculating Linear Regressions or Correlation Coefficients
- Generating Random Numbers
- Randomizing a Set of Rows
- Selecting Random Items from a Set of Rows
- Assigning Ranks
Handling Duplicates
- Introduction
- Preventing Duplicates from Occurring in a Table
- Dealing with Duplicates at Record-Creation Time
- Counting and Identifying Duplicates
- Eliminating Duplicates from a Query Result
- Eliminating Duplicates from a Self-Join Result
- Eliminating Duplicates from a Table
Performing Transactions
- Introduction
- Verifying Transaction Support Requirements
- Performing Transactions Using SQL
- Performing Transactions from Within Programs
- Using Transactions in Perl Programs
- Using Transactions in PHP Programs
- Using Transactions in Python Programs
- Using Transactions in Java Programs
- Using Alternatives to Transactions
Introduction to MySQL on the Web
- Introduction
- Basic Web Page Generation
- Using Apache to Run Web Scripts
- Using Tomcat to Run Web Scripts
- Encoding Special Characters in Web Output
Incorporating Query Resultsinto Web Pages
- Introduction
- Displaying Query Results as Paragraph Text
- Displaying Query Results as Lists
- Displaying Query Results as Tables
- Displaying Query Results as Hyperlinks
- Creating a Navigation Index from Database Content
- Storing Images or Other Binary Data
- Retrieving Images or Other Binary Data
- Serving Banner Ads
- Serving Query Results for Download
Processing Web Input with MySQL
- Introduction
- Creating Forms in Scripts
- Creating Single-Pick Form Elements from Database Content
- Creating Multiple-Pick Form Elements from Database Content
- Loading a Database Record into a Form
- Collecting Web Input
- Validating Web Input
- Using Web Input to Construct Queries
- Processing File Uploads
- Performing Searches and Presenting the Results
- Generating Previous-Page and Next-Page Links
- Generating Click to Sort Table Headings
- Web Page Access Counting
- Web Page Access Logging
- Using MySQL for Apache Logging
Using MySQL-Based Web Session Management
- Introduction
- Using MySQL-Based Sessions in Perl Applications
- Using MySQL-Based Storage with the PHP Session Manager
- Using MySQL for Session BackingStore with Tomcat
Appendix A. Obtaining MySQL Software
- Appendix A. Obtaining MySQL Software
- A.1. Obtaining Sample Source Code and Data
- A.2. Obtaining MySQL and Related Software
Appendix B. JSP and Tomcat Primer
- Appendix B. JSP and Tomcat Primer
- B.1. Servlet and JavaServer Pages Overview
- B.2. Setting Up a Tomcat Server
- B.3. Web Application Structure
- B.4. Elements of JSP Pages
Appendix C. References
- Article 320 Armored Cable Type AC
- Article 332 Mineral-Insulated, Metal-Sheathed Cable Type MI
- Article 334 Nonmetallic-Sheathed Cable Types NM, NMC, and NMS
- Example No. D5(a) Multifamily Dwelling Served at 208Y/120 Volts, Three Phase
- Example No. D9 Feeder Ampacity Determination for Generator Field Control
