book

Web Database Applications with PHP and MySQL, 2nd Edition

by Hugh E. Williams, David Lane

May 2004

Intermediate to advanced

820 pages

24h 34m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Web Database Applications with PHP and MySQL, 2nd Edition
SPECIAL OFFER: Upgrade this ebook with O’Reilly
A Note Regarding Supplemental Files
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
1. Database Applications and the Web
1.1. The Web
1.2. Three-Tier Architectures
1.2.1. HTTP: the Hypertext Transfer Protocol1.2.1.1. HTTP example1.2.2. State1.2.3. Thickening the Client in the Three-Tier Model1.2.4. The Middle Tier1.2.4.1. Web servers1.2.5. Web Scripting with PHP1.2.5.1. Introducing PHP51.2.6. The Database Tier1.2.7. Database Management Systems1.2.7.1. SQL1.2.7.2. Why use a database server?1.2.7.3. Examples of when to use a database server1.2.7.4. Examples of when not to use a DBMS1.2.7.5. The MySQL server1.2.7.6. Introducing MySQL 4
2. The PHP Scripting Language
2.1. Introducing PHP2.1.1. PHP Basics2.1.1.1. Creating PHP scripts2.1.1.2. Comments2.1.1.3. Outputting data with echo and print2.1.2. String Literals2.1.2.1. Variable substitution2.1.2.2. Character encoding2.1.3. Variables2.1.4. Types2.1.5. Constants2.1.6. Expressions, Operators, and Variable Assignment2.1.6.1. Operator precedence
2.2. Conditions and Branches
2.2.1. if...else Statement2.2.2. switch Statement2.2.3. Conditional Expressions
2.3. Loops
2.3.1. while2.3.2. do...while2.3.3. for2.3.4. Changing Loop Behavior
2.4. Functions
2.5. Working with Types
2.5.1. Type Conversion2.5.2. Automatic Type Conversion2.5.3. Examining Variable Type and Content2.5.3.1. Is-identical and is-not-identical operators2.5.3.2. Debugging with gettype( ), print_r( ), and var_dump( )2.5.3.3. Testing, setting, and unsetting variables
2.6. User-Defined Functions
2.6.1. Parameter Types and Return Types2.6.2. Variable Scope2.6.2.1. Global variables2.6.2.2. Static variables2.6.3. Passing Variables to Functions2.6.3.1. Passing parameters by reference2.6.3.2. Assigning by reference2.6.3.3. Default parameter values2.6.4. Reusing Functions with Include and Require Files2.6.4.1. Managing include files
2.7. A Working Example
3. Arrays, Strings, and Advanced Data Manipulation in PHP
3.1. Arrays3.1.1. Creating Arrays3.1.1.1. Associative arrays3.1.1.2. Removing elements from an array3.1.1.3. Array order3.1.1.4. Heterogeneous arrays3.1.1.5. Multidimensional arrays3.1.2. Using foreach Loops with Arrays3.1.3. Basic Array Functions3.1.3.1. Counting elements in arrays3.1.3.2. Functions that create arrays3.1.3.3. Exploding and imploding strings3.1.3.4. Finding the maximum and minimum values in an array3.1.3.5. Finding values in arrays with in_array( ) and array_search( )3.1.3.6. Keys and values3.1.3.7. Joining two or more arrays3.1.3.8. Reordering elements with array_reverse( )3.1.4. Sorting Arrays3.1.4.1. Sorting with sort( ) and rsort( )3.1.4.2. Sorting associative arrays3.1.4.3. Sorting on keys3.1.4.4. Sorting with user-defined element comparison
3.2. Strings
3.2.1. Length of a String3.2.2. Printing and Formatting Strings3.2.2.1. Creating formatted output with sprintf( ) and printf( )3.2.2.2. Padding strings3.2.2.3. Changing case3.2.2.4. Trimming whitespace3.2.3. Comparing Strings3.2.4. Finding and Extracting Substrings3.2.4.1. Extracting a substring from a string3.2.4.2. Finding the position of a substring3.2.4.3. Extracting a found portion of a string3.2.5. Replacing Characters and Substrings3.2.5.1. Replacing substrings3.2.5.2. Translating characters and substrings
3.3. Regular Expressions
3.3.1. Regular Expression Syntax3.3.1.1. Characters and wildcards3.3.1.2. Character lists3.3.1.3. Anchors3.3.1.4. Optional and repeating characters3.3.1.5. Groups3.3.1.6. Alternative patterns3.3.1.7. Escaping special characters3.3.1.8. Metacharacters3.3.2. Regular Expression Functions3.3.2.1. Finding and extracting values3.3.2.2. Replacing substrings3.3.2.3. Splitting a string into an array
3.4. Dates and Times
3.4.1. Generating a Timestamp3.4.1.1. Current time3.4.1.2. Creating timestamps with mktime( ) and gmmktime( )3.4.1.3. String to timestamp3.4.1.4. Subsecond times3.4.2. Formatting a Date3.4.3. Validating a Date
3.5. Integers and Floats
3.5.1. Absolute Value3.5.2. Ceiling and Floor3.5.3. Rounding3.5.4. Number Systems3.5.5. Basic Trigonometry Functions3.5.6. Powers and Logs3.5.7. Testing Number Results3.5.8. Random Number Generation
4. Introduction to Object-Oriented Programming with PHP 5
4.1. Classes and Objects4.1.1. Member Variables4.1.2. Member Functions4.1.3. Using include Files for Class Definitions4.1.4. Constructors4.1.5. Destructors4.1.6. Private Members Variables4.1.7. Private Member Functions4.1.8. Static Member Variables4.1.9. Static Member Functions4.1.10. Cloning Objects4.1.10.1. Cloning in PHP54.1.10.2. Cloning in PHP4
4.2. Inheritance
4.2.1. Calling Parent Constructors4.2.2. Redefined Functions4.2.3. Protected Member Variables and Functions4.2.4. Final Functions
4.3. Throwing and Catching Exceptions
4.3.1. The Exception Class
5. SQL and MySQL
5.1. Database Basics5.1.1. Introducing Relational Databases5.1.2. Database Terminology5.1.3. The Winestore Database5.1.3.1. The winestore entity-relationship model
5.2. MySQL Command Interpreter
5.3. Managing Databases and Tables
5.3.1. Creating Databases5.3.2. Creating Tables5.3.2.1. Tables and attributes5.3.2.2. Modifiers5.3.2.3. Keys5.3.3. Deleting Databases and Tables
5.4. Inserting, Updating, and Deleting Data
5.4.1. Inserting Data5.4.1.1. Defaults5.4.1.2. Auto-increment5.4.2. Deleting Data5.4.3. Updating Data
5.5. Querying with SQL SELECT
5.5.1. Basic Querying5.5.2. WHERE Clauses5.5.3. Sorting and Grouping Output5.5.3.1. ORDER BY5.5.3.2. GROUP BY5.5.3.3. HAVING5.5.3.4. Combining clauses5.5.3.5. DISTINCT5.5.4. Limiting Output in MySQL
5.6. Join Queries
5.6.1. Beware of the Cartesian Product5.6.2. Elementary Natural Joins5.6.2.1. Examples5.6.2.2. Using DISTINCT in joins5.6.3. Joins with More than Two Tables
5.7. Case Study: Adding a New Wine
6. Querying Web Databases
6.1. Querying a MySQL Database Using PHP6.1.1. Opening and Using a Database Connection6.1.2. Using mysql_fetch_array( )6.1.3. Error Handling of MySQL Database Functions6.1.4. Working with Table Structures6.1.5. Formatting Results6.1.6. Using Require Files in Practice6.1.7. Case Study: Producing a Select List6.1.7.1. Implementing the selectDistinct( ) function
6.2. Processing User Input
6.2.1. Passing Data from the Browser to the Server6.2.2. Passing Data with the HTML Form Environment6.2.3. Passing Data with URLs6.2.4. Passing Data with Embedded Links6.2.5. More on Accessing User Data6.2.5.1. Before PHP 4.26.2.6. Processing Form Data6.2.6.1. The MULTIPLE attribute6.2.6.2. Other form issues6.2.7. Security and User Data6.2.8. Querying with User Input6.2.9. One-Component Querying
6.3. MySQL Function Reference
6.3.1. Frequently Used Functions6.3.2. Other Functions6.3.3. Functions to Avoid
7. PEAR
7.1. Overview
7.2. Core Components
7.2.1. What's Installed?7.2.1.1. Unix systems—PHP 4.3.0 and later7.2.1.2. Microsoft Windows—PHP 4.3.2 and later7.2.2. Using PEAR DB7.2.2.1. Should I use PEAR DB?7.2.2.2. Getting started7.2.2.3. Handling errors in PEAR DB7.2.2.4. Essential functions for accessing MySQL with PEAR DB
7.3. Packages
7.3.1. Installing, Upgrading, and Understanding Packages7.3.1.1. Finding out about packages7.3.1.2. Using the PEAR installer7.3.2. Using HTML Templates7.3.2.1. Working with blocks and placeholders7.3.2.2. Nested blocks7.3.2.3. Preserving and removing blocks7.3.2.4. More on nesting and optional blocks7.3.2.5. Extended Integrated Templates (ITX)7.3.2.6. Essential IT and ITX functions7.3.3. Optional Packages7.3.3.1. Authentication7.3.3.2. Benchmarking7.3.3.3. Caching7.3.3.4. Console7.3.3.5. Database7.3.3.6. Date7.3.3.7. Filesystem7.3.3.8. HTML7.3.3.9. HTTP7.3.3.10. Internationalization7.3.3.11. Logging7.3.3.12. Mail7.3.3.13. Networking7.3.3.14. PEAR7.3.3.15. PHP7.3.3.16. XML7.3.3.17. Web services
8. Writing to Web Databases
8.1. Database Inserts, Updates, and Deletes8.1.1. Reloading Data and Relocation Techniques8.1.1.1. Solving the reload problem in practice8.1.2. Inserting, Updating, and Deleting Data8.1.2.1. Inserting data8.1.2.2. Updating data8.1.2.3. Case study: updates in practice8.1.2.4. Deleting data
8.2. Issues in Writing Data to Databases
8.2.1. Transactions and Concurrency8.2.2. Locking to Achieve Concurrency in MySQL8.2.2.1. When and how to lock tables8.2.2.2. The LOCK TABLES and UNLOCK TABLES statements in MySQL8.2.2.3. Locking for performance8.2.3. Locking Tables in Web Database Applications8.2.3.1. Locking methods that don't work in web database applications8.2.3.2. Locking with an auxiliary table8.2.3.3. Managing identifiers with PEAR DB
9. Validation with PHP and JavaScript
9.1. Validation and Error Reporting Principles9.1.1. Models That Don't Work9.1.2. Models That Do Work
9.2. Server-Side Validation with PHP
9.2.1. Mandatory Data9.2.2. Validating Strings9.2.2.1. Basic techniques9.2.2.2. Validating Zip and postcodes9.2.2.3. Validating email addresses9.2.2.4. Validating URLs9.2.2.5. Validating numbers9.2.2.6. Validating credit cards9.2.3. Validating Dates and Times9.2.3.1. Dates9.2.3.2. Times9.2.3.3. Using the PEAR Date package9.2.3.4. Logic, the date function, and MySQL
9.3. JavaScript and Client-Side Validation
9.3.1. Introducing JavaScript9.3.2. JavaScript and PHP9.3.2.1. Generating output9.3.2.2. Loops and conditionals9.3.2.3. Functions9.3.2.4. Debugging JavaScript9.3.2.5. Objects9.3.2.6. Events9.3.2.7. Methods and properties9.3.3. JavaScript Examples9.3.3.1. A password form validation function9.3.3.2. Rollover presentation with mouseover events9.3.3.3. Prefilling form data with JavaScript calculations9.3.3.4. Interacting with the web browser9.3.3.5. Which browser is the user using?9.3.3.6. Drop-down menus9.3.4. Case Study: A Generic JavaScript Validation Function9.3.4.1. The JavaScript validation script9.3.4.2. Using the JavaScript validation function9.3.4.3. The PHP and template components
10. Sessions
10.1. Introducing Session Management
10.2. PHP Session Management
10.2.1. Starting a Session10.2.2. Using Session Variables10.2.2.1. Unsetting session variables10.2.2.2. Session variable types10.2.2.3. Serialization of session variables10.2.3. Ending a Session10.2.4. Designing Session-Based Applications10.2.4.1. Session to track authenticated users10.2.4.2. Sessions to track anonymous users
10.3. Case Study: Using Sessions in Validation
10.3.1. Improving the Phonebook Details Form10.3.2. The Validation Script10.3.2.1. Improving error messages10.3.2.2. Saving last-entered values as a session variable10.3.2.3. The final validation script10.3.3. The Phonebook Entry Form Script10.3.3.1. Displaying previously entered form values10.3.3.2. Displaying error messages10.3.3.3. The final phonebook entry script
10.4. When to Use Sessions
10.4.1. Reasons to Use Sessions10.4.1.1. Performance10.4.1.2. Sequence of interaction10.4.1.3. Intermediate results10.4.1.4. Personalization10.4.2. Reasons to Avoid Sessions10.4.2.1. Need for centralized session store10.4.2.2. Performance10.4.2.3. Timeouts10.4.2.4. Bookmark restrictions10.4.2.5. Security
10.5. PHP Session API and Configuration
10.5.1. Functions for Accessing Sessions in PHP10.5.1.1. Functions used when register_globals is enabled10.5.2. Session Management Without Cookies10.5.2.1. No cookie?10.5.2.2. Including the session ID in URLs10.5.2.3. URL rewriting10.5.2.4. Turning off cookies10.5.3. Garbage Collection10.5.4. Configuration Parameters
11. Authentication and Security
11.1. HTTP Authentication11.1.1. How HTTP Authentication Works11.1.2. Using Apache to Authenticate
11.2. HTTP Authentication with PHP
11.2.1. Accessing User Credentials11.2.2. Managing HTTP Authentication with PHP11.2.3. Limiting Access by IP Address11.2.4. Authentication Using a Database11.2.4.1. Creating a database and table11.2.4.2. Protecting passwords11.2.4.3. Authenticating11.2.4.4. Encrypting other data in a database
11.3. Form-Based Authentication
11.3.1. Reasons to Use HTTP Authentication11.3.2. Reasons to Avoid HTTP Authentication11.3.3. Authentication and Session-Based Applications11.3.3.1. Session hijacking11.3.3.2. Recording IP addresses to detect session hijack attempts11.3.4. Session-Based Authentication Framework11.3.4.1. Code overview11.3.4.2. Login page11.3.4.3. Setup script11.3.4.4. The authentication.inc require file11.3.4.5. Application scripts and pages11.3.4.6. Logout script11.3.4.7. Password management
11.4. Protecting Data on the Web
11.4.1. The Secure Sockets Layer Protocol11.4.1.1. SSL architecture11.4.1.2. Cipher suites11.4.1.3. SSL sessions11.4.1.4. Certificates and certification authorities
12. Errors, Debugging, and Deployment
12.1. Errors12.1.1. Accessing the PHP Manual12.1.2. Configuring Error Reporting
12.2. Common Programming Errors
12.2.1. A Page That Produces Partial or No Output12.2.2. Variable Problems12.2.2.1. Variable naming12.2.2.2. Missing output12.2.3. Less Common Problems12.2.3.1. Complaints about headers12.2.3.2. Missing semicolons, braces, and quotes12.2.3.3. Source shown in the browser
12.3. Custom Error Handlers
12.3.1. A Basic Custom Handler12.3.2. A Production Error Handler12.3.2.1. Including debugging information12.3.2.2. Logging and notifying the user12.3.2.3. Triggering your own errors12.3.2.4. Cleaning up the application
13. Reporting
13.1. Creating a Report13.1.1. Formats13.1.1.1. Portable Document Format (PDF)13.1.1.2. Rich Text Format (RTF)13.1.1.3. PostScript13.1.1.4. HTML and XML13.1.1.5. Email and plain text
13.2. Producing PDF
13.2.1. Hello, world13.2.2. A Full-Featured Document13.2.3. A Database Example13.2.4. Creating a Report
13.3. PDF-PHP Reference
13.3.1. EZPDF Class13.3.2. Base Class
14. Advanced Features of Object-Oriented Programming in PHP 5
14.1. Working with Class Hierarchies14.1.1. Polymorphism14.1.2. Discovering Relationships14.1.2.1. Functions
14.2. Class Type Hints
14.3. Abstract Classes and Interfaces
14.3.1. The abstract keyword14.3.2. Interfaces
14.4. Freight Calculator Example
14.4.1. Review of the FreightCalculator14.4.2. Deliverable Interface14.4.3. Improving the FreightCalculator14.4.4. Summary of Improvements14.4.5. Using the Improved Freight Calculator14.4.6. Class Diagram
15. Advanced SQL
15.1. Exploring with SHOW
15.2. Advanced Querying
15.2.1. Advanced Join Types15.2.1.1. Natural and inner joins15.2.1.2. Left and right joins15.2.1.3. Unions15.2.2. Aliases15.2.3. Nested Queries15.2.3.1. Introduction15.2.3.2. The IN clause15.2.3.3. The EXISTS clause15.2.3.4. Nested queries in the FROM clause15.2.4. User Variables15.2.5. ROLLUP with GROUP BY15.2.6. Other MySQL Topics
15.3. Manipulating Data and Databases
15.3.1. Altering Databases15.3.2. More on Inserting Data15.3.2.1. Using INSERT with SELECT15.3.2.2. Using CREATE TABLE with SELECT15.3.2.3. Replacing data15.3.2.4. Bulk loading a file into a database15.3.2.5. Delayed insertion15.3.3. More on Deleting Data15.3.4. More on Updating Data
15.4. Functions
15.4.1. Arithmetic and Comparison Operators15.4.1.1. String functions15.4.1.2. Mathematical functions15.4.1.3. Date and time functions15.4.1.4. Miscellaneous operators and functions
15.5. Automating Querying
15.5.1. Unix15.5.2. Microsoft Windows
15.6. Table Types
15.6.1. Overview15.6.2. MyISAM15.6.3. InnoDB15.6.3.1. Transactions using COMMIT and ROLLBACK15.6.4. Heap
15.7. Backup and Recovery
15.7.1. Backup15.7.2. Selective Backups15.7.3. Restore15.7.4. Checking and Fixing Tables15.7.5. Exporting Data to Other Environments
15.8. Managing Users and Privileges
15.8.1. Creating Users and Privileges15.8.1.1. Privileges and scope15.8.1.2. Network access15.8.2. Revoking Privileges15.8.2.1. How MySQL manages privileges
15.9. Tuning MySQL
15.9.1. Index Design15.9.2. Design Tips15.9.3. Server Tuning Tips15.9.4. Query Caching15.9.4.1. Configuring query caching15.9.4.2. Controlling query caching
16. Hugh and Dave's Online Wines:A Case Study
16.1. Functional and System Requirements16.1.1. Requirements List
16.2. Application Overview
16.2.1. Winestore Scripts16.2.1.1. Functional overview16.2.1.2. Using and accessing the source code
16.3. Common Components
16.3.1. Application Templates16.3.2. The winestoreTemplate Class16.3.2.1. How the class works16.3.2.2. The buttons and the button parameter16.3.3. The winestoreFormTemplate Class16.3.3.1. How the class works16.3.4. Database Parameters16.3.5. Validation16.3.6. Custom Error Handler16.3.7. General-Purpose Functions
17. Managing Customers
17.1. Code Overview
17.2. Customer Validation
17.3. The Customer Form
17.3.1. The Customer Receipt Page
18. The Shopping Cart
18.1. Code Overview
18.2. The Winestore Home Page
18.3. The Shopping Cart Implementation
18.3.1. Viewing the Shopping Cart18.3.2. Adding Items to the Shopping Cart18.3.3. Emptying the Shopping Cart18.3.4. Updating the Shopping Cart Quantities
19. Ordering and Shipping at the Online Winestore
19.1. Code Overview
19.2. Credit Card and Shipping Instructions
19.3. Finalizing Orders
19.4. HTML and Email Receipts
19.4.1. Email Receipt19.4.1.1. The PEAR Mail package19.4.2. HTML Receipts
20. Searching and Authentication in the Online Winestore
20.1. Code Overview20.1.1. Searching and Browsing20.1.2. Authentication
20.2. Searching and Browsing
20.2.1. Search Criteria Form20.2.2. Querying and Displaying Results20.2.2.1. Finding the wines20.2.2.2. Displaying the wines
20.3. Authentication
20.3.1. General-Purpose Functions20.3.2. Logging In and Out20.3.3. Changing Passwords
A. Linux Installation Guide
A.1. Finding Out What's InstalledA.1.1. MySQLA.1.2. Apache and PHP
A.2. Installation Overview
A.3. Installing MySQL
A.3.1. Starting MySQLA.3.2. Configuring MySQL
A.4. Installing Apache
A.4.1. Installing a Secure Apache ServerA.4.1.1. Installing OpenSSLA.4.1.2. Creating a key and a certificateA.4.2. Installing a Regular Apache Server
A.5. Installing PHP
A.6. What's Needed for This Book
A.6.1. Installing PEAR PackagesA.6.2. Installing the Code ExamplesA.6.3. Installing the PDF PHP LibraryA.6.4. Loading the Winestore DatabaseA.6.5. Installing the Winestore Application
B. Microsoft Windows Installation Guide
B.1. Installation Overview
B.2. Installing with EasyPHP
B.3. What's Needed for This Book
B.3.1. Installing PEAR PackagesB.3.2. Installing the Code ExamplesB.3.3. Installing the PDF PHP LibraryB.3.4. Loading the Winestore DatabaseB.3.5. Installing the Winestore Application
C. Mac OS X Installation Guide
C.1. Getting Started
C.2. Installing MySQL
C.2.1. Configuring MySQL
C.3. Setting Up Apache and PHP
C.3.1. Starting ApacheC.3.2. The Apache and PHP SetupC.3.3. Upgrading PHPC.3.4. Installing a Secure Apache ServerC.3.4.1. Creating a key and certificate
C.4. What's Needed for This Book
C.4.1. Installing PEAR PackagesC.4.2. Installing the Code ExamplesC.4.3. Installing the PDF PHP LibraryC.4.4. Loading the Winestore DatabaseC.4.5. Installing the Winestore Application
D. Web Protocols
D.1. Network BasicsD.1.1. TCP/IPD.1.2. IP AddressesD.1.2.1. Ports
D.2. Hypertext Transfer Protocol
D.2.1. Uniform Resource LocatorsD.2.1.1. ProtocolD.2.1.2. Host and service identificationD.2.1.3. Nonstandard TCP portsD.2.1.4. Resource identificationD.2.1.5. Parameters and queriesD.2.1.6. Fragment identifiersD.2.1.7. Absolute and relative URLsD.2.1.8. URL encodingD.2.2. HTTP RequestsD.2.2.1. Request methodsD.2.2.2. GET versus POSTD.2.3. HTTP ResponsesD.2.3.1. Status codesD.2.4. Caching
E. Modeling and Designing Relational Databases
E.1. The Relational ModelE.1.1. Case Study: Relationships in the Winestore
E.2. Entity-Relationship Modeling
E.2.1. Case Study: Modeling the Online WinestoreE.2.1.1. Identifying entities in ER modelingE.2.1.2. Identifying relationships in ER modelingE.2.1.3. Relationships in the winestore ER modelE.2.1.4. Identifying key attributes in ER modelingE.2.1.5. Other ER modeling toolsE.2.1.6. Completing the ER modelE.2.2. Converting an Entity-Relationship Model to SQLE.2.2.1. Step 1: Convert regular entities to tablesE.2.2.2. Step 2: Convert weak entities to tablesE.2.2.3. Step 3: One-to-one relationshipsE.2.2.4. Step 4: One-to-many relationshipsE.2.2.5. Step 5: Many-to-many relationships
F. Managing Sessions in theDatabase Tier
F.1. Using a Database to Keep State
F.2. PHP Session Management
F.2.1. PHP Session Management Storage MethodsF.2.2. Building User-Defined Storage Handlers
F.3. MySQL Session Store
F.3.1. Session Table StructureF.3.2. Handler ImplementationsF.3.2.1. Support functionsF.3.2.2. Session open handlerF.3.2.3. Session read handlerF.3.2.4. Session write handlerF.3.2.5. Session close handlerF.3.2.6. Session destroy handlerF.3.2.7. Garbage collection handlerF.3.2.8. Registering session handlersF.3.3. Using the User-Defined Session Handler CodeF.3.4. PEAR's HTTP_Session Package
G. Resources
G.1. Client Tier Resources
G.2. Middle-Tier Resources
G.2.1. Web Server and Web Technology ResourcesG.2.2. Networking and Web ResourcesG.2.3. PHP resources
G.3. Database Tier Resources
G.4. Security and Cryptography Resources
H. The Improved MySQL Library
H.1. New Features
H.2. Getting Started
H.2.1. Installing the LibraryH.2.2. Migrating to the New Library
H.3. Using the New Features
H.3.1. Basic FeaturesH.3.2. Preparing and Executing QueriesH.3.2.1. Inserting dataH.3.2.2. Retrieving dataH.3.3. Profiling Queries
Index
About the Authors
Colophon
SPECIAL OFFER: Upgrade this ebook with O’Reilly

Content preview from Web Database Applications with PHP and MySQL, 2nd Edition

Join Queries

You'll often want to output data that's based on relationships between two or more tables. For example, in the winestore database, you might want to know which customers have placed orders, which customers live in Australia, or how many bottles of wine Lucy Williams has bought. These are examples of join queries, queries that match rows between tables based (usually) on primary key values. In SQL, a join query matches rows from two or more tables based on a condition in a WHERE clause and outputs only those rows that meet the condition.

As part of the process of converting the winestore entity-relationship model to SQL statements, we've included the attributes required in any practical join condition. To understand which tables can be joined in the winestore database, and how the joins are processed, it's helpful to have a copy of the ER model at hand as you work your way through this section.

Beware of the Cartesian Product

Suppose you want to find out the names of the wineries in the winestore database and, for each winery, the name of the region that it's located in. To do this, you examine the ER model and discover that the region and winery tables are related, and that they both contain attributes that you need in the answer to your query. Specifically, you need to retrieve the winery_name attribute from the winery table and the region_name attribute from the region table, and you need to join the two tables together to find the result.

Consider this query, which ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Web Database Applications with PHP, and MySQL

Publisher Resources

ISBN: 0596005431Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Web Database Applications with PHP and MySQL, 2nd Edition

by Hugh E. Williams, David Lane

Join Queries

Beware of the Cartesian Product

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.