Chapter 3: Building a Database Schema with MySQL

One of the most important elements to building a website with PHP and MySQL is the database. MySQL is a robust database server that supports a wide range of features. With MySQL you have the ability to insert, update, drop, index, replicate (file), and lock tables, and much more. If you build your database properly and configure your tables accordingly, you should have no performance problems running your site from a MySQL back end. After reading this chapter, you will be able to use MySQL and some of the associated third-party software for MySQL management.

Before you go any further in this chapter, you should grab a copy of MySQL and install it. You can download MySQL at http://www.mysql.com/ for free. I recommend you download all of the associated files for your operating system with the MySQL base installation so that you receive the MySQL Admin Tools.
Understanding MySQL



To effectively plan and use MySQL, you need to know some basics about the different types of tables and columns you will use in building your databases. Using tables and columns properly will ensure that your databases run smooth and efficiently. Let's look at tables first.
MySQL Table Types

MySQL supports a few different types of tables for using with your databases. As of MySQL version 3.23, you can choose from HEAP, ISAM, and MyISAM table types. Let's take a quick look at the differences:

HEAP This table type uses hashed indexes that are stored in memory, which makes them very fast. However, if MySQL crashes, you will lose all the data stored in them. Thus, this table type is great for temporary tables!

ISAM This type of table will be deprecated in future versions of MySQL. Furthermore, it has no major advantages over the MyISAM table type. The following are some of the properties and features of ISAM:

Compressed and fixed-length keys.

Fixed and dynamic record length.

Sixteen keys with 16 key parts per key.

Maximum key length of 256 (default).

Data is stored in machine format; this is fast, but it is machine/operating system dependent.

MyISAM This is the default table type and is compatible with nearly any requirements you may have for a database. It is based on the ISAM code and has many more useful extensions, including error checking, error repair, compression, and much more.

Furthermore, you can compile MySQL with additional table support such as InnoDB and BDB (BerkeleyDB). Please check the MySQL manual for information regarding these table types. Note
Throughout this book, I will use MyISAM table types by default.

MySQL Column Types

Before you begin building your database structure, you should understand the column types and the purposes for which they are used. These are the most commonly used column types:

TINYINT A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.

SMALLINT A small integer. The signed range is -32,768 to 32,767. The unsigned range is 0 to 65,535.

MEDIUMINT A medium-sized integer. The signed range is -8,388,608 to 8,388,607. The unsigned range is 0 to 16,777,215.

INT A normal-sized integer. The signed range is -2,147,483,648 to 2,147,483,647. The unsigned range is 0 to 4,294,967,295.

BIGINT A large integer. The signed range is -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

FLOAT A floating-point number. Precision can be less than or equal to 24 for a single-precision floating-point number and between 25 and 53 for a double-precision floating-point number.

DOUBLE A normal-sized (double-precision) floating-point number. Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, zero, and 2.2250738585072014E-308 to 1.7976931348623157E+308.

DECIMAL An unpacked floating-point number.

DATE A date. The supported range is 1000-01-01 to 9999-12-31.

DATETIME A date and time combination. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

TIMESTAMP A timestamp. The range is '1970-01-01 00:00:00' to sometime in the year 2037.

TIME A time. The range is '-838:59:59' to '838:59:59'.

YEAR A year in two-digit or four-digit format (the default is four-digit format). The allowable values are 1,901 to 2,155.

VARCHAR A variable-length string.

TINYBLOB or TINYTEXT A BLOB or TEXT column with a maximum length of 255 (2^8 - 1) characters.

BLOB or TEXT A BLOB or TEXT column with a maximum length of 65,535 (2^16 - 1) characters.

MEDIUMBLOB or MEDIUMTEXT A BLOB or TEXT column with a maximum length of 16,777,215 (2^24 - 1) characters.

LONGBLOB or LONGTEXT A BLOB or TEXT column with a maximum length of 4,294,967,295 (2^32 - 1) characters.

ENUM An enumeration. A string object that can have only one value, chosen from the list of values 'value1', 'value2', ..., NULL, or the special '' error value. An ENUM can have a maximum of 65,535 distinct values.

This list contains the most commonly used column types for your purposes. There are many more column types, and you can find a complete list in the MySQL manual at www.mysql.com/ documentation. Do not worry if you do not understand these column types yet. Throughout the database design in this book, I will give you practical examples of the column types you will need. Note
When working with MySQL, you may notice in various places the terms column and field. A column relates to the database table structure, and a field relates to data stored for a particular column in a row, which is stored inside the table.

What Is ADOdb?

ADOdb is a set of classes that attempts to hide the differences between the nonstandardized PHP database access functions. It is most commonly used in applications such as PHAkt (a third-party Macromedia Dreamweaver extension) and the PostNuke content management system.

ADOdb currently supports the following database types:

MySQL

Oracle

Microsoft SQL Server

Sybase

Sybase SQL Anywhere

Informix

PostgreSQL

FrontBase

Interbase (Firebird and Borland variants)

Foxpro

Access

ADO

ODBC

If you are interested in learning more about ADOdb, refer to the official website at php.weblogs.com/adodb.

Using MySQL Database Tools

The best part about working with excellent software such as MySQL is that someone is always working on third-party software to make your life better. In my experience, I have found some great third-party software applications and web-based scripts that allow me to create, delete, modify, and manage elements of my MySQL servers fairly easily without the need to learn command-line arguments for each action I take. This chapter will not cover how to manage MySQL from the command line, but it will show you some great point-and-click software that will make your life much easier. Warning

When managing a MySQL Server with root privileges, do not delete, or drop, the database named mysql. This database contains all of the necessary information to run your MySQL server. Deleting this database will render MySQL inoperative.

Using phpMyAdmin: Web-Based MySQL Administration

What better way to manage the MySQL portion of your PHP/MySQL website than using a set of PHP scripts with an excellent interface? The folks over at phpMyAdmin.net have created a set of scripts that you download and unpack (unzip or untar) on your website's directories. Simply open your web browser and point to the directory where you unpacked the files, and you now have an easy way to manage your MySQL.

Even if you do not plan to use phpMyAdmin, I encourage you to read this section to get a better understanding of different column types and why you use them.

Let's begin by grabbing phpMyAdmin:

Go to http://www.phpmyadmin.net/ and pick the file format of your choice. Download the files either directly to your web server or to your local computer.

Unpack the files and place them in your website's directory. For this example, I have placed my copy of phpMyAdmin in a directory called phpMyAdmin in my website's document root.

Modify the file called config.inc.php with your PHP editor and follow the directions for entering your MySQL server information, usernames, passwords, and so on. See Figure 3.1 where I configured the config.php file for the $cfg['PmaAbsoluteUri'] setting and Figure 3.2 where I configured the username and password in the phpMyAdmin config.php file.

Figure 3.1: Configuring phpMyAdmin's config.inc.php file for the PmaAbsolueURI settings



Figure 3.2: Configuring phpMyAdmin's config.inc.php file for username and password settings

4.Open your web browser and type the address to the phpMyAdmin files. For example, because I created a directory called phpMyAdmin on my web server's document root, I go to www.mysite.com/phpMyAdmin in my web browser (see Figure 3.3).



Figure 3.3: Introduction screen of phpMyAdmin

Warning

phpMyAdmin has various types of authentication. The method used in this chapter is a direct authentication method where you do not have to enter a username and password to access phpMyAdmin. Ensure that you protect your server by password protecting the phpMyAdmin directory with your web server or use the cookie-based authentication for the auth_type directive.

While you are in the main screen, you will have options to select your databases via the drop-down menu on the left panel, create new databases in the right (main) panel to view statistics, reload your MySQL server, and so on.

Creating Databases Using phpMyAdmin

Probably the first thing you will do when designing the database is to create one. In phpMyAdmin, this is simple. In the right window, simply type your database name and then click the Create button. For now, let's create a database named mydb (see Figure 3.4).



Figure 3.4: Creating a new database in phpMyAdmin

Once you have created your database, you will see a new screen that allows you to create tables in your database. Let's create a new table called news_articles and add five columns to it (see Figure 3.5).




Figure 3.5: Creating a new table for a database in phpMyAdmin

On the next screen, you will be able to give your columns names and types for this database (see Figure 3.6).



Figure 3.6: Configuring fields for the newly created table in phpMyAdmin

After you have entered your column information, click Save; you will see the screen in Figure 3.7.



Figure 3.7: Confirmation screen for creating a new table in phpMyAdmin

Let's take a moment to discuss the column types used in this example. This will give you an opportunity to explore the column types you learned about earlier in this chapter.

news_articles.article_id

The first column is called article_id. I created this column to give each article a unique and simple method of identification: an ID number. This field is a medium integer (number) with a length of 25 values. When you assign a length to a column, it does not mean that you can only enter up to that length in numbers or characters; however, it means that the length of the field can be up to 25 numbers long, for example: 9999999999999999999999999.

This column also has an auto_increment value assigned to it. MySQL has the ability to automatically increment the row numbers for you. In other words, every time you make a new database entry, a unique value will be assigned to the new row. This value will be incremented by one each time a new row is entered into the database, for example, 1, 2, 3, 4….

The last important element about this column is that it is the primary key for this table. A key is used as a method to index the information inside the table. You can have more than one key, and the keys can be primary or unique. For this example, you will stick to using the primary key and assign it to this column.
news_articles.article_title

When I planned this table in my database, I decided I would need a column to store the title of the article. I then decided that each title should be fewer than 255 characters, and I chose the VARCHAR type of column for it.

VARCHAR stands for variable characters. VARCHAR columns are great because they can store simple information, and they also truncate the values if you exceed the maximum limit of 255 characters. Also, another value of using VARCHAR is that the trailing spaces are trimmed from the data entered.
news_articles.article_date

A great method to keep track of your dates and times is to use the DATE or DATETIME column type. In the article_date column, for example, I used DATETIME, which will return a value such as YYYY-MM-DD HH:MM:SS. For example, 2003-09-22 13:30:01 would be September 22, 2003, at 13:30:01 in the afternoon.

During the data insertion, you can use a MySQL function called now() to enter the current date and time information into a column. You will use this function quite often in this book, so keep your eyes open for it!
news_articles.article_caption

During the planning phase for this table, I decided I would have a caption on the front page of my website where the articles would appear. Then, when the user clicks a link, I would display the full article from the database. Using the TEXT column type, I can enter up to 65,535 characters for my caption. This may be too many characters, but that is okay because it is a lot better than having data truncated when using the TINYTEXT column, which has a maximum value of 255 characters.
news_article.article_body

For my article body, the column that stores the full article, I wanted to ensure that I would not run out of space if I wanted to write a really long article. So, I decided to use a LONGTEXT column. The LONGTEXT column allows 4,294,967,295 characters. MySQL has field types for every usage scenario, so you will not have any problems with space when designing your applications either.
Adding Additional Table Fields in phpMyAdmin

The greatest feature of phpMyAdmin is flexibility. You never know when you will need to go back and add, delete, or alter tables and fields. Let's look at how to add additional columns to the table you created in the previous section. Follow these steps:

Open phpMyAdmin and choose the database you created from the drop-down menu on the left column. You'll see the screen shown in Figure 3.8.



Figure 3.8: Database properties screen in phpMyAdmin

Click the Properties link next to the table name. You will see a new screen that shows you the columns for that table. Toward the bottom of your screen, you will see a section that looks like Figure 3.9.



Figure 3.9: Adding new columns to an existing table in phpMyAdmin

3.In the Add New Field text box, change the number to however many fields you want to add and then select where you want to add the columns from the drop-down menu. For this example, let's add one new field at the end of the table. Click the Go button, and you will see the screen in Figure 3.10.



Figure 3.10: Naming new columns in phpMyAdmin

In Figure 3.10, I added a new VARCHAR column named article_author and assigned it a 255-character length. Once again, I value those VARCHAR column types. Click the Save button, and you'll see Figure 3.11.



Figure 3.11: Verification screen for creating new columns in phpMyAdmin

Now you should have a good understanding of how to create new columns with phpMyAdmin. Next, you will learn how to change and drop fields.
Changing and Dropping Tables and Fields with phpMyAdmin

Sometimes you just have to modify your database. The authors at phpMyAdmin.net recognize that and have incorporated ways for you to alter your table structures rather easily. Let's see how!

For this example, let's create a new table with two columns to it:

1.Open phpMyAdmin and select the database from the left drop-down menu. Below the current table information you will see a Create a New Table on Database mydb box, as shown in Figure 3.12.



Figure 3.12: Creating a new table to an existing database in phpMyAdmin

Enter junk in the Name box and 2 in the Fields box and then click Go. You should be in familiar territory if you followed the steps for creating a table and columns earlier in this chapter. For the first column, enter junk1 as the name, select INT as the column type, select 25 as the length values, select AUTO_INCREMENT for the EXTRA section, and select Primary Key.

In the second column, enter the name junk2, select VARCHAR as the column type, and enter 255 for the column length. See Figure 3.13 for this table setup.



Figure 3.13: Naming new fields for a new table in phpMyAdmin

4.Finally, click the Save button, and you should see the acknowledgement screen that displays the new table.

Now that you have the junk table created, you can practice changing column names, dropping columns, and finally dropping the entire table.

Let's start by opening your database from scratch. Open your web browser, go to phpMyAdmin, and select your database from the left drop-down menu. When you have your database selected, you should now see two tables, junk and news_articles (see Figure 3.14).



Figure 3.14: Database properties screen in phpMyAdmin

Click the Properties link next to the junk table. Now you should see the detailed information for this column. Next to the junk2 column, let's click the Change hyperlink. You will see a screen similar to Figure 3.15.



Figure 3.15: Modifying field properties in phpMyAdmin

In this screen, you can change the name, column type, and so on. Let's change the name from junk2 to junk3, set the Length/Values to 155, and then click Save (see Figure 3.16).



Figure 3.16: Table alteration verification screen in phpMyAdmin

As you may notice, the column has been modified. You are definitely on the right track now! Let's see how to drop a column.

Because this screen is similar to the properties screen, click the Drop hyperlink next to the junk3 column. This will delete the column after you confirm your intentions (see Figure 3.17).



Figure 3.17: Alter table verification message in phpMyAdmin

Click OK to confirm your deletion and refresh your screen. You should see that the junk3 column has disappeared, as shown in Figure 3.18.



Figure 3.18: You have successfully deleted junk3.

Now that you know how to manage columns, you will learn how to drop tables. Open your database from scratch again, and you'll see the two tables, junk and news_articles. Next to the junk table, click the Drop hyperlink, and then you will be asked if you want to drop the table junk (see Figure 3.19). Click the OK button to confirm, and your screen will refresh with only the news_articles column visible. You have successfully deleted your junk table!



Figure 3.19: Drop table verification message in phpMyAdmin

Now that you have mastered the ability to create, change, and delete tables and columns, the last cool thing I will show you with phpMyAdmin is how to back up and restore entire databases.

Performing Backups and Restores with phpMyAdmin

Let's face it-you never know when you are going to need a good backup and restore of your database. I generally make it a point to create a backup on a live site before I do any modifications to my database. You never know when the server is going to take a "lunch break" on you and cause havoc on your website. It's a good feeling to know you have something standing by to fix your problems.

To make a backup, follow these steps:

Open phpMyAdmin.

Select your database from the drop-down menu on the left panel, and you will see your database open on the right panel.

Click the Export hyperlink, as shown in Figure 3.20. You'll see a new page that has the export options in Figure 3.21.



Figure 3.20: Export options tab for phpMyAdmin



Figure 3.21: phpMyAdmin export options

phpMyAdmin has many different export options. The following are the major ones:

Structure Only This allows you to back up on the structure of the database and not the data.

Structure and Data Create a full backup of the database including structure and data.

Data Only Only back up the data, not the structure.

Add 'Drop Table' This is useful when performing a restore on an existing database. It will drop or delete the tables and data before rebuilding the database with the backup file. It is important to select this option if you want to perform a restore. If you are going to dump this backup into a new server, you do not have to select this option; however, I recommend it anyway.

Save As File If you select this option you will be prompted to download the database backup as a file. If you chose to compress the file, your backed-up file will be in the compression format that you choose.

Now that you better understand these options, let's perform a simple backup of the structure and data. Select the following options: Structure and Data, Add 'Drop Table,' and Save As File (see Figure 3.22). You will be prompted to download the file to your hard drive (see Figure 3.23).



Figure 3.22: Your phpMyAdmin export options



Figure 3.23: phpMyAdmin download export file

Tuck this file away in a safe place because you will use it again in a moment when you restore the database.

To restore a database, follow these steps:

Select your database from the drop-down menu.

In the database screen, click the SQL hyperlink, as shown in Figure 3.24.



Figure 3.24: phpMyAdmin SQL options tab

3.You'll see a screen similar to Figure 3.25. This screen allows you to copy and paste a MySQL query directly or select a file that contains a query from your hard drive



Figure 3.25: phpMyAdmin SQL options page

4.Click the Browse button and select the file from your hard drive that you saved in the backup procedure. Next, click the Go button to initiate the upload and query process on the file you chose.

Once you have uploaded your file, you should see the query performed on the next screen. Thus, the restore will be complete (see Figure 3.26).



Figure 3.26: phpMyAdmin SQL query success message

This section has covered the basic tasks involved in managing your MySQL database with phpMyAdmin. Although it only touched on the potential of what phpMyAdmin can do with MySQL, you are off to a good start. I recommend you explore this awesome application as much as possible; you will find that there are many more features that phpMyAdmin has to offer.

SQLyog MySQL Manager for Windows

I love Linux, and I attempt to use it every chance I get; however, working on Windows makes life much easier-when it works properly. I am extremely happy that I have found SQLyog to manage MySQL easily through my Windows Desktop.

SQLyog is an application developed by the folks over at Webyog.com. It was designed to be lightweight, super fast, and user friendly, and I believe the authors have achieved their goals. You can download SQLyog at www.webyog.com/sqlyog/.

After you have downloaded SQLyog, all that you have to do to install it is click the file; it will set itself up like many other Windows Installer applications do.

When you run SQLyog for the first time, you will see the Connection Manager (see Figure 3.27). From this screen, you will be able to manage connections to multiple servers and multiple usernames and passwords. Simply enter the connection information for your server, and click Save to save the information for later use or click Connect to connect using your settings.



Figure 3.27: SQLyog Connection Manager

Note

It is important you understand how to create MySQL connections to remote servers and user permissions. Usually, when a hosting provider configures a user account, it does not allow connections outside of the "localhost" environment. If you are attempting to connect to a remote server, you must allow the user you are connecting with to use remote connections. Please refer to the MySQL manual to get a better understanding of this.

Once you have made a connection, you will see the main screen for SQLyog. This screen provides you with a list of databases, information screens on the right, and more (see Figure 3.28).



Figure 3.28: SQLyog main screen

Creating Databases with SQLyog

Creating a new database with SQLyog is simple. Press Ctrl+D on your keyboard, and a new window will appear. Enter the database name you want and then click OK. For this example, I will create the database named mydb. If you followed the same steps in the previous section with phpMyAdmin, you may want to name yours mydb2 or something similar (see Figure 3.29).



Figure 3.29: SQLyog Create Database screen

After you click the OK button, you will see your database appear in the list of databases on the left panel (see Figure 3.30).



Figure 3.30: SQLyog database list panel

Now that you have your database created, you will add a new table to it. Click the database in the list and press the Insert key on your keyboard. A new window will appear, and you can now enter your column information in this new window (see Figure 3.31).



Figure 3.31: SQLyog field setup screen

You will now set up this table the same as you did with news_articles using phpMyAdmin. Table 3.1 lists the table fields.
Table 3.1: Field Setup for the articles Table

Table 3.1: Field Setup for the articles Table

Field Name	Data Type	Length	Extra
article_id	MEDIUMINT	25	Primary key, auto increment
article_title	VARCHAR	255
article_date	DATETIME
article_caption	TEXT
article_body	LONGTEXT
article_author	VARCHAR	255

If you have any questions about these field types, please refer to the "Using phpMyAdmin: Web-Based MySQL Administration" section earlier in this chapter.

After you are done entering your field information, click the Create Table button at the bottom-left corner of this window (see Figure 3.32). You will be prompted for a table name, so enter news_articles, as shown in Figure 3.33.

If you have any questions about these field types, please refer to the "Using phpMyAdmin: Web-Based MySQL Administration" section earlier in this chapter.

After you are done entering your field information, click the Create Table button at the bottom-left corner of this window (see Figure 3.32). You will be prompted for a table name, so enter news_articles, as shown in Figure 3.33.



Figure 3.32: SQLyog field setup screen actions



Figure 3.33: SQLyog new table name entry screen

After you have entered your table name and clicked the OK button, you should see a success message, as shown in Figure 3.34. After you click OK on this window, you will have the ability to add more tables, as shown in Figure 3.35. Simply click No on this window, and you will be back in the main screen of SQLyog.



Figure 3.34: SQLyog new table success message



Figure 3.35: SQLyog prompt message

Modifying Tables with SQLYog

A major strength of SQLyog is the ability to perform multiple actions from one screen. When you open SQLyog and make a connection to the MySQL server, you will see your database list on the left. Double-click the database you want to work with, and you will notice the tables list expand below the database name (see Figure 3.36).



Figure 3.36: SQLyog database table listings

If you click the table you want to modify, press the F6 key or right-click, and then choose Alter Table Structure, you will see a screen that allows you to perform multiple tasks such as adding new fields and dropping fields from the same screen (see Figure 3.37).



Figure 3.37: SQLyog modify fields screen

To add a new field to your table, simply enter the information for the new field on the first empty row and click the Alter Table button on the bottom-left corner of this window.

If you need to modify a field, simply make the changes and click the Alter Table button on the bottom-left corner of this window.

If you would like to delete or "drop" a field, click the field name and click the Drop Field button on the lower button bar. You will be prompted to acknowledge the deletion of a table before the program carries out the action.

That is all there is to modifying table structures in SQLyog! It is pretty easy to use and very user friendly. The authors have really planned this software well, and they have taken into account ways to make working with your databases easier and quicker!

Performing a Database Backup with SQLYog

This application would not be complete without a method of creating database backups. It is really simple to back up your database, as well! All you have to do is open SQLyog and make a connection to the server. Once in the main screen, right-click your database and select Export Database As Batch Scripts. You will see the window shown in Figure 3.38.



Figure 3.38: SQLyog Export Data screen

From this window, you have to give the file a name and a location to save to in the Export to File box. You can leave everything else as the default. When you click the Export button at the bottom, a new file will be saved on your hard drive. The file should look something like this:

/*
SQLyog v3.11
Host - localhost : Database - mydb
**************************************************************
Server version 3.23.55-nt
*/

create database if not exists `mydb`;

use `mydb`;

/*
Table struture for news_articles
*/

drop table if exists `news_articles`;
CREATE TABLE `news_articles` (
`article_id` mediumint(25) NOT NULL auto_increment,
`article_title` varchar(255) default NULL,
`article_date` datetime default NULL,
`article_caption` text,
`article_body` longtext,
`article_author` varchar(255) default NULL,
PRIMARY KEY (`article_id`)
) TYPE=MyISAM;

This file is compatible with phpMyAdmin and command-line tools because it is a simple set of commands that will build the database structure.

Note

SQLyog does not provide any dialog boxes after the export procedure completes. You will see the message "Exporting of data successful. Total Time Taken = nnn ms" directly above the Export and Cancel buttons in the Export Data window. You must manually close the Export Data dialog box after you see this message.

If you would like to restore a database from a batch file, simply select Tools ‚ Execute Batch Script from the menu. Browse to the batch file you created in the previous step and click the Execute button. You will see a status message appear in the window, as shown in Figure 3.39, which means you are done!



Figure 3.39: SQLyog Execute Query(s) from a File screen

Note

SQLyog does not provide any dialog boxes upon completion of the restore process. You will see a message similar to "N Query(s) Executed Successfully. Total Time Take = n ms." You must close the Execute Query(s) from a File window manually.

This section covered the basics of using SQLyog, including creating databases, adding tables, adding and modifying columns, exporting backups, and restoring backups to your database. I encourage you to learn more about this excellent application for managing your MySQL databases.

Connecting to MySQL Databases with PHP

Now that you know how to create a MySQL database with some third-party applications, it is time to make a connection to the database using PHP. The following sections discuss how to make a connection, the differences between a persistent and nonpersistent connection, and how to set up a global file that you will include throughout your website to establish the connection to your database for you

Persistent and Nonpersistent MySQL Connections

What are the persistent and nonpersistent connections? With PHP, you have a few different methods to connect to your database. How you want to manage these connections is entirely up to you as the developer to choose. You may share the database connections with persistent connections, or you may close them and open new connections on demand with nonpersistent connections. Let's take a look at the differences.

Persistent Connections (mysql_pconnect)

I prefer to use this method of connecting to the MySQL server with all of my websites. With a persistent connection, PHP will first check for a connection that has already been established using the same username and password as the same host. If one is found and it is not currently being used by anyone else, PHP will pick up that connection and use it. When PHP is done with the connection, it will return it to the "pool" and free it up until it is needed again instead of closing the connection.

There are major advantages of using persistent connections, mainly efficiency. However, you should be aware that if you are limited to any number of persistent connections and your server's workload is high, if you exceed your connection limits, your script may not be able to connect and an error message will be displayed.

Note

If you have any questions about persistent connections, please refer to Chapter 21 of the PHP manual.

Nonpersistent Connections (mysql_connect)

A nonpersistent connection will be opened when the mysql_connect function is called and will remain open until the script has completed execution or until you use the mysql_close function to close the resource identifier. A nonpersistent connection simply opens the connection, performs the operations, and closes when the script is done with it.

Making the Connection

Now that we have discussed the types of connections, let's make one with PHP now. For this example, assume that your server is going to have an address of localhost, and your username will be root with a password of password. You can also assume that you are using the database that you created in the previous examples of this chapter, mydb. Let's check out the PHP code:

$sql = mysql_pconnect('localhost', 'root', 'password');
mysql_select_db('mydb', $sql) or die (mysql_error());
?>

Let's break down this code into sections.

First, you started the PHP engine to parse the code with the
mysql_pconnect - Open a persistent connection to a MySQL server Description resource mysql_pconnect ( [string server [, string username [, string password [, int client_flags]]]])

As you can see, you entered the server address for string server, the username for string username, and the password for string password in this function usage. Notice that you assigned a variable called $sql to the mysql_pconnect function. This simply returns a resource identifier to use in the mysql_select_db function.

Finally, you select a database with the mysql_select_db function. Let's look at this function usage:

mysql_select_db - Select a MySQL database Description bool mysql_select_db ( string database_name [, resource link_identifier])

Using the mysql_select_db function, you entered the database name mydb for the string database_name, and you used the $sql variable for the resource link_identifier. This allows you to use the persistent connection function with the select database function.

You can compound these two functions if you want. You achieve this similar to this example:

mysql_select_db('mydb', mysql_pconnect('localhost', 'root', 'password'))
or die (mysql_error());
?>

The last portion of this code I want to point out is the mysql_error function. This function, when used with or die, will terminate the script execution and display the error from the MySQL server regarding the connection attempt and failure. If no errors were detected, the script will execute as advertised without error messages.

Finally, save this file as database.php and place it inside a directory called includes in your web server's document root. You will use this file when you start performing MySQL queries in Chapter 5, "Creating a Website Membership System."