Getting PHP to Talk to MySQL

In this first part of a three-part series. you will begin learning how to use PHP to display and modify data from a MySQL database. This article is excerpted from chapter 9 of Learning PHP and MySQL, written by Michele Davis and Jon Phillips (O’Reilly, 2006; ISBN: 0596101104). Copyright © 2006 O’Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O’Reilly Media.

Now that you’re comfortable using the MySQL client tools to manipulate data in the database, you can begin using PHP to display and modify data from the database. PHP has standard functions for working with the database.

First, we’re going to discuss PHPs built-in database functions. We’ll also show you how to use the PEAR database functions that provide the ability to use the same functions to access any supported database. This type of flexibility comes from a process called abstraction. Abstraction is the information you need to log into a database that is placed into a standard format. This standard format allows you to interact with MySQL as well as other databases using the same format. Similarly, MySQL-specific functions are replaced with generic ones that know how to talk to many databases.

In this chapter, you’ll learn how to connect to a MySQL server from PHP, learn how to use PHP to access and retrieve stored data, and how to correctly display information to the user.

The Process

The basic steps of performing a query, whether using the mysql command-line tool or PHP, are the same:

  • Connect to the database.
  • Select the database to use.
  • Build a SELECT statement.
  • Perform the query. 
  • Display the results.

We’ll walk through each of these steps for both plain PHP and PEAR functions.

Resources

When connecting to a MySQL database, you will use two new resources. The first is the link identifier that holds all of the information necessary to connect to the database for an active connection. The other resource is the results resource. It contains all information required to retrieve results from an active database query’s result set. You’ll be creating and assigning both resources in this chapter.

{mospagebreak title=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 allows you to immediately start performing queries and gathering data.

As in Chapter 8, we’ll need the same pieces of information to connect to the database:

  1. The IP address of the database server
  2. The name of the database
  3. The username
  4. The password

If you’re not sure what to use for these values, consult Chapter 7. And, 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 is not shown. It’s done with plain PHP code, not a MySQL-specific PHP function.

{mospagebreak title=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.


Figure 9-1.  The interaction between functions and resources when using the database

Example 9-1.   PHP file format

  <?php
  $db_host=’hostname of database server‘;
  $db_database=’database name‘;
  $db_username=’username‘;
  $db_password=’password‘;
  ?>

In Example 9-2, we created this file to use a database on the same machine as the web server. We assign it a database name, username, and password.

Example 9-2.  The db_login.php file with values filled in

  <?php
  $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’re going to continue using the database that you started to set up in Chapter 7.

Example 9-3 is an abbreviated dump of the database created from the mysqldump command. 

 


Figure 9-2.  Reusing the login details in multiple files

Example 9-3.   The SQL to recreate the test objects


— Table structure for table `authors`

DROP TABLE IF EXISTS `authors`;
CREATE TABLE `authors` (
  `author_id` int(11) NOT NULL auto_increment,
  `title_id` int(11) NOT NULL default ‘0’,
  `author` varchar(125) default NULL,
  PRIMARY KEY (`author_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

— Dumping data for table `authors`

INSERT INTO `authors` VALUES (1,1,’Ellen Siever’),(2,1,’Aaron Weber’),(3,2,
‘Arnold Robbins’),(4,2,’Nelson H.F. Beebe’);

— Table structure for table `books`

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 the last chapter, the code in Example 9-3 can be saved as backup.sql and run from the command prompt with the following:

  mysql -u username -p password D database_name < backupfile.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.

{mospagebreak title=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.

Example 9-4.  Including the connection values and calling mysql_connect

<?php
include(‘db_login.php’);
$connection = mysql_connect($db_host, $db_username, $db_password);
if (!$connection){
die ("Could not connect to the database: <br />". mysql_error());
}
?>

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, use the die function to display the error, and then stop 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 FilesApache
  GroupApache2htdocstest.php on line 4

This occurs because PHP 5.1.2 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 edit lines 461 and 589 of C: WINDOWSphp.ini. This will change the extension to include the directory to C:/php and uncommenting the MySQL extension line, respectively.

You’ll need to restart Apache, and then MySQL support will be enabled.

Please check back next week for the continuation of this article.
[gp-comments width="770" linklove="off" ]

chat