Home Page Icon
Home Page
Table of Contents for
Cover
Close
Cover
by David Lane, Hugh E. Williams
Web Database Applications with PHP, and MySQL
Web Database Applications with PHP & MySQL
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
How to Contact Us
Web Site and Code Examples
Acknowledgments
1. Database Applications and the Web
Three-Tier Architectures
Hypertext Transfer Protocol
HTTP example
State
Thin Clients
The Client Tier
The Middle Tier
Web Servers
The Apache HTTP server, Version 1.3
The Apache HTTP server, Version 2.0
Web Scripting with PHP
The Database Tier
Database Management Systems
Why Use a DBMS?
Examples of when to use a DBMS
Examples of when not to use a DBMS
The MySQL DBMS
SQL
History
SQL components
Our Case Study
What Is Hugh and Dave’s Online Wines?
System requirements
Components of the Winestore
Database-driven querying
User-driven querying and browsing
Data entry and saving records to a database
Validation in the client and middle tiers
User tracking and session management
Authentication
The complete application
2. PHP
Introducing PHP
PHP Basics
Creating PHP scripts
Comments
Outputting data with echo and print
String literals
Variables
Types
Constants
Expressions, Operators, and Variable Assignment
Expressions
Operator precedence
Type Conversion
Automatic type conversion
Examining Variable Type and Content
Debugging with print_r( ) and var_dump( )
Testing, setting, and unsetting variables
Conditions and Branches
if...else Statement
switch Statement
Conditional Expressions
Loops
while
do...while
for
foreach
Changing Loop Behavior
A Working Example
Comments on Example 2.3
Arrays
Creating Arrays
Associative arrays
Heterogeneous arrays
Multidimensional arrays
Using foreach Loops with Arrays
Using Array Pointers
Basic Array Functions
Counting elements in arrays
Finding the maximum and minimum values in an array
Finding values in arrays with in_array( ) and array_search( )
Reordering elements in arrays with array_reverse( )
Sorting Arrays
Sorting with sort( ) and rsort( )
Sorting associative arrays
Sorting on keys
Sorting with user-defined element comparison
Strings
String Literals
Variable substitution
Length of a string
Printing and Formatting Strings
Creating formatted output with sprintf( ) and printf( )
Padding strings
Changing case
Trimming whitespace
Rendering newline characters with <br>
Comparing Strings
Finding and Extracting Substrings
Extracting a substring from a string
Finding the position of a substring
Extracting a found portion of a string
Extracting multiple values from a string
Replacing Characters and Substrings
Replacing substrings
Translating characters and substrings
Regular Expressions
Regular Expression Syntax
Characters and wildcards
Character lists
Anchors
Optional and repeating characters
Groups
Alternative patterns
Escaping special characters
Metacharacters
Regular Expression Functions
Finding and extracting values
Replacing substrings
Splitting a string into an array
Date and Time Functions
Generating a Timestamp
Current time
Creating timestamps with mktime( ) and gmmktime( )
String to timestamp
Subsecond times
Formatting a Date
Validating a Date
Integer and Float Functions
Absolute Value
Ceiling and Floor
Rounding
Number Systems
Basic Trigonometry Functions
Powers and Logs
Random Number Generation
User-Defined Functions
Argument Types and Return Types
Variable Scope
Global variables
How Variables Are Passed to Functions
Passing arguments by reference
Assigning by reference
Default argument values
Reusing Functions with Include and Require Files
Objects
Classes and Objects
Inheritance
Common Mistakes
A Page That Produces Partial or No Output
Variable Problems
Variable naming
Missing output
Complaints About Headers
Other Common Problems
3. MySQL and SQL
Database Basics
Introducing Relational Databases
Terminology
Quick Start Guide
Loading the Winestore Database
The Winestore Database
The winestore entity-relationship model
Creating the winestore with SQL
MySQL Command Interpreter
Managing Databases, Tables, and Indexes
Creating Databases
Creating Tables
Altering Tables and Indexes
Displaying Database Structure with SHOW
Inserting, Updating, and Deleting Data
Inserting Data
Bulk loading into a database
Transferring data between databases and DBMSs
Deleting Data
Updating Data
Querying with SQL SELECT
Basic Querying
WHERE Clauses
Sorting and Grouping Output
ORDER BY
GROUP BY
HAVING
DISTINCT
Limiting Result Sets in MySQL
Join Queries
Beware of the Cartesian Product
Elementary Natural Joins
Examples
Table aliases in SQL queries
Using DISTINCT in joins
Joins with More than Two Tables
Modifying the Database
Adding a New Wine to the Winestore
Buying a Bottle of Wine from the Winestore
INSERTing with a SELECT Statement
Functions
Arithmetic and comparison operators
String-comparison operators and functions
Mathematical functions
Date and time functions
Miscellaneous operators and functions
More on SQL and MySQL
Keys, Primary Keys, and Indexes
Tuning the Database System
Adding and Deleting Users
Permissions
Limitations of MySQL
4. Querying Web Databases
Connecting to a MySQL Database
Opening and Using a Database Connection
Essential Functions for Accessing MySQL with PHP
More MySQL Functions in PHP
Frequently used functions
Other functions
Functions to avoid
Error Handling of MySQL Database Functions
Formatting Results
Using Include Files in Practice
Case Study: The Front-Page Panel
Step 1: Producing Visually Appealing Tables
Limitations of Step 1
Step 2: Adding Varieties to the Panel
Adding a second or subsequent query
Adding calculations to the result presentation
Step 3: Finishing the Panel
Fixing the queries
Interacting with Other DBMSs Using PHP
Microsoft SQL Server
Open DataBase Connectivity (ODBC)
Oracle 7 and 8 Through the OCI8 Interface
PostgreSQL
5. User-Driven Querying
User Input
Passing Data with URLs
Passing Data with the HTML <form> Environment
Passing Data with Embedded Links
Security and User Data
How PHP Initializes Variables
Querying with User Input
Combined Scripts
Adding Links to Results
One-Component Querying
Case Study: Previous and Next Browsing
Step 1: Using the Generic browse Function
Step 2: Implementing the Generic browse Function
Step 3: Adding Page Numbers
What’s Missing from the Previous and Next Browser
Case Study: Producing a select List
Implementing the selectDistinct Function
6. Writing to Web Databases
Database Inserts, Updates, and Deletes
Reloading Data and Relocation Techniques
Solving the reload problem in practice
Uploading and Inserting Files into Databases
Inserting, Updating, and Deleting Data
PHP DML functions for database modifications
Inserting data
Updating data
Case study: Inserts and updates in practice
Deleting data
Issues in Writing Data to Databases
Transactions and Concurrency
Locking for Concurrency in MySQL
When and how to lock tables
The LOCK TABLES and UNLOCK TABLES statements in MySQL
Locking for performance
Locking Tables in Web Database Applications
Locking methods that don’t work in web database applications
Locking with an auxiliary table
The table-level locking paradigm in MySQL
Other locking paradigms
What isn’t covered here
7. Validation on the Server and Client
Validation and Error Reporting for Web Database Applications
Models That Don’t Work
Models That Do Work
Server-Side Validation
Case Study: Customer Validation in the Winestore
Validating dates
Validating numeric fields
Validating email addresses
Processing <form> Data on the Server Side
Processing <form> controls with the MULTIPLE attribute
Other <form> issues
Client-Side Validation with JavaScript
Validating <form> Data with JavaScript
Case study: A generic JavaScript validation function
Case study: A password <form> validation function
JavaScript Tips and Tricks
Rollover presentation with mouseOver events
Prefilling <form> data with JavaScript calculations
Interacting with the web browser
Which browser is the user using?
Comments
8. Sessions
Building Applications That Keep State
Managing State in the Client Tier
Cookies
Session Management Over the Web
PHP Session Management
Overview
Starting a Session
Using Session Variables
Ending a Session
Functions for Accessing Sessions in PHP
Session Management Without Cookies
Encoding the session ID as a GET variable
Turning off cookies
Garbage Collection
Configuration of PHP Session Management
Case Study: Adding Sessions to the Winestore
Improving the Client Entry <form>
The Validation Script
Improving error messages
Saving last-entered values as a session variable
The final validation script
The Client Entry <form> Script
Displaying previously entered values
Displaying error messages
The final client entry script
When to Use Sessions
Reasons to Use Sessions
Performance
Sequence of interaction
Intermediate results
Personalization
Reasons to Avoid Sessions
Need for centralized session store
Performance
Timeouts
Bookmark restrictions
Security
9. Authentication and Security
HTTP Authentication
How HTTP Authentication Works
Using Apache to Authenticate
HTTP Authentication with PHP
Access to User Credentials from PHP
Managing HTTP Authentication with PHP
Authorizing User Access
Limits placed on IP addresses
Authentication Using a Database
MySQL encryption
Encrypting other data in a database
Web Database Applications and Authentication
Building Stateless Applications
Building Session-Based Applications
Forcing users to a login page
Authenticating without HTTP
Session hijacking
Recording IP addresses to detect session hijack attempts
Case Study: Customer Authentication
Case study overview
Login page
Authentication script
Logout script
Authorizing other requests
Protecting Data on the Web
The Secure Sockets Layer Protocol
SSL architecture
Cipher suites
SSL sessions
Certificates and Certification Authorities
10. Winestore Customer Management
Overview of the Winestore Application
Winestore Scripts
Customer Management
Customer Validation
The Customer <form>
The Customer Receipt Page
Authenticating Users
The Winestore Include Files
Custom Error Handlers
11. The Winestore Shopping Cart
The Winestore Home Page
The Shopping Cart Architecture
Viewing the Shopping Cart
Adding Items to the Shopping Cart
Emptying the Shopping Cart
Updating the Shopping Cart Quantities
Managing Redirection
12. Ordering and Shipping at the Winestore
Finalizing Orders
HTML and Email Receipts
13. Related Topics
Automated Housekeeping
cron Jobs
Templates
Templates in the Shipping Module
The application logic
The template
Searching and Browsing
A. Installation Guide
Installing MySQL, Apache, and PHP
Installing MySQL
Configuring MySQL
Installing Apache
Installing PHP
Installing the Winestore Examples
Installing Apache to Use SSL
Installing OpenSSL
Installing Apache and ApacheSSL
Creating a Key and Certificate
Installation Resources
B. Internet and Web Protocols
The Internet
An Analogy
TCP/IP
IP addresses
Ports
Hypertext Transfer Protocol
Request and Response Model
Simulating an HTTP request
Uniform Resource Locators
Protocol
Host and service identification
Nonstandard TCP ports
Resource identification
Parameters and queries
Fragment identifiers
Absolute and relative URLs
URL encoding
HTTP Requests
Request methods
GET versus POST
HTTP Responses
Status codes
Caching
HTTP/1.0 cache control
HTTP/1.1 cache control
HTTP and TCP/IP
Simultaneous request model
Persistent connections
MIME
C. Modeling and Designing Relational Databases
The Relational Model
Case Study: Relations in the Winestore
Entity-Relationship Modeling
Case Study: Modeling the Online Winestore
System requirements analysis
Identifying entities in ER modeling
Identifying relationships in ER modeling
Relationships in the winestore ER model
Identifying key attributes in ER modeling
Other ER modeling tools
Completing the ER model
Converting an Entity-Relationship Model to SQL
Step 1: Convert regular entities to tables
Step 2: Convert weak entities to tables
Step 3: One-to-one relationships
Step 4: Regular one-to-many relationships
Step 5: Many-to-many relationships
D. Managing Sessions in the Database Tier
Using a Database to Keep State
PHP Session Management
PHP Session Management Storage Methods
Building User-Defined Session Handlers
MySQL Session Store
Session Table Structure
Handler Implementations
Support functions
sessionOpen
sessionRead
sessionWrite
sessionClose
sessionDestroy
Garbage collection
Registering session handlers
Using the User-Defined Session Handler Code
E. Resources
Client Tier Resources
Middle Tier Resources
Web Server and Web Technology Resources
Networking and Web Resources
More About PHP
Books
Web resources
Libraries
Third-party products
Open source PHP applications
Database Tier Resources
Security and Cryptography Resources
Index
Colophon
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Next
Next Chapter
Web Database Applications with PHP & MySQL
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset