results from an active database query’s result set. You’ll be creating and assigning both resources in this chapter. Querying the Database with PHP Functions
In this section, we introduce how to connect to a MySQL database with PHP. It’s quite simple, and we’ll begin shortly with examples, but we should talk briefly about what actually happens. When you try connecting to a MySQL database, the MySQL server authenticates you based on your username and password. PHP handles connecting
to the database for you, and it allows you to start performing queries and gathering data immediately.
As in Chapter 8, we’ll need the same pieces of information to connect to the database:
? The IP address of the database server ? The name of the database ? The username ? The password
Before moving on, make sure you can log into your database using the MySQL command-line client.
Figure 9-1 shows how the steps of the database interaction relate to the two types of resources. Building the SELECT statement happens before the third function call, but it is not shown. It’s done with plain PHP code, not a MySQL-specific PHP function.
Figure 9-1. The interaction between functions and resources when using the database
Including Database Login Details
You’re going to create a file to hold the information for logging into MySQL. Storing this information in a file you include is recommended. If you change the database password, there is only one place that you need to change it, regardless of how many
PHP files you have that access the database. You don’t have to worry about anyone directly viewing the file and getting your database login details. The file, if requested by itself, is processed as a PHP file and returns a blank page.
Let’s call this file db_login.php and place it in the same directory as your other PHP files. The file is represented in Example 9-1.
Example 9-1. A template for setting database login settings
$db_host='hostname of database server'; $db_database='database name'; $db_username='username'; $db_password='password';
?>
In Example 9-2, we create this file to use a database on the same machine as the web server. We assign it a database name, username, and password.
$db_host='localhost'; $db_database='test'; $db_username='test'; $db_password='yourpass'; ?>
Figure 9-2 illustrates how you’re going to use this file with other PHP files. You’regoing to continue using the database that you started to set up in Chapter 7.
Figure 9-2. Reusing the login details in multiple files
Example 9-3. The SQL to recreate the test objects (continued) DROP TABLE IF EXISTS books; CREATE TABLE books (
title_id int(11) NOT NULL auto_increment, title varchar(150) default NULL, pages int(11) default NULL, PRIMARY KEY (title_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; --
-- Dumping data for table books --
INSERT INTO books VALUES (1,'Linux in a Nutshell',476),(2,'Classic Shell Scripting',256); --
-- Table structure for table purchases --
DROP TABLE IF EXISTS purchases; CREATE TABLE purchases (
id int(11) NOT NULL auto_increment, user varchar(10) default NULL, title varchar(150) default NULL, day date default NULL, PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; --
-- Dumping data for table purchases --
LOCK TABLES purchases WRITE;
INSERT INTO purchases VALUES (1,'Mdavis','Regular Expression Pocket Reference','2005-02-15'),(2,'Mdavis','JavaScript & DHTML Cookbook','2005-02-10');
If you didn’t create the tables in Chapter 8, the code in Example 9-3 can be saved as backup.sql and run from the command prompt with the following syntax:
mysql -u username -ppassword -D database_name < backup_file_name.sql Using the values from the examples, it becomes:
mysql -u test -pyourpass -D test < backup.sql The database is called test, and it consists of three tables called books, authors, and purchases. Each table has a few sample rows. That’s enough to get us started querying from PHP. Connecting to the Database
The first thing you need to do is connect to the database and check to make sure there’s a connection. Including the file that you set up to store your connection information allows you to use the variables instead of hardcoded values when you call the mysql_connect function, as shown in Example 9-4. We’re assembling one file, db_test.php, by adding these code snippets.
Example 9-4. Including the connection values and calling mysql_connect in db_test.php
// Include our login information include('db_login.php'); // Connect
$connection = mysql_connect($db_host, $db_username, $db_password); if (!$connection){
die (\}
The mysql_connect function takes the database host, username, and password as parameters. If the connection is successful, a link to a database is returned. FALSE is returned if a connection can’t be made. Check the return value from the function to make sure there’s a connection. If there’s a problem, such as an incorrect password, print out a polite warning and the reason for the error using mysql_error.
Instead of simply echoing an error message, die( ) displays the error and stops the program. Not being able to access the database makes most database-driven pages fairly useless and prevents the user from seeing numerous errors.
Notice that we didn’t specify the database name yet.
Troubleshooting connection errors One error you may get is:
Fatal error: Call to undefined function mysql_connect( ) in C:\\Program Files\\Apache
Software Foundation\\Apache2.2\\htdocs\\db_test.php on line 4
This error occurs because PHP 5.x for Windows was downloaded, and MySQL support was not included by default. To fix this error, copy the
php_mysql.dll file from the ext/ directory of the PHP ZIP file to C:\\php, and then C:\\WINDOWS\\php.ini.
Make sure there are two lines that are not commented out by a semicolon (;) at the beginning of the line like these:
extension_dir = \extension=php_mysql.dll
This will change the extension to include the directory to C:/php and include the MySQL extension, respectively. You can use the Search function of your text editor to check whether the lines are already there and just need to be uncommented, or whether they need to be added completely.
You’ll need to restart Apache, and then MySQL support will be enabled. Selecting the Database
Now that you’re connected, the next step is to select which database to use with the mysql_select_db command. It takes two parameters: the database name and, optionally, the database connection. If you don’t specify the database connection, the default is the connection from the last mysql_connect:
// Select the database
$db_select=mysql_select_db($db_database); if (!$db_select) {
die (\}
Again, it’s good practice to check for an error and display it every time you access the database.
While it’s possible to call mysql_select_db multiple times within the same script, it’s not considered good practice.
Now that you’ve got a good database connection, you’re ready to execute your SQL query.
Building the SQL SELECT Query
Building a SQL query is as easy as setting a variable to the string that is your SQL query. Of course, you’ll need to use a valid SQL query, or MySQL returns with an error when you execute the query. The variable name $query is used since the name reflects its purpose, but you can choose anything you’d like for a variable name. The SQL query in this example is SELECT * FROM books.
