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

What This Book Is About

1.1. The Web
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.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.1. if...else Statement2.2.2. switch Statement2.2.3. Conditional Expressions
2.3.1. while2.3.2. do...while2.3.3. for2.3.4. Changing Loop Behavior
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.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
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.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.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.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.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.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.1. Calling Parent Constructors4.2.2. Redefined Functions4.2.3. Protected Member Variables and Functions4.2.4. Final Functions
4.3.1. The Exception Class
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.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.1. Inserting Data5.4.1.1. Defaults5.4.1.2. Auto-increment5.4.2. Deleting Data5.4.3. Updating Data
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.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
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.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.1. Frequently Used Functions6.3.2. Other Functions6.3.3. Functions to Avoid
7.1. Overview
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.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.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.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.1. Validation and Error Reporting Principles9.1.1. Models That Don't Work9.1.2. Models That Do Work
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.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.1. Introducing 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.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.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.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.1. HTTP Authentication11.1.1. How HTTP Authentication Works11.1.2. Using Apache to Authenticate
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.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.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.1. Errors12.1.1. Accessing the PHP Manual12.1.2. Configuring Error Reporting
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.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.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.1. Hello, world13.2.2. A Full-Featured Document13.2.3. A Database Example13.2.4. Creating a Report
13.3.1. EZPDF Class13.3.2. Base Class
14.1. Working with Class Hierarchies14.1.1. Polymorphism14.1.2. Discovering Relationships14.1.2.1. Functions
14.3.1. The abstract keyword14.3.2. Interfaces
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.1. Exploring with SHOW
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.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.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.1. Unix15.5.2. Microsoft Windows
15.6.1. Overview15.6.2. MyISAM15.6.3. InnoDB15.6.3.1. Transactions using COMMIT and ROLLBACK15.6.4. Heap
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.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.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.1. Functional and System Requirements16.1.1. Requirements List
16.2.1. Winestore Scripts16.2.1.1. Functional overview16.2.1.2. Using and accessing the source code
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.1. Code Overview
17.3.1. The Customer Receipt Page
18.1. Code Overview
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.1. Code Overview
19.4.1. Email Receipt19.4.1.1. The PEAR Mail package19.4.2. HTML Receipts
20.1. Code Overview20.1.1. Searching and Browsing20.1.2. Authentication
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.1. General-Purpose Functions20.3.2. Logging In and Out20.3.3. Changing Passwords
A.1. Finding Out What's InstalledA.1.1. MySQLA.1.2. Apache and PHP
A.3.1. Starting MySQLA.3.2. Configuring MySQL
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.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.1. Installation Overview
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.1. Getting Started
C.2.1. Configuring MySQL
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.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.1. Network BasicsD.1.1. TCP/IPD.1.2. IP AddressesD.1.2.1. Ports
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.1. The Relational ModelE.1.1. Case Study: Relationships in the Winestore
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.1. Using a Database to Keep State
F.2.1. PHP Session Management Storage MethodsF.2.2. Building User-Defined Storage Handlers
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.1. Client Tier Resources
G.2.1. Web Server and Web Technology ResourcesG.2.2. Networking and Web ResourcesG.2.3. PHP resources
H.1. New Features
H.2.1. Installing the LibraryH.2.2. Migrating to the New Library
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

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

MySQL Command Interpreter

The MySQL command interpreter is commonly used to create databases and tables in web database applications and to test queries. Throughout the remainder of this chapter we discuss the SQL statements for managing a database. All these statements can be directly entered into the command interpreter and executed. In later chapters, we'll show how to include SQL statements in PHP scripts so that web applications can get and change data in a database.

Once the MySQL server is running, the command interpreter can be used. The command interpreter can be run using the following command from the shell in a Unix or Mac OS X system, assuming you've created a user hugh with a password shhh:

%/usr/local/bin/mysql -uhugh -pshhh

The shell prompt is represented here as a percentage character, %.

On a Microsoft Windows platform, you can access the command interpreter by clicking on the Start menu, then the Run option, and typing into the dialog box:

"C:\Program Files\EasyPHP1-7\mysql\bin\mysql.exe" -uhugh -pshhh

Then, press the Enter key or click OK.

(For both Unix and Microsoft Windows environments, we're assuming you've installed MySQL in the default directory location using our instructions in Appendix A through Appendix C.)

Running the command interpreter displays the output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.0.15-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

The command interpreter ...