Using MySQL from PHP

by John Coggeshall
02/19/2004

Welcome back to PHP Foundations. My previous column finished the crash course on using MySQL to store and retrieve data from a database using the Structured Query Language (SQL). In today's column, I will begin to use everything I have shown you thus far to work with and create database-driven web pages using PHP. Let's get started by discussing how a database interacts with a web application.

Database-Driven Architecture

For those of you who have been reading my column on a regular basis since it began (thank you, I'm aware of at least a few of you), way back in 2001 I provided you with a flow diagram outlining how PHP works within a web server. Back then, the diagram was pretty generic, but today I'll revisit it in more detail to describe a database-driven architecture.

In database-driven applications, three different players produce the final output of the web page you view with your client: the web server, the scripting language (PHP), and the database back end (MySQL). When the client browser requests a page from your web site, the following steps occur:

1. The web server receives the request via HTTP for a particular web page and resolves and retrieves the requested file.

2. Depending on the nature of the file (i.e., if it ends in .php), it is pre-processed using, in our case, the PHP engine.

3. The script's application and presentation logic executes, performing database queries as necessary.

4. The PHP engine uses the results from the database in its application logic to construct the HTML document, returning it to the web server and, finally, the client.

We will focus on steps three and four in our discussions here. Looking at those steps in more detail, we can summarize the process of accessing and working with a database connection from within a PHP script in the following steps. (The steps in parentheses are optional, depending on circumstance.)

• Establish a connection to the database server.
• (Validate any user input.)
• Select the database on the server to use.
• Execute the desired query against the database.
• (Retrieve and process the results.)
• Create HTML or perform actions based on results.
• (Close the database connection.)

Connecting to a MySQL Database

From a development standpoint, connecting and executing queries from PHP is as simple as calling the appropriate functions. Let's look at the basic functions used in almost every database-driven application. As I have already explained, the first step is to connect to the database — in our case, this is done via the mysql_connect() function, whose syntax follows:

mysql_connect([$server [, $username [, $password [, $new_link [, $flags]]]]])

If you have worked with MySQL's mysql client application, most of these parameters should already make sense to you. The first parameter, $server, is the address of the MySQL server to connect to using the username and password provided by the $username and $password parameters. When I say "address," however, I am not necessarily talking about a TCP/IP address. This parameter can take multiple forms:

// Connect to the server at hostname using the default port
$server = 'hostname'

// Connect to the server at hostname using the specified port
$server = 'hostname:port'

// Connect to the server on the local machine using the provided local socket
$server = ':/path/to/socket'

Related Reading Web Database Applications with PHP and MySQL By Hugh E. Williams, David Lane

Note: when specifying a server using the hostname, it is worthy to note that the MySQL extension in PHP will attempt to connect using a local socket (or named pipe in Windows) instead of via TCP/IP, if the hostname is of the form localhost, or localhost:port is used. Although this is generally desirable (and recommended), you can also force a TCP/IP connection by using the IP address 127.0.0.1 instead.

The fourth parameter, $new_link, indicates if a new link should be established, even if one already exists for this request. This only applies if you call mysql_connect() multiple times to the same server with the same authentication information. Normally, PHP will reuse an already-opened connection. This parameter will override that behavior, creating a new connection.

The fifth and final parameter is $flags. This parameter is any combination of the following constants bitwise ORd together:

MYSQL_CLIENT_COMPRESS
Establish a connection to the database using a compressed version of the protocol.

MYSQL_CLIENT_IGNORE_SPACE
Ignore white space after function names in queries.

MYSQL_CLIENT_INTERACTIVE
Use the interactive_timeout settings of the MySQL server instead of the default wait_timeout setting when determining if the connection is inactive and should be closed by the server. (See the MySQL documentation for more information on these settings.)

When executed, the mysql_connect() function will attempt to establish a connection to the database and return a resource representing that connection. If the attempt fails for any reason, mysql_connect() will return a Boolean false.

Selecting the Database to Use

Once you have a database connection, the next step is to select the database that you will be performing queries against. (Remember the SQL USE statement?) To do this, you'll need the mysql_select_db() function which has the following syntax:

mysql_select_db($database [, $link]);

where $database is the name of the database to use, and the optional parameter $link is the database connection resource returned from the mysql_connect() function. This function will attempt to select the specified database and return a Boolean indicating success or failure.

Note: As is the case with almost all of the MySQL extension functions, the $link parameter is optional. In every case, PHP will use the last opened connection. If no connection is open, it will attempt to open one automatically. It is strongly recommended that you provide a $link explicitly to avoid problems as your applications become more advanced.

Performing a Query Against a Database

Now that you know how to connect to the MySQL database, let's see how to perform a query against the database from within PHP. To do this, use the appropriately named mysql_query() function, whose syntax is as follows:

mysql_query($query [, $link]);

where $query is a single SQL query to execute (without the terminating semi-colon or \g) and the optional parameter $link is the value returned from mysql_connect(). As usual, PHP will use the last opened connection if you do not provide the $link parameter.

Upon successful execution, mysql_query() will return a resource representing the result set or a Boolean false if the query failed. Note that a query is considered a success even if no results are returned; mysql_query() will only fail if the query itself was malformed or otherwise unable to execute on the server. Determining if any results were actually returned from a query requires a different method.

Retrieving a Result Set

Now that you know how to perform a query, it's time to learn how to access the data from a result set. PHP has many different methods to accomplish this task, but they all have the same general form. For our purposes, I'll explain things in the context of the mysql_fetch_row() function. Its syntax is as follows:

mysql_fetch_row($result);

where $result is the result resource returned from a successful query executed using the mysql_query() function. This function will return a single row from the result set as an enumerated array, where element zero represents the first column, element one represents the second, and so on. Each subsequent call will return the next row in the result set until no more rows remain. Then, mysql_fetch_row() will return a Boolean false. Generally, this function is used in conjunction with a while loop to traverse the entire array as shown in the below example snippet:

<?php

/* Connection code omitted */

$result = mysql_query("SELECT * FROM books");

if(!$result) die("Query Failed.");

while($row = mysql_fetch_row($result)) {

    /*  
    $row[0] now contains the first column of the current row,
    index 1 is the second, etc.
    */

}
?>

As I stated earlier, mysql_fetch_row() is not the only function available that allows you to access rows of a result set in this fashion. Each of the following functions has an identical syntax and use as mysql_fetch_row(), but each provides the row in a different format as described:

• Return the current row as an associative array, where the name of each column is a key in the array.

  $row = mysql_fetch_assoc($result) 
  $row['column_name']

• Return the current row with both associative and numeric indexes where each column can either be accessed by 0, 1, 2, etc., or the column name.

  $row = mysql_fetch_array($result) 
  $row[0]
  
  // or 
  
  $row['column_name']

• Return the current row as an object with member variables for each column in the result set.

  $row = mysql_fetch_object($result) 
  
  $row->column_name

Closing a Database Connection

Although it is not strictly necessary, sometimes it is advantageous to close an open connection to the database when you no longer need it, instead of waiting for the end of the request, when PHP will do so automatically. Use the mysql_close() function with the following syntax:

mysql_close($link)

where $link is the database connection resource returned from the mysql_connect() function.

More PHP/MySQL to Come

That's all for today! My next column will introduce even more MySQL PHP functions that do things like determine the number of rows in the result set and deal with errors. Soon after that, I'll pull together all of these ideas to create a front end to the book information database we worked with previously. See you then!

John Coggeshall is a a PHP consultant and author who started losing sleep over PHP around five years ago.

---

Read more PHP Foundations columns.

Return to the PHP DevCenter.