4.3. Opening a Connection

You need a method through which your PHP scripts can connect to MySQL in order to interact with the database. You can establish this connection in any of several approaches:

• PHP's MySQL Extension

• PHP's MySQLi Extension

• PHP Data Objects (PDO)

Due to potential security weaknesses in the MySQL Extension, developers are strongly encouraged to use PDO or MySQLi when using MySQL 4.1.3 or later.

4.3.1. PHP's MySQL Extension

The MySQL Extension is the original extension provided by PHP that allows developers to create PHP applications that interact with MySQL databases earlier than version 4.1.3.

The MySQL Extension uses a procedural interface, which means that each action is an individual function (see the code sample that follows). You can use the artists table described earlier as a basis for writing a PHP script that retrieve all the artists' names. Open test.php in Eclipse and enter the following code:

<?php
    // Open a MySQL connection
    $link = mysql_connect('localhost', 'root', ''),
    if(!$link) {
        die('Connection failed: ' . mysql_error());
    }

// Select the database to work with
    $db = mysql_select_db('test'),
    if(!$db) {
        die('Selected database unavailable: ' . mysql_error());
    }

    // Create and execute a MySQL query
    $sql = "SELECT artist_name FROM artists";
    $result = mysql_query($sql);

    // Loop through the returned data and output it
    while($row = mysql_fetch_array($result)) {
        printf("Artist: %s<br />", $row['artist_name']);
    }

    // Free the memory associated with the query
    mysql_free_result($result);

    // Close the connection
    mysql_close($link);
?>

Navigating to http://localhost/simple_blog/test.php in your browser yields the following result:

Artist: Bon Iver
Artist: Feist

As the immediately preceding example illustrates, each step in the process has a function assigned to it:

mysql_connect(): Accepts the host, username, and password for a MySQL connection. You must call this function before any interaction with the database can take place.

die(): This is an alias for the exit() command. It stops execution of a script after displaying an optional message (passed as the function argument).

mysql_error(): If an error occurs in the MySQL database, this function displays that error; this is helpful for debugging.

mysql_select_db(): Selects the database that the script will interact with.

mysql_query(): Executes a query to the database. This query can create, modify, return, or delete table rows, as well as perform many other tasks.

mysql_fetch_array(): Converts the MySQL resource returned by mysql_query() into an array.

mysql_free_result():Frees the memory used by mysql_query() to maximize script performance.

mysql_close(): Closes the connection opened by mysql_connect().

The MySQL extension doesn't support prepared statements (see the "Using Prepared Statements" section later in this chapter), so it is susceptible to SQL injection, a potentially devastating security issue in web applications. Malicious users can use SQL injection to extract sensitive information from a database, or even go so far as to erase all the information in a given database.

You can minimize this risk by sanitizing all information that you want to insert into the database. The MySQL extension provides a function for escaping data called mysql_real_escape_string(), which escapes (inserts a backslash before) special characters. Additional functions for sanitizing data, such as htmlentities() and strip_tags(), are available. However, some risks exist even if you implement these safeguards.

4.3.2. The MySQLi Extension

The MySQL manual recommends that developers using MySQL 4.1.3 or later use the MySQLi extension. There are many benefits to using MySQLi over the original MySQL extension, including MySQLi's:

• Support for both object-oriented and procedural programming methods

• Support for multiple statements

• Enhanced debugging capabilities

• Support for prepared statements

4.3.2.1. Using Prepared Statements

The MySQLi and PDO extensions provide an extremely useful feature in prepared statements.

In a nutshell, prepared statements enable you to separate the data used in a SQL command from the command itself. If you fail to separate these, a malicious user could potentially tamper with your commands. Using a prepared statement means that all submitted data is completely escaped, which eliminates the possibility of SQL injection. You can read more about this subject in Harrison Fisk's article on prepared statements at http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html.

A prepared statement works similarly to a regular MySQL statement, except that it uses a placeholder (a question mark [?]) to represent data. You can make the best use of prepared statements when use your user input in a query.

For instance, if you have a form on your site that asks what a user's favorite color is, you could use that input in a MySQL query via the $_POST superglobal:

$sql = "SELECT info FROM colors WHERE color = '$_POST[fav_color]'";

However, you aren't performing any sanitization of this input, so a malicious user could potentially exploit your form or harm your site using SQL injection. To avoid this, you can rewrite the preceding statement as a prepared statement:

$sql = "SELECT info FROM colors WHERE color = ?";

The question mark acts as a placeholder, and it signifies to MySQL that anything passed to this query is to be used only as a parameter for the current statement. This prevents a malicious user from tricking MySQL into giving away information or damaging the database.

4.3.2.2. Using MySQLi

To use MySQLi, you establish a connection using an object-oriented interface. I'll cover how to take advantage of object-oriented programming (OOP) in the next chapter, as well as discuss the pros and cons of OOP versus procedural programming.

The primary difference between OOP and procedural code is that an object can store information, freeing you from having to pass variables explicitly from function to function.

To familiarize yourself with MySQLi, you can rewrite the preceding example using MySQLi. Modify test.php so it contains the following:

<?php
    // Open a MySQL connection
    $link = new mysqli('localhost', 'root', '', 'test'),
    if(!$link) {
        die('Connection failed: ' . $link->error());
    }

    // Create and execute a MySQL query
    $sql = "SELECT artist_name FROM artists";
    $result = $link->query($sql);

    // Loop through the returned data and output it
    while($row = $result->fetch_assoc()) {
        printf("Artist: %s<br />", $row['artist_name']);
    }

    // Free the memory associated with the query
    $result->close();

    // Close the connection
    $link->close();
?>

Navigating to http://localhost/simple_blog/test.php in your browser yields the following result:

Artist: Bon Iver
Artist: Feist

MySQLi works similarly to the MySQL extension, with one key exception: instead of providing individual functions, developers using MySQLi have access to methods, or functions contained within the MySQLi object. In the preceding code snippet, you instantiate your MySQLi object in the variable $link and establish a connection with your host, username, password, and a database name.

To execute a query, you call the query() method and pass the variable containing your MySQL statement. You call a method in OOP using the variable that contains the object, followed by an arrow (->) and the name of the method you want to call. For example, this line from the previous code example illustrates how to call a method in OOP:

$result = $link->query($sql);

The query() method returns a mysqli_result object, which has methods that allow you to access the information returned by the query.

To access each returned entry in order, you set up a loop that uses the result of calling this line:

$result->fetch_assoc();

Next, you kick out the returned data, then destroy the returned data set by calling the close() method on the $result object. Also, you close the MySQLi connection by calling the close() method on $link, as well.

4.3.2.3. Using Prepared Statements with MySQLi

What really sets MySQLi apart from the MySQL extension is its ability to use prepared statements. If you want to allow a user to select an artist that she wants to see albums from, you can create a form that looks something like this:

<form method="post">
    <label for="artist">Select an Artist:</label>
    <select name="artist">
        <option value="1">Bon Iver</option>
        <option value="2">Feist</option>
    </select>
    <input type="submit" />
</form>

When the user selects an artist, the artist's unique ID is passed to the processing script in the $_POST['artist'] variable (review Chapter 3 for a refresher on $_POST), which allows you to change your query based on user input.

In test.php, you can build a quick script that displays album names based on user input:

<?php
    if($_SERVER['REQUEST_METHOD']=='POST')
    {
        // Open a MySQL connection
        $link = new mysqli('localhost', 'root', '', 'test'),

if(!$link) {
            die('Connection failed: ' . $mysqli->error());
        }

        // Create and execute a MySQL query
        $sql = "SELECT album_name FROM albums WHERE artist_id=?";
        if($stmt = $link->prepare($sql))
        {
            $stmt->bind_param('i', $_POST['artist']);
            $stmt->execute();
            $stmt->bind_result($album);
            while($stmt->fetch()) {
                printf("Album: %s<br />", $album);
            }
            $stmt->close();
        }

        // Close the connection
        $link->close();
    }
    else {
?>

<form method="post">
    <label for="artist">Select an Artist:</label>
    <select name="artist">
        <option value="1">Bon Iver</option>
        <option value="2">Feist</option>
    </select>
    <input type="submit" />
</form>

<?php } // End else ?>

When a user submits the form, a new MySQLi object is created, and a query is created with a placeholder for the artist_id in the WHERE clause. You can then call the prepare() method on your MySQLi object ($link->prepare($sql)) and pass the query as a parameter.

With your statement ($stmt) prepared, you need to tell MySQL how to handle the user input and insert it into the query. This is called binding parameters to the query, and you accomplish this by calling the bind_param() method on the newly created $stmt, which is a MySQLi_STMT object.

Binding parameters requires a couple steps: begin by passing the type of the parameter, then pass the parameter value.

MySQLi supports four data types:

• i: Integer (any whole number value)

• s: String (any combination of characters)

• d: Double (any floating point number)

• b: Blob (data is sent in packets that is used for storing images or other binary data)

You're passing the artist's ID, so you set the parameter type to i, then pass the value of $_POST['artist'].

With the parameters bound, you can execute the statement using the execute() method.

After the query is executed, you need to specify variables to contain the returned results, which you accomplish using the bind_result() method. For each column you've requested, you need to provide a variable to contain it. In this example, you need to store the album name, which you accomplish by supplying the $album variable.

Your script now knows where to store returned values, so you can set up a loop to run while results still exist (as returned by the fetch() method). Inside the loop, you output each album name.

Finally, you destroy your resultset and close the connection by calling the close() method on both your MySQLi_STMT and MySQLi objects; this frees the memory used by the query.

If you load your script by navigating to http://localhost/simple_blog/test.php and select Bon Iver from the list, you see the following output:

Album: For Emma, Forever Ago
Album: Blood Bank - EP

4.3.3. PHP Data Objects (PDO)

PHP Data Objects, or PDO, is similar to MySQLi in that it is an object-oriented approach to handling queries that supports prepared statements.

The main difference between MySQLi and PDO is that PDO is a database-access abstraction layer. This means that PDO supports multiple database languages and provides a uniform set of methods for handling most database interactions.

This is a great advantage for applications that need to support multiple database types, such as PostgreSQL, Firebird, or Oracle. Changing from one database type to another generally requires that you rewrite only a small amount of code, which enables developers to change your existing drivers for PDO and continue with business as usual.

The downside to PDO is that some of the advanced features of MySQL are unavailable, such as support for multiple statements. Another potential issue when using PDO is that it relies on the OOP features of PHP5, which means that servers running PHP4 won't be able to run scripts using PDO. This is becoming less of an issue over time because few servers lack access to PHP5; however, it's still something you need to take into consideration when choosing your database access method.

4.3.3.1. Rewriting Your Example in PDO

You can use PDO to rewrite your prepared statement. In test.php, modify the code as follows:

<?php
    if($_SERVER['REQUEST_METHOD']=='POST')
    {
        // Open a MySQL connection
        $dbinfo = 'mysql:host=localhost;dbname=test';

$user = 'root';
        $pass = '';
        $link = new PDO($dbinfo, $user, $pass);


        // Create and execute a MySQL query
        $sql = "SELECT album_name
                FROM albums
                WHERE artist_id=?";
        $stmt = $link->prepare($sql);
        if($stmt->execute(array($_POST['artist'])))
        {
            while($row = $stmt->fetch()) {
                printf("Album: %s<br />", $row['album_name']);
            }
            $stmt->closeCursor();
        }
    }
    else {
?>

<form method="post">
    <label for="artist">Select an Artist:</label>
    <select name="artist">
        <option value="1">Bon Iver</option>
        <option value="2">Feist</option>
    </select>
    <input type="submit" />
</form>

<?php } // End else ?>

The first step, opening the database connection, is a little different from the other two methods you've learned about so far. This difference stems from the fact that PDO can support multiple database types, which means you need to specify a driver to create the right type of connection.

First, you create a variable called $dbinfo that tells PDO to initiate itself using the MySQL driver for the host localhost host and the test database. Next, you create two more variables, $user and $pass, to contain your database username and password.

After you pen your connection, you form your query with a placeholder, pass it to the prepare() method, and then pass the query to be prepared. This returns a PDOStatement object that you save in the $stmt variable.

Next, you call the execute() method with an array containing the user-supplied artist ID, $_POST['artist']. This is equivalent to calling both bind_param() and execute() with the MySQLi extension.

After the statement has executed, you set up a loop to run while results still exist. Each result is sent to the browser, and you free the memory using the closeCursor() method.

Running this script by loading http://localhost/simple_blog/test.php produces the following:

Album: For Emma, Forever Ago
Album: Blood Bank - EP