Databases

Many people believe database access in PHP is its most important feature, and the PHP team have indeed made it as easy as they can to interact with databases using the language. I think it is fair to say that a PHP developer who has yet to come into contact with databases really has only touched the tip of the PHP iceberg!

In this chapter we will start off with a brief description of what makes up a database, what is provided by a database manager (the program that you use to interact with the database), and a history of the most popular database manager. In the section entitled SQL you will learn how to create and store information in a database using the Structured Query Language, and also how to get it out again just as you like it. We'll also be extensively covering how to interact with your database manager using PHP, how to format your data, and much more - this is a big chapter!

In order to be able to get into serious depth on this topic, we're going to be looking specifically at the MySQL database manager - more precisely, version 5 of MySQL. This is no great loss, because MySQL 5 is the most popular open-source database in existence, so chances are you will be using it. Although we will be sticking fairly closely to MySQL, much of what we cover here will apply to other databases easily.

It is important to note that you do not need to know everything covered here - normalisation, for example, is helpful to know if you really want to perfect your database skills, but you can wing it without such knowledge. Similarly, you can skip over the detailed information on the PEAR database system if you have no intention to use it.

Topics covered in this chapter are:

  • What makes a database

  • What databases are available

  • SQL commands using MySQL

  • Connecting to MySQL through PHP

  • Using PEAR::DB for database abstraction

  • SQLite for systems without a database system

  • Normalisation and table joins

  • Table design considerations

  • Persistent connections and transactions


Chapter contents

  1. 9.1. Introduction
    1. 9.1.1. Database hierarchy
    2. 9.1.2. Types of data
    3. 9.1.3. Date and time
    4. 9.1.4. Transactions
    5. 9.1.5. Stored procedures
    6. 9.1.6. Triggers
    7. 9.1.7. Views
    8. 9.1.8. Keys
    9. 9.1.9. Referential integrity
    10. 9.1.10. Indexes
    11. 9.1.11. Persistent connections
    12. 9.1.12. Temporary Tables
    13. 9.1.13. Table handlers
    14. 9.1.14. Round up
  2. 9.2. History
    1. 9.2.1. MySQL
    2. 9.2.2. PostgreSQL
    3. 9.2.3. Oracle
    4. 9.2.4. Microsoft SQL Server
  3. 9.3. SQL
    1. 9.3.1. SQL comments
    2. 9.3.2. Interacting with MySQL
    3. 9.3.3. Creating tables
    4. 9.3.4. Making table changes
    5. 9.3.5. Deleting tables
    6. 9.3.6. Inserting data
    7. 9.3.7. Selecting data
    8. 9.3.8. Extra SELECT keywords
    9. 9.3.9. Updating data
    10. 9.3.10. Deleting data
    11. 9.3.11. MySQL for dummies
    12. 9.3.12. A working example
    13. 9.3.13. Multiple WHERE conditions
    14. 9.3.14. Grouping rows together with GROUP BY
    15. 9.3.15. MySQL functions
    16. 9.3.16. Managing indexes
    17. 9.3.17. Simple text searching using LIKE
    18. 9.3.18. Advanced text searching using full-text indexes
    19. 9.3.19. Range matching
    20. 9.3.20. Working with NULL
    21. 9.3.21. Default values
  4. 9.4. Using MySQL with PHP
    1. 9.4.1. Connecting to a MySQL database
    2. 9.4.2. Querying and formatting
    3. 9.4.3. Disconnecting from a MySQL database
    4. 9.4.4. Reading in data
    5. 9.4.5. Mixing in PHP variables
    6. 9.4.6. Results within results
    7. 9.4.7. Advanced formatting
    8. 9.4.8. Reading auto-incrementing values
    9. 9.4.9. Unbuffered queries for large data sets
  5. 9.5. phpMyAdmin
  6. 9.6. PEAR::DB
    1. 9.6.1. Quick PEAR::DB calls
    2. 9.6.2. Query information
    3. 9.6.3. Advanced PEAR::DB
    4. 9.6.4. Impeared performance?
  7. 9.7. SQLite
    1. 9.7.1. Using SQLite
    2. 9.7.2. Before you begin
    3. 9.7.3. Getting started
    4. 9.7.4. Advanced functions
    5. 9.7.5. Mixing SQLite and PHP
  8. 9.8. Normalisation
    1. 9.8.1. Why separate data?
    2. 9.8.2. So, what is the solution here?
    3. 9.8.3. Why not separate data?
    4. 9.8.4. First normal form
    5. 9.8.5. Second normal form
    6. 9.8.6. Other normal forms
    7. 9.8.7. Conclusion
  9. 9.9. Table joins
    1. 9.9.1. Complex joins
  10. 9.10. Using temporary tables
  11. 9.11. Adjusting the priority queue
  12. 9.12. How to design your tables
  13. 9.13. Picking the perfect data type
  14. 9.14. When MySQL knows best
  15. 9.15. Persistent connections
  16. 9.16. Choosing a table type
  17. 9.17. Transactions
  18. 9.18. MySQL Improved
  19. 9.19. Subselects, views, and other advanced functions
    1. 9.19.1. Subselects
    2. 9.19.2. Views
    3. 9.19.3. Referential integrity
  20. 9.20. Summary
  21. 9.21. Exercises
  22. 9.22. Further reading
  23. 9.23. Next chapter

Next chapter: Introduction >>

Previous chapter: Next chapter

Jump to:

 

Home: Table of Contents

Follow us on Identi.ca or Twitter

Username:   Password:
Create Account | About TuxRadar