Chapter 11. Databases

I l @ ve RuBoard

Strange as it may sound to say, the Internet would not be where it is today if not for the existence of databases. In fact, PHP itself would probably not be as popular or as useful if not for its built-in support for numerous types of databases. (PHP's degree of database support and its ease of use are things you won't find using CGI scripts, as a rule).

A database is a collection of tables (tables being made up of columns and rows) that stores information. Databases are used all over the Internet. E-commerce sites use databases to keep product specifications (such as price and color ) as well as customer data, while content sites put articles and news stories into databases.

There are currently many existing database servers or Database Management Systems (DBMS), which function on different platforms. (Technically a DBMS is the software that interfaces with the database proper. However, more and more people are using the terms database and DBMS synonymously. I will continue to distinguish between the two to avoid confusion.) On any operating system, Oracle is generally considered the best DBMS, but its price puts it out of the running for all but the largest and best-financed applications. For Windows and Windows NT, you'll often encounter Access or SQL Server, both of which are very useful but not cross-platform compliant.

In this chapter, I'll use MySQL (Figure 11.1) as my example DBMS. Although MySQL, which is available for most platforms, is not as powerful as other database servers, it has enough speed and functionality for most purposes and its price ”free for Unix servers ”makes it the most common choice for Web development. If you are using a server provided by an ISP or Web host, check to see what DBMS they have available for you (most likely at an extra cost). If you are running your own server, you should consider installing MySQL (www.MySQL.com or another database server).

Figure 11.1. MySQL can be obtained from the MySQL home page (www.MySQL.com). Here you can also find documentation on installing and using MySQL as well as check for any applicable licensing fees.

Databases are created, updated, and read using SQL (Structured Query Language). There are surprisingly few commands in SQL, which is both a blessing and a curse. SQL was designed to be written a lot like the English language, which makes it very user friendly, but it does take some thought to create more elaborate SQL statements with only the handful of available terms. Be aware that incorporating a database into your Web applications creates more potential for errors, so check and double-check your database work as you go.

I'll lead you through how to develop one simple database that records user feedback. Although you will learn enough here to get yourself started, you may want to visit Appendix C, PHP Resources, once you've finished this chapter to find some references where you can learn more about the topic.

Since I will be working with MySQL, all of the functions I use in this chapter will be MySQL specific. For example, to connect to a database in MySQL, the proper function is mysql_connect(), but if you are using PostgreSQL, you would instead write pg_connect(). If you are not using a MySQL DBMS, you will need to use the PHP manual (available through www.PHP.net) to find the appropriate function names .

I l @ ve RuBoard