9     Databases

This is NOT the latest copy of this book; click here for the latest version.

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 4 of MySQL. This is no great loss, because MySQL 4 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. Having said all that, there are a number of things that are not supported in the stable release of MySQL, so this chapter also looks at how to use Microsoft SQL Server 2000 where MySQL falls down.

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

9.1. Introduction

9.1.1. Database hierarchy
9.1.2. Types of data
9.1.3. Date and time
9.1.4. Transactions
9.1.5. Stored procedures
9.1.6. Triggers
9.1.7. Views
9.1.8. Keys
9.1.9. Referential integrity
9.1.10. Indexes
9.1.11. Persistent connections
9.1.12. Temporary Tables
9.1.13. Table handlers
9.1.14. Round up

9.2. History

9.2.1. MySQL
9.2.2. PostgreSQL
9.2.3. Oracle
9.2.4. Microsoft SQL Server

9.3. SQL

9.3.1. SQL comments
9.3.2. Interacting with MySQL
9.3.3. Creating tables
9.3.4. Making table changes
9.3.5. Deleting tables
9.3.6. Inserting data
9.3.7. Selecting data
9.3.8. Extra SELECT keywords
9.3.9. Updating data
9.3.10. Deleting data
9.3.11. MySQL for dummies
9.3.12. A working example
9.3.13. Multiple WHERE conditions
9.3.14. Grouping rows together with GROUP BY
9.3.15. MySQL functions
9.3.16. Managing indexes
9.3.17. Simple text searching using LIKE
9.3.18. Advanced text searching using full-text indexes
9.3.19. Range matching: between() and in()
9.3.20. NULL
9.3.21. Default values

9.4. Using MySQL with PHP

9.4.1. Connecting to a MySQL database: mysql_connect(), mysql_select_db()
9.4.2. Querying and formatting: mysql_query() and mysql_num_rows()
9.4.3. Disconnecting from a MySQL database: mysql_free_result() and mysql_close()
9.4.4. Reading in data: mysql_fetch_assoc()
9.4.5. Mixing in PHP variables: mysql_escape_string()
9.4.6. Results within results
9.4.7. Advanced formatting
9.4.8. Reading auto-incrementing values: mysql_insert_id()
9.4.9. Unbuffered queries for large data sets: mysql_unbuffered_query()

9.5. phpMyAdmin
9.6. PEAR::DB

9.6.1. Quick PEAR::DB calls
9.6.2. Query information
9.6.3. Advanced PEAR::DB
9.6.4. Impeared performance?

9.7. SQLite

9.7.1. Using SQLite
9.7.2. Before you begin
9.7.3. Getting started: sqlite_open(), sqlite_close(), sqlite_query(), and sqlite_fetch_array()
9.7.4. Advanced functions: sqlite_last_insert_rowid(), sqlite_fetch_single(), and sqlite_array_query()
9.7.5. Mixing SQLite and PHP: sqlite_create_function()

9.8. Normalisation

9.8.1. Why separate data?
9.8.2. So, what is the solution here?
9.8.3. Why not separate data?
9.8.4. First normal form
9.8.5. Second normal form
9.8.6. Other normal forms
9.8.7. Conclusion

9.9. Table joins

9.9.1. Complex joins

9.10. Using temporary tables
9.11. Adjusting the priority queue
9.12. How to design your tables
9.13. Picking the perfect data type
9.14. When MySQL knows best
9.15. Persistent connections: mysql_pconnect() and sqlite_popen()
9.16. Choosing a table type
9.17. Transactions
9.18. MySQL Improved
9.19. Subselects, views, and other advanced functions

9.19.1. Subselects
9.19.2. Views
9.19.3. Referential integrity

9.20. Summary
9.21. Exercises
9.22. Further reading
9.23. Next chapter