Practical PHP Programming:Databases
From IpbWiki
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
- 8.1. Practical_PHP_Programming:Introduction to databases
- 8.1.1. Practical_PHP_Programming:Database hierarchy
- 8.1.2. Practical_PHP_Programming:Database data types
- 8.1.3. Practical_PHP_Programming:Storing dates and times
- 8.1.4. Practical_PHP_Programming:Transactions
- 8.1.5. Practical_PHP_Programming:Stored procedures
- 8.1.6. Practical_PHP_Programming:Triggers
- 8.1.7. Practical_PHP_Programming:Views
- 8.1.8. Practical_PHP_Programming:Keys
- 8.1.9. Practical_PHP_Programming:Referential integrity
- 8.1.10. Practical_PHP_Programming:Indexes
- 8.1.11. Practical_PHP_Programming:Persistent connections
- 8.1.12. Practical_PHP_Programming:Temporary tables
- 8.1.13. Practical_PHP_Programming:MySQL table handlers
- 8.2. Practical_PHP_Programming:Some database history
- 8.3. Practical_PHP_Programming:Getting started with SQL
- 8.3.1. Practical_PHP_Programming:Comments in SQL
- 8.3.2. Practical_PHP_Programming:Interacting with MySQL
- 8.3.3. Practical_PHP_Programming:Creating tables
- 8.3.4. Practical_PHP_Programming:Changing existing tables
- 8.3.5. Practical_PHP_Programming:Deleting tables
- 8.3.6. Practical_PHP_Programming:Inserting data
- 8.3.7. Practical_PHP_Programming:Reading data from a table
- 8.3.8. Practical_PHP_Programming:Extra SELECT keywords
- 8.3.9. Practical_PHP_Programming:Updating data in a table
- 8.3.10. Practical_PHP_Programming:Deleting data from a table
- 8.3.11. Practical_PHP_Programming:MySQL for dummies
- 8.3.12. Practical_PHP_Programming:A working MySQL example
- 8.3.13. Practical_PHP_Programming:Multiple WHERE conditions
- 8.3.14. Practical_PHP_Programming:Grouping rows together
- 8.3.15. Practical_PHP_Programming:MySQL functions
- 8.3.16. Practical_PHP_Programming:Managing data indexes
- 8.3.17. Practical_PHP_Programming:Simple text searching
- 8.3.18. Practical_PHP_Programming:Advanced text searching
- 8.3.19. Practical_PHP_Programming:Matching ranges
- 8.3.20. Practical_PHP_Programming:The NULL value
- 8.3.21. Practical_PHP_Programming:Default field values
- 8.4. Practical_PHP_Programming:Using MySQL with PHP
- 8.4.1. Practical_PHP_Programming:Connecting to a database server with PHP
- 8.4.2. Practical_PHP_Programming:Querying and formatting
- 8.4.3. Practical_PHP_Programming:Disconnecting from a MySQL server
- 8.4.4. Practical_PHP_Programming:Reading data
- 8.4.5. Practical_PHP_Programming:Using PHP variables in SQL queries
- 8.4.6. Practical_PHP_Programming:Results within results
- 8.4.7. Practical_PHP_Programming:Advanced formatting
- 8.4.8. Practical_PHP_Programming:Reading auto-incrementing values
- 8.4.9. Practical_PHP_Programming:Using unbuffered queries for large datasets
- 8.5. Practical_PHP_Programming:Using phpMyAdmin
- 8.6. Practical_PHP_Programming:Introducing PEAR::DB
- 8.7. Practical_PHP_Programming:Introducing SQLite
- 8.8. Practical_PHP_Programming:Normalisation
- 8.8.1. Practical_PHP_Programming:Why separate data?
- 8.8.2. Practical_PHP_Programming:Getting started with normalisation
- 8.8.3. Practical_PHP_Programming:Why not separate data?
- 8.8.4. Practical_PHP_Programming:First normal form
- 8.8.5. Practical_PHP_Programming:Second normal form
- 8.8.6. Practical_PHP_Programming:Other normal forms
- 8.8.7. Practical_PHP_Programming:Normalisation conclusion
- 8.9. Practical_PHP_Programming:Table joins
- 8.10. Practical_PHP_Programming:Using temporary tables
- 8.11. Practical_PHP_Programming:Adjusting the priority queue
- 8.12. Practical_PHP_Programming:How to design your tables
- 8.13. Practical_PHP_Programming:Picking the perfect data type
- 8.14. Practical_PHP_Programming:When MySQL knows best
- 8.15. Practical_PHP_Programming:Using persistent connections
- 8.16. Practical_PHP_Programming:Choosing a table type
- 8.17. Practical_PHP_Programming:Transactions
- 8.18. Practical_PHP_Programming:MySQL Improved
- 8.19. Practical_PHP_Programming:Subselects, views and other advanced functionality