PHP and Relational Databases

mysql_connect()

    resource mysql_connect([string hostname [:port] [:/path/to/socket]]
                           [, string username] [, string password])

This function establishes a connection to a MySQL server on the specified hostname (or localhost, if none is specified). It returns a link identifier if successful, or false otherwise:

    <?php
    $conn = mysql_connect("localhost", "jon", "secret")
          or die("Could not connect to MySQL.");
    echo("Connection successful.");
    mysql_close($conn);
    ?>

If another call to mysql_connect() is made with the same arguments while the original connection is still open, a new connection to the server will not be established. Instead, the second call to mysql_connect() will simply return the link identifier of the connection that is already open. The connection will be closed when either mysql_close() is called or the PHP script terminates.

mysql_pconnect()

    resource mysql_pconnect([string hostname [:port] [:/path/to/socket]]
                            [, string username [, string password]])

The "p" stands for "persistent". mysql_pconnect() is the same function as mysql_connect(), except for the fact that the connection created by mysql_pconnect() is not closed when mysql_close() is called nor when the PHP script ends. The PHP interpreter itself maintains the connection to the database server.

When subsequent calls to mysql_pconnect() are made with the same arguments, the PHP interpreter reuses the existing connection instead of establishing a new one. This eliminates the burden of repeatedly opening and closing database connections in applications in which frequent calls are made using the same arguments. This time saving of persistent connections is one of PHP's great advantages over the CGI model. Of course, this only works if PHP is installed as a web server module, and not in CGI mode.

Use persistent connections with care, however. Overusing persistent connections could lead to a large number of idle database connections to your database. The most ideal use of a persistent connection is in those instances where multiple pages will also request the same kind (meaning same connection parameters) of database connection. In this case, persistent connections offer a substantial performance boost.

mysql_close()

    boolean mysql_close([resource link_identifier])

This function closes non-persistent links to the MySQL server and returns true or false, depending on its success:

    <?php
    $conn = mysql_connect("localhost", "jon", "secret")
          or die("Could not connect to MySQL.");
    mysql_close($conn);
    ?>

mysql_select_db()

    boolean mysql_select_db(string database_name [, resource link_identifier])

This function is equivalent to the USE statement in the MySQL interpreter. It sets the currently active database. Subsequent calls to mysql_query() are then executed against the selected database:

    <?php
    $conn = mysql_connect("localhost", "jon", "secret")
          or die("Could not connect to MySQL.");
    $selected = mysql_select_db("Library", $conn)
            or die("Could not select database.");
    mysql_close($conn);
    ?>

mysql_query()

    resource mysql_query(string query [, resource link_identifier])

mysql_query() is used to send SQL statements to the MySQL server to be executed. For queries other than SELECT statements, the function returns true on success or false on failure. For SELECT statements, this function returns a link identifier on success and false on failure. The link identifier can be used with mysql_result() or one of the mysql_fetch_*() functions (covered later in this section) to access the resulting data:

    <?php
    $conn = mysql_connect("localhost", "jon", "secret")
          or die("Could not connect to MySQL.");
    $selected = mysql_select_db("Library", $conn)
            or die("Could not select database.");
    $result = mysql_query("SELECT * from author");
    mysql_close($conn);
    ?>

mysql_affected_rows()

    int mysql_affected_rows([resource link_identifier])

mysql_affected_rows() returns the number of rows that were changed by the most recent INSERT, REPLACE, UPDATE, or DELETE query for the given link_identifier:

    <?php
    $conn = mysql_connect("localhost", "jon", "secret")
          or die("Could not connect to MySQL.");
    $selected = mysql_select_db("Library", $conn)
            or die("Could not select database.");
    $sql = "UPDATE details SET num_of_books=9 WHERE ISB N='1231003730'";
    $result = mysql_query($sql, $conn);
    if ($result) {
      $affectedRows = mysql_affected_rows($conn);
      echo("$affectedRows record(s) updated.");
    } else {
      echo("Query failed: $sql");
    }
    mysql_close($conn);
    ?>

Note that, unlike mysql_num_rows(), mysql_affected_rows()'s argument is the database connection's link identifier, not the query's result identifier.

mysql_num_rows()

    int mysql_num_rows(resource result)

mysql_num_rows() returns the number of rows in the resultset for a SELECT query. Here is an example that demonstrates this function:

    <?php
    $conn = mysql_connect("localhost", "jon", "secret")
          or die("Could not connect to MySQL.");
    $selected = mysql_select_db("Library", $conn)
            or die("Could not select database.");
    $sql = "SELECT book_title FROM title";
    $result = mysql_query($sql, $conn);
    if ($result) {
      $numRows = mysql_num_rows($result);
      echo("$numRows record(s) retrieved.");
    } else {
      echo("Query failed: $sql");
    }
    mysql_close($conn);
    ?>

Unlike mysql_affected_rows(), mysql_num_rows()'s argument is the query's result identifier, not the database connection's link identifier.

mysql_result()

    mixed mysql_result(resource result, int row [, mixed field])

mysql_result() is used to retrieve a single value from a mysql_query() resultset. To retrieve a full row of data from the resultset, refer the mysql_fetch() functions covered later.

Here's an example that demonstrates mysql_result():

    <?php
    $conn = mysql_connect("localhost", "jon", "secret")
          or die("Could not connect to MySQL.");
    $selected = mysql_select_db("Library", $conn)
            or die("Could not select database.");
    $sql = "SELECT book_title FROM title";
    $result = mysql_query($sql, $conn);
    if ($result) {
      $title = mysql_result($result, 0, 'book_title');
      echo("The title of the first book is $title.");
    } else {
      echo("Query failed: $sql");
    }
    mysql_close($conn);
    ?>

mysql_fetch_object()

    object mysql_fetch_object(resource result, [int result_type])

mysql_fetch_object() is used to access the data in a SELECT statement's resultset. It returns a single record of data in the form of an object whose properties correspond to the record's fields. If there are no records left in the resultset, mysql_fetch_object() will return false. This function is usually used in a loop:

    <?php
    // (Connect to database ...)

    $sql = "SELECT ISB N, book_title FROM title";
    $result = mysql_query($sql, $conn);
    while ($row = mysql_fetch_object($result)) {
      echo("ISB N: " . htmlspecialchars($row->ISB N) .
         ", Title: " . htmlspecialchars($row->book_title) . "<br />");
    }
    mysql_free_result($result);
    mysql_close($conn);
    ?>

In this snippet, we place mysql_fetch_object() in a while loop to process each record in the set. Each call of mysql_fetch_object() automatically moves to the next record in the resultset. At the end of the set, mysql_fetch_object()'s resulting false will cause the loop to end.

The individual fields are accessed as properties of the returned object. We use htmlspecialchars() to ensure that odd characters in the data (such as < or &) cannot wreak havoc in the browser.

mysql_fetch_row()

    array mysql_fetch_row(resource result)

mysql_fetch_row() is very similar to mysql_fetch_object(), except for what it returns. Instead of returning an object, it returns a numerically indexed array containing the field values:

    <?php
    // (Connect to database ...)

    $sql = "SELECT ISB N, book_title FROM title";
    $result = mysql_query($sql, $conn);
    while ($row = mysql_fetch_row($result)) {
      echo("ISB N: " . htmlspecialchars($row[0]) .
         ", Title: " . htmlspecialchars($row[1]) . "<br />");
    }
    mysql_free_result($result);
    mysql_close($conn);
    ?>

mysql_fetch_assoc()

    array mysql_fetch_assoc(resource result)

mysql_fetch_assoc() is nearly identical to mysql_fetch_row(), except the returned array is associative rather than numerically indexed:

    <?php
    // (Connect to database ...)

    $sql = "SELECT ISB N, book_title FROM title";
    $result = mysql_query($sql, $conn);
    while ($row = mysql_fetch_assoc($result)) {
      echo("ISB N: " . htmlspecialchars($row["ISB N"]) .
         ", Title: " . htmlspecialchars($row["book_title"]) . "<br />");
    }
    mysql_free_result($result);
    mysql_close($conn);
    ?>

mysql_free_result()

    int mysql_free_result(resource result)

mysql_free_result() releases all memory associated with a resultset. This would happen anyway when the script finishes executing, but if you are concerned about the memory usage for large resultsets in complex scripts, this function can give you a breather.

mysql_insert_id()

    int mysql_insert_id([resource link_identifier])

When a table does not have a logical primary key inherent in the data, it is common to create a surrogate primary key. This is usually an integer field that has the AUTO_INCREMENT property defined. When a new record is inserted into the table, AUTO_INCREMENT automatically generates a new ID by incrementing the ID from the previously inserted record.

The mysql_insert_id() function returns the AUTO_INCREMENT field ID generated by the most recent INSERT statement. If the table involved does not have an AUTO_INCREMENT field, the function returns 0.

A Database Abstraction Layer

For our own database abstraction layer we'll create a SQL class to contain our database functions, because object-oriented programming is more fun and pays better than procedural programming, and because it offers a bit more flexibility.

The disadvantage of the object-oriented approach is chiefly its slight lag in performance – PHP is not optimized for object-oriented code. If speed is of top priority, then it is best to use a procedural (function- based) approach like PHP's built-in dbx() functions. Refer to the online documentation for details. If speed is less of an issue and you favor clean, maintainable code, then an object-based solution is right for you.

Also bear in mind that it is not necessary to build your own database abstraction layer at all, as several excellent ones already exist on the web free for download, including:

• PEAR (http://pear.php.net/)

• PHPLIB (http://phplib.sourceforge.net/)

• MetaBase (http://phpclasses.UpperDesign.com/browse.html/package/20/)

• ADODB (http://php.weblogs.com/adodb/)

We're just doing it for practice.

Building the DB Class

We start by creating a new class named DB. We'll define it in a file called DB.php.

This version of the class will be written using the MySQL functions. By wrapping the functions within the DB class, however, we end up exposing an abstract API to our application code. The application won't have to know that the underlying database is a MySQL database, with the exception of the SQL queries that get executed (because, as we learned, some SQL statements can be database-specific).

We'll begin by defining the skeleton of the class:

    <?php
    class DB
    {
      /* Connection parameters */
      var $host = '';
      var $user = '';
      var $password = '';
      var $database = '';
      var $persistent = false;

      /* Database connection handle */
      var $conn = NULL;

      /* Query result */
      var $result = false;

      function DB($host, $user, $password, $database, $persistent = false)
      {
        $this->host = $host;
        $this->user = $user;
        $this->password = $password;
        $this->database = $database;
        $this->persistent = $persistent;
      }
    }
    ?>

The above block of code defines the base of our database abstraction class. We store the various connection parameters as instance variables ($host, $user, $password, $database, and $persistent). We also declare instance variables to hold the current database connection handle ($conn) and the query result ($result).

The constructor accepts values for the connection parameters. The instance variables are then populated with these values.

Now, create a simple test script named test.php that will demonstrate the functionality of the DB class:

    <?php
    require_once("DB.php");

    $db = new DB('localhost', 'jon', 'secret', 'Library');
    ?>

In this example, we simply include the file that contains our DB class (DB.php) and create a new instance of that class. We also supply a set of connection parameters. Note that because we don't supply the optional fifth parameter to the constructor, we are not requesting a persistent connection.

Let's add some functionality to our class. We'll start by adding methods opening and closing the database connection.

As the DB class allows for both persistent and non-persistent connections, the open() function must check which type of connection has been requested (by testing the value of $this->persistent, which was set in the constructor). Based on that test, the appropriate connection function is chosen (either mysql_pconnect(), for persistent connections, or mysql_connect(), for non-persistent connections) and its name stored in the $func variable:

        function open()
        {
            /* Choose the appropriate connect function */
            if ($this->persistent) {
                $func = 'mysql_pconnect';
            } else {
            $func = 'mysql_connect';
            }

One of the most convenient side-effects of PHP's interpreted nature is the ability to call functions from a variable that contains a function's name ($func, in this case). We see an example of this in the following block of code, which performs the database connection attempt. The result of the database connection attempt is stored in $this->conn. A successful connection attempt will return a link identifier, but a failed attempt will return false. We test for the false condition, and if we detect a failure, the open() function itself returns false:

            /* Connect to the MySQL server */
            $this->conn = $func($this->host, $this->user, $this->password);
            if (!$this->conn) {
                return false;
            }

On success, the function continues by selecting the requested database. Here, we test the result of the mysql_select_db() function call. If it fails, we return false. If it succeeds, the function continues and returns true, indicating a database connection was successfully opened:

            /* Select the requested database */
            if (@!mysql_select_db($this->database, $this->conn)) {
                return false;
            }
            return true;
        }

The close() function is quite simple. It merely returns the result of the mysql_close() call:

        function close()
        {
            return(@mysql_close($this->conn));
        }

There are a number of places where we return false upon an error condition. While this is easy to detect programmatically, it is often desirable to provide the user with some sort of meaningful error message. Let's add a function to provide one:

        function error()
        {
            return (mysql_error());
        }

This function calls the mysql_error() function and returns the resulting error message. The mysql_error() function returns a string describing the last error that occurred inside the PHP MySQL extension.

We can now extend our test program to including functions that open and close connections to the database and report error messages on failure:

    <?php
    require_once("DB.php");

    $db = new DB('localhost', 'jon', 'secret', 'Library');

    if (!$db->open()) {
        die($db->error());
    }

    if (!$db->close()) {
        die($db->error());
    }
    ?>

Now that we can open and close database connections, we can add some real functionality to our database abstraction class.

The query() function allows us to pass SQL statements to the database server. In this implementation, we simply call mysql_query() with the given SQL statement. The result of the query is stored in the $this->result instance variable.

Different types of queries return different results. For example, SELECT queries return a resultset while DELETE queries simply indicate the success of their operation. They have one thing in common, however: they all return false on failure. Because we want our abstract query() function to return true on success and false on failure, we test for a false return value from mysql_query(). If we don't receive a false value, we can assume the query was successful, and our function will return true. Otherwise, our function returns false:

        function query($sql)
        {
            $this->result = @mysql_query($sql, $this->conn);
            return($this->result != false);
        }

Now that our database abstraction class supports queries, we can start working with results:

        function affectedRows()
        {
            return(@mysql_affected_rows($this->conn));
        }

        function numRows()
        {
            return(@mysql_num_rows($this->result));
        }

These two functions simply return the number of rows affected by the query and the number of rows that exist in the resultset, respectively:

        function fetchObject()
        {
            return(@mysql_fetch_object($this->result, MYSQL_ASSOC));
        }

        function fetchArray()
        {
            return(@mysql_fetch_array($this->result, MYSQL_NUM));
        }

      function fetchAssoc()
      {
        return(@mysql_fetch_assoc($this->result));
      }

These three functions are quite similar. They all return the contents of the resultset, but in different formats. Each subsequent call to one of these functions will return the next row in the resultset. When all of the rows in the set have been returned, the function returns false.

The last method we need to add to our DB class is freeResult():

        function freeResult()
        {
            return(@mysql_free_result($this->result));
        }

Our database abstraction class is now complete. It provides all of the functionality that would be required by most PHP applications.

Testing the DB Class

It's now time to extend our test script to demonstrate the full functionality of our new DB class.

First, we'll test a REPLACE statement:

    <?php

    require_once 'DB.php';

    $db = new DB('localhost', 'jon', 'secret', 'Library');
    if (!$db->open()) {
      die($db->error());
    }

    if (!$db->query("REPLACE INTO title VALUES ('1861003730', 'New Title')")) {
      die($db->error());
    }

    echo("Affected rows: " . $db->affectedRows() . "<br />");

    $db->freeResult();
    $db->close();

    ?>

Here is the output:

Notice how simple it is to execute queries with our abstraction layer. We just instantiate a new object, execute an SQL statement, and the object handles the details. Let's test a SELECT statement:

    <?php
    require_once("DB.php");

    $db = new DB('localhost', 'jon', 'secret', 'Library');

    if (!$db->open()) {
      die($db->error());
    }

    if (!$db->query("SELECT * FROM title")) {
      die($db->error());
    }

    while ($row = $db->fetchAssoc()) {
      echo("ISB N: " . htmlspecialchars($row['ISB N']) .
         ", title: " . htmlspecialchars($row['book_title']) . "<br />");
    }

    $db->freeResult();
    $db->close();
    ?>

The ouput of this code:

Remember that the htmlspecialchars() function is there to prevent data in the database from being interpreted as client-side code. In normal usage, you might have the query results display cleanly in an XHTML table or some such construct. Right now, we're just checking the functionality of the DB class, so this rough format will do. We'll try one more:

    <?php
    require_once("DB.php");

    $db = new DB('localhost', 'jon', 'secret', 'Library');

    if (!$db->open()) {
      die($db->error());
    }

    if (!$db->query("DELETE FROM author WHERE auth_name='Jon Parise'")) {
      die($db->error());
    }

    echo("Affected rows: " . $db->affectedRows() . "<br />");

    $db->freeResult();
    $db->close();
    ?>

This is the output:

The database abstraction layer class that we created here is a very light and simple implementation. It really doesn't save any lines of code over using the native MySQL functions directly. However, it does establish a uniform API for accessing database functions and, as you will see in later chapters, this only requires you to learn one abstract set of functions for accessing your database; the native implementation is hidden behind the abstraction layer.