Connecting to MySQL and Selecting a Database

The first step when dealing with MySQLconnecting to the serverrequires the appropriately named mysqli_connect() function:

$database_connection = mysqli_connect ( 'host', 'user', 'password' );

The values you use in place of host, user, and password are determined from the users and privileges set up within the mysql database (see Chapter 2, "Running MySQL," for more information). Normally, the host will be localhost, but not necessarily. In any case, the most important rule about connecting to MySQL from PHP is this: use the same username/hostname/password combination that you would to connect using the mysql client.

The $database_connection variable is a reference point that PHP will use to the newly created connection (you can use another name for this variable, like just $dbc). Many of the other PHP functions for interacting with MySQL will take this variable as an argument.

Once you have connected to MySQL, you will need to select the database with which you want to work. This is the equivalent of saying USE databasename within the mysql client and is accomplished with the mysqli_select_db() function:

mysqli_select_db($dbc, "databasename" );

Alternatively, you can connect to MySQL and select the database in one fell swoop:

$dbc = mysqli_connect ( "host", "user", "password", "databasename" );

I'll start the demonstration of connecting to MySQL by creating a special file just for that purpose. Every PHP script that requires a MySQL connection can then include this file.

MySQL's PHP Connector

Because PHP and MySQL go so well together, the good people at MySQL provide updated versions of the mysql and mysqli libraries for PHP. The connector package is just a build of the MySQLrelated PHP functions, but using the latest versions of the MySQL client libraries (which PHP itself may or may not contain).

The connector will contain all bug fixes and support the latest MySQL features. You can download the latest connector package from the MySQL Web site. Note that it is only available for Windows and that installation instructions are included on the connector's download page.

To connect to and select a database:

Begin a new PHP document in your text editor (Script 7.1).

<?php

Add the appropriate comments.

// ***** mysql_connect.inc.php *****
// ***** Script 7.1 *****
// Developed by Larry E. Ullman
// MySQL: Visual QuickStart Guide
// SECOND EDITION
// Contact: mysql2@DMCinsights.com
// Created: February 15, 2006
// Last modified: February 15, 2006
// This file contains the database access information
// for the accounting database.
// This file also establishes a connection to MySQL
// and selects the accounting database.

For the most part, I will refrain from including prodigious comments within the steps, but I did want to pinpoint these lines to give you a sense of how I might document a configuration file. Also, as a matter of convenience, I'll include the filename and script name as a comment in every script in this book (for easier reference).

Script 7.1. The `mysqli_connect.inc.php` script will be used by every other script in this application. In this script, a connection to the database is established.

1 <?php 2 3 // ***** mysqli_connect.inc.php ***** 4 // ***** Script 7.1 ***** 5 // Developed by Larry E. Ullman 6 // MySQL: Visual QuickStart Guide 7 // SECOND EDITION 8 // Contact: mysql2@DMCinsights.com 9 // Created: February 15, 2006 10 // Last modified: February 15, 2006 11 // This file contains the database access information 12 // for the accounting database. 13 // This file also establishes a connection to MySQL 14 // and selects the accounting database. 15 16 // Database-specific information: 17 DEFINE ('DB_USER', 'username'); 18 DEFINE ('DB_PASSWORD', 'password'); 19 DEFINE ('DB_HOST', 'localhost'); 20 DEFINE ('DB_NAME', 'accounting'); 21 22 //Connect to MySQL and select the database: 23 $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME); 24 ?>

Set the database host, username, password, and database name as constants.

DEFINE (`DB_USER', `username');
DEFINE (`DB_PASSWORD', `password');
DEFINE (`DB_HOST', `localhost');
DEFINE (`DB_NAME', `accounting');

I prefer to establish these variables as constants for security reasons (they cannot be changed this way), but that isn't required. Setting these values as some sort of variable makes sense so that you can separate the configuration parameters from the functions that use them, but again, this is not required.

The only truly important consideration is that you use a username/hostname/password combination that has privileges in MySQL to interact with the accounting database.

Connect to MySQL and select the database to be used.

$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

The mysqli_connect() function, if it successfully connects to MySQL, will return a resource link that corresponds to the open connection. This link will be assigned to the $dbc variable.

While connecting, I'm also selecting the database (since this application will interact with only one database). Failure to select the database will create problems in later scripts, although if an application uses multiple databases, you might not want to globally select one here.

Close the PHP and save the file as

mysqli_connect.inc.php.
?>

I chose to name the file with an .inc.php extension. This indicates to me that the file is used as an inclusion in other PHP scripts.

Upload the file to your server, above the Web document root (Figures 7.1 and 7.2).

Figure 7.1. Assuming that html is the root directory of my Web documents (e.g., `www.dmcinsights.com` or `http://localhost` would lead there), the configuration files should be stored outside of it. This image was taken on Mac OS X.

Figure 7.2. Assuming that wwwroot is the root directory of my Web documents (e.g., `www.dmcinsights.com` or `http://localhost` would lead there), the configuration files should be stored outside of it. This image was taken on Windows.

Because the file contains sensitive MySQL access information, it ought to be stored securely. If you can, place it in the directory immediately above or otherwise outside of the Web directory. This isn't required, but it's a nice bit of extra security.

Tips

If you place the connection script in the Web directory, then you can run it in a Web browser. If there are no problems, the result will be a blank page. If a connection problem occurs, you'll see error messages.
In this Chapter I discuss the most important of PHP's mysqli functions. All of these, and many, many more, are well documented in the PHP manual (not the MySQL manual). You'll also find plenty of sample usage code there.
Once you've written one mysqli_connect.inc.php file, you can easily make changes to the DEFINE() lines to use the script for other projects.