Index
    Preface
      What This Book Is About
      What You Need to Know
      How This Book Is Organized
      How to Use This Book
      Conventions Used in This Book
      Using Code Examples
      How to Contact Us
      Web Site and Code Examples
      Acknowledgments
      Chapter 1.  Database Applications and the Web
      Section 1.1.  The Web
      Section 1.2.  Three-Tier Architectures
      Chapter 2.  The PHP Scripting Language
      Section 2.1.  Introducing PHP
      Section 2.2.  Conditions and Branches
      Section 2.3.  Loops
      Section 2.4.  Functions
      Section 2.5.  Working with Types
      Section 2.6.  User-Defined Functions
      Section 2.7.  A Working Example
      Chapter 3.  Arrays, Strings, and Advanced Data Manipulation in PHP
      Section 3.1.  Arrays
      Section 3.2.  Strings
      Section 3.3.  Regular Expressions
      Section 3.4.  Dates and Times
      Section 3.5.  Integers and Floats
      Chapter 4.  Introduction to Object-Oriented Programming with PHP 5
      Section 4.1.  Classes and Objects
      Section 4.2.  Inheritance
      Section 4.3.  Throwing and Catching Exceptions
      Chapter 5.  SQL and MySQL
      Section 5.1.  Database Basics
      Section 5.2.  MySQL Command Interpreter
      Section 5.3.  Managing Databases and Tables
      Section 5.4.  Inserting, Updating, and Deleting Data
      Section 5.5.  Querying with SQL SELECT
      Section 5.6.  Join Queries
      Section 5.7.  Case Study: Adding a New Wine
      Chapter 6.  Querying Web Databases
      Section 6.1.  Querying a MySQL Database Using PHP
      Section 6.2.  Processing User Input
      Section 6.3.  MySQL Function Reference
      Chapter 7.  PEAR
      Section 7.1.  Overview
      Section 7.2.  Core Components
      Section 7.3.  Packages
      Chapter 8.  Writing to Web Databases
      Section 8.1.  Database Inserts, Updates, and Deletes
      Section 8.2.  Issues in Writing Data to Databases
      Chapter 9.  Validation with PHP and JavaScript
      Section 9.1.  Validation and Error Reporting Principles
      Section 9.2.  Server-Side Validation with PHP
      Section 9.3.  JavaScript and Client-Side Validation
      Chapter 10.  Sessions
      Section 10.1.  Introducing Session Management
      Section 10.2.  PHP Session Management
      Section 10.3.  Case Study: Using Sessions in Validation
      Section 10.4.  When to Use Sessions
      Section 10.5.  PHP Session API and Configuration
      Chapter 11.  Authentication and Security
      Section 11.1.  HTTP Authentication
      Section 11.2.  HTTP Authentication with PHP
      Section 11.3.  Form-Based Authentication
      Section 11.4.  Protecting Data on the Web
      Chapter 12.  Errors, Debugging, and Deployment
      Section 12.1.  Errors
      Section 12.2.  Common Programming Errors
      Section 12.3.  Custom Error Handlers
      Chapter 13.  Reporting
      Section 13.1.  Creating a Report
      Section 13.2.  Producing PDF
      Section 13.3.  PDF-PHP Reference
      Chapter 14.  Advanced Features of Object-Oriented Programming in PHP 5
      Section 14.1.  Working with Class Hierarchies
      Section 14.2.  Class Type Hints
      Section 14.3.  Abstract Classes and Interfaces
      Section 14.4.  Freight Calculator Example
      Chapter 15.  Advanced SQL
      Section 15.1.  Exploring with SHOW
      Section 15.2.  Advanced Querying
      Section 15.3.  Manipulating Data and Databases
      Section 15.4.  Functions
      Section 15.5.  Automating Querying
      Section 15.6.  Table Types
      Section 15.7.  Backup and Recovery
      Section 15.8.  Managing Users and Privileges
      Section 15.9.  Tuning MySQL
      Chapter 16.  Hugh and Dave's Online Wines:A Case Study
      Section 16.1.  Functional and System Requirements
      Section 16.2.  Application Overview
      Section 16.3.  Common Components
      Chapter 17.  Managing Customers
      Section 17.1.  Code Overview
      Section 17.2.  Customer Validation
      Section 17.3.  The Customer Form
      Chapter 18.  The Shopping Cart
      Section 18.1.  Code Overview
      Section 18.2.  The Winestore Home Page
      Section 18.3.  The Shopping Cart Implementation
      Chapter 19.  Ordering and Shipping at the Online Winestore
      Section 19.1.  Code Overview
      Section 19.2.  Credit Card and Shipping Instructions
      Section 19.3.  Finalizing Orders
      Section 19.4.  HTML and Email Receipts
      Chapter 20.  Searching and Authentication in the Online Winestore
      Section 20.1.  Code Overview
      Section 20.2.  Searching and Browsing
      Section 20.3.  Authentication
      Appendix A.  Linux Installation Guide
      Section A.1.  Finding Out What's Installed
      Section A.2.  Installation Overview
      Section A.3.  Installing MySQL
      Section A.4.  Installing Apache
      Section A.5.  Installing PHP
      Section A.6.  What's Needed for This Book
      Appendix B.  Microsoft Windows Installation Guide
      Section B.1.  Installation Overview
      Section B.2.  Installing with EasyPHP
      Section B.3.  What's Needed for This Book
      Appendix C.  Mac OS X Installation Guide
      Section C.1.  Getting Started
      Section C.2.  Installing MySQL
      Section C.3.  Setting Up Apache and PHP
      Section C.4.  What's Needed for This Book
      Appendix D.  Web Protocols
      Section D.1.  Network Basics
      Section D.2.  Hypertext Transfer Protocol
      Appendix E.  Modeling and Designing Relational Databases
      Section E.1.  The Relational Model
      Section E.2.  Entity-Relationship Modeling
      Appendix F.  Managing Sessions in theDatabase Tier
      Section F.1.  Using a Database to Keep State
      Section F.2.  PHP Session Management
      Section F.3.  MySQL Session Store
      Appendix G.  Resources
      Section G.1.  Client Tier Resources
      Section G.2.  Middle-Tier Resources
      Section G.3.  Database Tier Resources
      Section G.4.  Security and Cryptography Resources
      Appendix H.  The Improved MySQL Library
      Section H.1.  New Features
      Section H.2.  Getting Started
      Section H.3.  Using the New Features
    Colophon
    Copyright



 
Previous Section  < Day Day Up >  Next Section

Chapter 15. Advanced SQL

The SQL shown in Chapter 5 was sufficient for the scripts in earlier chapters and for the winestore application that's described in the next five chapters. However, SQL is extremely powerful and provides many other capabilities, some of which we'll cover in this chapter. Examples of these useful features include advanced join types, nested queries, and aliases.

Many of the features shown in this chapter are specific to MySQL's version of SQL. For example, MySQL's functions are useful tools for working with strings, dates and times, and math. Also, we'll show some ways to tune MySQL in order to improve application performance. It's important to know how to choose and design indexes for fast querying, and how to use MySQL's query cache for fast results.

We cover the following topics in this chapter:

  • Exploring your database, tables, indexes, and performance with SHOW

  • More on SELECT queries, including advanced join types, aliases, nested queries, user variables, and the limitations of MySQL

  • More on manipulating data and databases, including finding out about tables and databases, creating tables with queries, altering tables, more on the UPDATE and DELETE statements, and bulk loading and exporting data

  • Functions and operators in SQL and MySQL

  • Automatically running queries

  • MyISAM, InnoDB, and Heap table types

  • Backup and recovery, and transferring data between database servers

  • Managing database server users and privileges, and creating users for web database applications

  • Basic tuning of MySQL, including index design, using the query cache, and, miscellaneous tips for speed

This chapter contains advanced concepts. If you're keen to continue working with PHP and MySQL and study our example web database application, you can jump to Chapter 16. However, if you do skip this chapter, return later when you're ready to know more about how to improve your application. If you are proceeding with this chapter, refresh your memory about our winestore database that's discussed in Chapter 5 as it's used in most examples in this chapter.

    Previous Section  < Day Day Up >  Next Section







    Copyright © 2010 | Domen maybe sale - bye this domen