Cover image for Learning PHP and MySQL

Book description

The PHP scripting language and MySQL open source database are quite effective independently, but together they make a simply unbeatable team. When working hand-in-hand, they serve as the standard for the rapid development of dynamic, database-driven websites. This combination is so popular, in fact, that it's attracting many programming newbies who come from a web or graphic design background and whose first language is HTML. If you fall into this ever-expanding category, then this book is for you.

Learning PHP and MySQL starts with the very basics of the PHP language, including strings and arrays, pattern matching and a detailed discussion of the variances in different PHP versions. Next, it explains how to work with MySQL, covering information on SQL data access for language and data fundamentals like tables and statements. Finally, after it's sure that you've mastered these separate concepts, the book shows you how to put them together to generate dynamic content. In the process, you'll also learn about error handling, security, HTTP authentication, and more.

If you're a hobbyist who is intimidated by thick, complex computer books, then this guide definitely belongs on your shelf. Learning PHP and MySQL explains everything--from basic concepts to the nuts and bolts of performing specific tasks--in plain English.

Part of O'Reilly's bestselling Learning series, the book is an easy-to-use resource designed specifically for newcomers. It's also a launching pad for future learning, providing you with a solid foundation for more advanced development.

Table of Contents

  1. Learning PHP and MySQL
    1. SPECIAL OFFER: Upgrade this ebook with O’Reilly
    2. Preface
      1. Audience
      2. Assumptions This Book Makes
      3. Organization of This Book
      4. Supporting Books
      5. Conventions Used in This Book
      6. Using Code Examples
      7. Safari® Enabled
      8. How to Contact Us
      9. Acknowledgments
    3. 1. Dynamic Content and the Web
      1. 1.1. PHP and MySQL's Place in Web Development
        1. 1.1.1. Advantages of Using PHP with MySQL
        2. 1.1.2. The Value of Open Source
      2. 1.2. The Components of a PHP Application
        1. 1.2.1. Birth of PHP
        2. 1.2.2. Birth of Apache
        3. 1.2.3. Apache Versions
        4. 1.2.4. Birth of MySQL
        5. 1.2.5. Compatibility
      3. 1.3. Integrating Many Sources of Information
        1. 1.3.1. MySQL Database
        2. 1.3.2. PHP Templates
        3. 1.3.3. Cascading Style Sheets
        4. 1.3.4. HTML Markup
      4. 1.4. Requesting Data from a Web Page
        1. 1.4.1. PHP Interpretation on the Server
      5. 1.5. Chapter 1 Questions
    4. 2. Installation
      1. 2.1. Developing Locally
        1. 2.1.1. Installing Apache
        2. 2.1.2. Installing PHP
        3. 2.1.3. Installing MySQL 4.1
      2. 2.2. Working Remotely
      3. 2.3. Chapter 2 Questions
    5. 3. Exploring PHP
      1. 3.1. PHP and HTML Text
        1. 3.1.1. Text Output
          1. 3.1.1.1. Separating PHP from HTML
      2. 3.2. Coding Building Blocks
        1. 3.2.1. Variables
          1. 3.2.1.1. Reading a variable's value
          2. 3.2.1.2. Variable types
          3. 3.2.1.3. Variable scope
            1. 3.2.1.3.1. Global variables
            2. 3.2.1.3.2. Static variables
            3. 3.2.1.3.3. Super global variables
        2. 3.2.2. Strings
          1. 3.2.2.1. Special characters in strings
          2. 3.2.2.2. Comparing strings
        3. 3.2.3. Concatenation
          1. 3.2.3.1. Combining strings with other types
        4. 3.2.4. Constants
          1. 3.2.4.1. Predefined constants
        5. 3.2.5. Doing Math
          1. 3.2.5.1. Combined assignment
          2. 3.2.5.2. Autoincrement and autodecrement
          3. 3.2.5.3. Pre-increment and -decrement
      3. 3.3. Chapter 3 Questions
    6. 4. PHP Decision Making
      1. 4.1. Expressions
      2. 4.2. Operator Concepts
        1. 4.2.1. Number of Operands
        2. 4.2.2. Types of Operands
          1. 4.2.2.1. Order of precedence
          2. 4.2.2.2. Associativity
        3. 4.2.3. Relational Operators
          1. 4.2.3.1. Equality
          2. 4.2.3.2. Comparison operators
          3. 4.2.3.3. Logical operators
      3. 4.3. Conditionals
        1. 4.3.1. The if Statement
          1. 4.3.1.1. The else statement
          2. 4.3.1.2. The elseif statement
        2. 4.3.2. The ? Operator
        3. 4.3.3. The switch Statement
          1. 4.3.3.1. Breaking out
          2. 4.3.3.2. Defaulting
      4. 4.4. Looping
        1. 4.4.1. while Loops
        2. 4.4.2. do . . . while Loops
        3. 4.4.3. for Loops
        4. 4.4.4. Breaking Out of a Loop
        5. 4.4.5. continue Statements
      5. 4.5. Chapter 4 Questions
    7. 5. Functions
      1. 5.1. Calling Functions
      2. 5.2. Defining Functions
        1. 5.2.1. Parameters
        2. 5.2.2. Parameter References
        3. 5.2.3. Including and Requiring PHP Files
        4. 5.2.4. The include Statement
          1. 5.2.4.1. The include_once statement
          2. 5.2.4.2. require and require_once functions
        5. 5.2.5. Testing a Function
      3. 5.3. Object-Oriented Programming
        1. 5.3.1. Creating a Class
        2. 5.3.2. Creating an Instance
        3. 5.3.3. Methods and Constructors
        4. 5.3.4. Variable Scope Within Classes
        5. 5.3.5. Inheritance
          1. 5.3.5.1. The extends operator
          2. 5.3.5.2. The parent operator
        6. 5.3.6. Static Methods and Variables
          1. 5.3.6.1. The :: operator (scope resolution)
        7. 5.3.7. Variable References
      4. 5.4. Chapter 5 Questions
    8. 6. Arrays
      1. 6.1. Array Fundamentals
        1. 6.1.1. Associative Versus Numeric Indexed Arrays
        2. 6.1.2. Creating an Array
          1. 6.1.2.1. Assignment via array identifiers
          2. 6.1.2.2. Assignment using array
          3. 6.1.2.3. Looping through and referencing array values
          4. 6.1.2.4. Adding values to an array
          5. 6.1.2.5. Counting how many elements are in an array
          6. 6.1.2.6. Sorting arrays
        3. 6.1.3. Multidimensional Arrays
        4. 6.1.4. Extracting Variables from an Array
          1. 6.1.4.1. Using compact to build an array from variables
        5. 6.1.5. Array Functions in PHP
      2. 6.2. Chapter 6 Questions
    9. 7. Database Basics
      1. 7.1. Database Design
        1. 7.1.1. Relational Databases
        2. 7.1.2. Relationship Types
          1. 7.1.2.1. One-to-one relationships
          2. 7.1.2.2. One-to-many relationships
          3. 7.1.2.3. Many-to-many relationships
        3. 7.1.3. Normalization
        4. 7.1.4. Forms of Normalization
          1. 7.1.4.1. First Normal Form
          2. 7.1.4.2. Second Normal Form
          3. 7.1.4.3. Third Normal Form
        5. 7.1.5. Column Data Types
      2. 7.2. Structured Query Language
        1. 7.2.1. Creating Tables
        2. 7.2.2. Adding Data to a Table
        3. 7.2.3. Querying the Database
          1. 7.2.3.1. Limit results with WHERE
          2. 7.2.3.2. Specifying the order
          3. 7.2.3.3. Joining tables together
          4. 7.2.3.4. Natural joins
          5. 7.2.3.5. Aliases
        4. 7.2.4. Modifying Database Data
        5. 7.2.5. Deleting Database Data
        6. 7.2.6. Search Functions
        7. 7.2.7. Logical Operators
      3. 7.3. Chapter 7 Questions
    10. 8. Using MySQL
      1. 8.1. MySQL Database
        1. 8.1.1. Accessing the Database with the Command Line
          1. 8.1.1.1. Prompts
          2. 8.1.1.2. Commands
      2. 8.2. Managing the Database
        1. 8.2.1. Creating Users
        2. 8.2.2. Creating a MySQL Database
        3. 8.2.3. Table Manipulation
          1. 8.2.3.1. Renaming a table
          2. 8.2.3.2. Changing a column's data type
          3. 8.2.3.3. Adding a column
          4. 8.2.3.4. Remove a column
          5. 8.2.3.5. Deleting an entire table
        4. 8.2.4. Using phpMyAdmin
      3. 8.3. Backing Up and Restoring Data
        1. 8.3.1. Copying Database Files
        2. 8.3.2. The mysqldump Command
          1. 8.3.2.1. Backing up
          2. 8.3.2.2. Restoring a MySQL backup
          3. 8.3.2.3. Working with other formats
          4. 8.3.2.4. The mysqlimport command
          5. 8.3.2.5. Backup best practices
      4. 8.4. Advanced SQL
        1. 8.4.1. Indexes
          1. 8.4.1.1. When indexes are used
          2. 8.4.1.2. Where to specify the index
          3. 8.4.1.3. Multicolumn indexes
        2. 8.4.2. Selecting Using the LEFT JOIN ON Clause
        3. 8.4.3. Using Database Functions
          1. 8.4.3.1. String functions
            1. 8.4.3.1.1. Concatenation
            2. 8.4.3.1.2. Concatenation with a predefined separator
            3. 8.4.3.1.3. Calculate a string length
            4. 8.4.3.1.4. Changing strings to upper- or lowercase
            5. 8.4.3.1.5. Trimming and padding strings
            6. 8.4.3.1.6. String location and position
            7. 8.4.3.1.7. Cutting up strings
            8. 8.4.3.1.8. Search and replace function
          2. 8.4.3.2. Date and time functions
            1. 8.4.3.2.1. Days, months, years, and weeks
            2. 8.4.3.2.2. Hours, minutes, and seconds
            3. 8.4.3.2.3. Dates and times arithmetic
            4. 8.4.3.2.4. NOW function
            5. 8.4.3.2.5. Formatting for display
      5. 8.5. Chapter 8 Questions
    11. 9. Getting PHP to Talk to MySQL
      1. 9.1. The Process
        1. 9.1.1. Resources
      2. 9.2. Querying the Database with PHP Functions
        1. 9.2.1. Including Database Login Details
        2. 9.2.2. Connecting to the Database
          1. 9.2.2.1. Troubleshooting connection errors
        3. 9.2.3. Selecting the Database
        4. 9.2.4. Building the SQL SELECT Query
        5. 9.2.5. Executing the Query
        6. 9.2.6. Fetching and Displaying
          1. 9.2.6.1. Fetch types
        7. 9.2.7. Closing the Connection
        8. 9.2.8. Putting It All Together
      3. 9.3. Using PEAR
        1. 9.3.1. Installing
          1. 9.3.1.1. Unix
          2. 9.3.1.2. Windows
          3. 9.3.1.3. Hosted ISP
        2. 9.3.2. Adding Additional Packages
        3. 9.3.3. Rewriting the Books Example with PEAR
          1. 9.3.3.1. Creating a connect instance
          2. 9.3.3.2. Connect string
          3. 9.3.3.3. Querying
          4. 9.3.3.4. Fetching
          5. 9.3.3.5. Closing
          6. 9.3.3.6. PEAR error reporting
      4. 9.4. Chapter 9 Questions
    12. 10. Working with Forms
      1. 10.1. Building a Form
        1. 10.1.1. Accessing Submitted Form Values
        2. 10.1.2. Default Values
        3. 10.1.3. Types of Input
          1. 10.1.3.1. Text boxes
          2. 10.1.3.2. Text areas
          3. 10.1.3.3. Checkboxes
          4. 10.1.3.4. Radio buttons
          5. 10.1.3.5. Hidden
          6. 10.1.3.6. Selects
        4. 10.1.4. Working with Multiple Values
        5. 10.1.5. Validating Data
          1. 10.1.5.1. Validating checkboxes, radio buttons, and selects
          2. 10.1.5.2. Validating text boxes and text areas
        6. 10.1.6. Building a Feet-to-Meters Converter in PHP
        7. 10.1.7. Building a Time Zone Conversion Utility in PHP
        8. 10.1.8. Querying the Database with Form Data
      2. 10.2. Templates
        1. 10.2.1. Template Engine
        2. 10.2.2. Installation
          1. 10.2.2.1. Application level directories
          2. 10.2.2.2. Creating sample scripts
          3. 10.2.2.3. Create a sample template
      3. 10.3. Chapter 10 Questions
    13. 11. Practical PHP
      1. 11.1. String Functions
        1. 11.1.1. Formatting Strings for Display
          1. 11.1.1.1. Using printf
          2. 11.1.1.2. Padding
          3. 11.1.1.3. Specifying precision
          4. 11.1.1.4. Using sprintf
        2. 11.1.2. Length
        3. 11.1.3. Changing Case
        4. 11.1.4. Checking for a String
        5. 11.1.5. Using String Position and Substring to Extract a Portion of a String
      2. 11.2. Date and Time Functions
        1. 11.2.1. Display Formats
        2. 11.2.2. Arithmetic
        3. 11.2.3. Validating Dates
        4. 11.2.4. Using mktime to Create a Timestamp
      3. 11.3. File Manipulation
        1. 11.3.1. Functions and Precautions
          1. 11.3.1.1. Permissions
          2. 11.3.1.2. Creating files
          3. 11.3.1.3. Deleting files
          4. 11.3.1.4. Moving files
        2. 11.3.2. URL Wrappers
        3. 11.3.3. Uploading Files
          1. 11.3.3.1. Accessing the file
          2. 11.3.3.2. Validation
      4. 11.4. Calling System Calls
      5. 11.5. Chapter 11 Questions
    14. 12. Modifying MySQL Objects and PHP Data
      1. 12.1. Changing Database Objects from PHP
        1. 12.1.1. Creating a Table
        2. 12.1.2. Dropping a Table
        3. 12.1.3. Errors Happen
      2. 12.2. Manipulating Table Data
        1. 12.2.1. Adding Data
      3. 12.3. Displaying Results with Embedded Links
      4. 12.4. Presenting a Form to Add and Process in One File
        1. 12.4.1. SQL Injection
        2. 12.4.2. Cross-Site Scripting Attacks
      5. 12.5. Updating Data
      6. 12.6. Deleting Data
        1. 12.6.1. Generating Unique Identifiers
      7. 12.7. Performing a Subquery
      8. 12.8. Chapter 12 Questions
    15. 13. Cookies, Sessions, and Access Control
      1. 13.1. Cookies
        1. 13.1.1. Setting a Cookie
        2. 13.1.2. Accessing a Cookie
        3. 13.1.3. Destroying a Cookie
      2. 13.2. PHP and HTTP Authentication
        1. 13.2.1. Storing a Username and Password in a Database
      3. 13.3. Sessions
        1. 13.3.1. Using Sessions
        2. 13.3.2. Expanding Our Login Example
        3. 13.3.3. Ending a Session
          1. 13.3.3.1. Garbage collection
          2. 13.3.3.2. Setting a session's timeout
      4. 13.4. Using Auth_HTTP to Authenticate
      5. 13.5. Chapter 13 Questions
    16. 14. Security
      1. 14.1.
        1. 14.1.1. Limit Access to Administrative Pages
        2. 14.1.2. Including Files
        3. 14.1.3. Storing Passwords in the Database
        4. 14.1.4. The Problem with Automatic Global Variables
      2. 14.2. Session Security
        1. 14.2.1. Session Hijacking and Session Fixation
        2. 14.2.2. Trusting User Data
        3. 14.2.3. Shared Hosting Concerns
        4. 14.2.4. Preventing Access to the Database
        5. 14.2.5. Blocking Access to the Database for External Hosts
        6. 14.2.6. Create Separate Database Users
        7. 14.2.7. Magic Quotes
      3. 14.3. Chapter 14 Questions
    17. 15. Validation and Error Handling
      1. 15.1. Validating User Input with JavaScript
      2. 15.2. Pattern Matching
        1. 15.2.1.
          1. 15.2.1.1. Quantifiers
          2. 15.2.1.2. Anchors
          3. 15.2.1.3. Character classes
          4. 15.2.1.4. Executing pattern matches in PHP
          5. 15.2.1.5. preg_match
      3. 15.3. Redisplaying a Form After PHP Validation Fails
      4. 15.4. Chapter 15 Questions
    18. 16. Sample Applications
      1. 16.1. Building a Blog
        1. 16.1.1. Configuration File
        2. 16.1.2. Page Framework
        3. 16.1.3. Database
          1. 16.1.3.1. Sample data
        4. 16.1.4. Displaying a Postings Summary
        5. 16.1.5. Displaying a Posting and Its Comments
        6. 16.1.6. Adding and Changing Posts
        7. 16.1.7. Adding and Changing Comments
      2. 16.2. Chapter 16 Questions
    19. 17. Finishing Your Journey
      1. 17.1. Finding Help on the Web
        1. 17.1.1. Comments
        2. 17.1.2. Formatting
          1. 17.1.2.1. Indenting
          2. 17.1.2.2. PHP tags
          3. 17.1.2.3. Templating
          4. 17.1.2.4. Expressions
          5. 17.1.2.5. Function calls
          6. 17.1.2.6. Function definitions
        3. 17.1.3. Objects
        4. 17.1.4. Naming
        5. 17.1.5. Control Structures
          1. 17.1.5.1. Including or requiring PHP files
      2. 17.2. Web Sites
        1. 17.2.1. Chat and Listservs for PHP
        2. 17.2.2. PHP User Groups
      3. 17.3. Zend
      4. 17.4. Lampshade
      5. 17.5. Chapter 17 Questions
    20. A. Answers to Chapter Questions
      1. A.1. Chapter 1
      2. A.2. Chapter 2
      3. A.3. Chapter 3
      4. A.4. Chapter 4
      5. A.5. Chapter 5
      6. A.6. Chapter 6
      7. A.7. Chapter 7
      8. A.8. Chapter 8
      9. A.9. Chapter 9
      10. A.10. Chapter 10
      11. A.11. Chapter 11
      12. A.12. Chapter 12
      13. A.13. Chapter 13
      14. A.14. Chapter 14
      15. A.15. Chapter 15
      16. A.16. Chapter 16
      17. A.17. Chapter 17
    21. About the Authors
    22. Colophon
    23. SPECIAL OFFER: Upgrade this ebook with O’Reilly