PHP: The Good Parts

Chapter 7. Database Interaction

It would make little sense these days to have a static website that doesn’t change unless you physically alter the HTML or PHP of each file. There is usually a need to store and retrieve dynamic information as it relates to the content of a website or web application. In this chapter, we will look at how to make your pages draw some of their content from a database.

MySQLi Object Interface

The most popular database platform used with PHP is the MySQL database. If you look at the MySQL website you will discover that there are a few different versions of MySQL you can use. We will look at the freely distributable version known as the community server. PHP has a number of different interfaces to this database tool as well, so we will look at the object-oriented interface known as MySQL Improved extension (MySQLi). If you read the previous chapter on OOP with PHP, the use of this interface should not be overly foreign.

First, let’s use the very basic database schema I hinted at in the previous chapter by extending the example of a rudimentary guestbook page we started with. We’ll add the ability to actually save the entries into the database table. Here is the structure of the guests table:

table: guests
guestid  int(11)
fname      varchar(30)
lname      varchar(40)
comments text

And here is the SQL code to create it:

CREATE DATABASE 'website' ; USE 'website' ; CREATE TABLE 'guests' ( 'guestid' INT NOT NULL AUTO_INCREMENT PRIMARY KEY , 'fname' VARCHAR( 30 ) NOT NULL , ...