Learning PHP 5-7.12 MySQL Without PEAR DB

7.12 MySQL Without PEAR DB

PEAR DB smooths over a lot of the rough edges of database access in a PHP program, but there are two reasons why it's not always the right choice: PEAR DB might not be available on some systems, and a program that uses the built-in PHP functions tailored to a particular database is faster than one that uses PEAR DB. Programmers who don't anticipate switching or using more than one database program often pick those built-in functions.

The basic model of database access with the built-in functions is the same as with PEAR DB. You call a function that connects to the database. It returns a variable that represents the connection. You use that connection variable with other functions to send queries to the database program and retrieve the results.

The differences are in the details. The applicable functions and how they work differ from database to database. In general, you have to retrieve results one row at a time instead of the convenience that getAll( ) offers, and there is no unified error handling.

As an example for database access without PEAR DB, this section discusses the mysqli extension, which works with MySQL 4.1.2 or greater and with PHP 5. There are similar PHP extensions for other database programs. Table 7-4 lists the database programs that PHP supports and where in the PHP Manual you can read about the functions in the extension for each database. All of the extensions listed in Table 7-4 are not usually installed by default with the PHP interpreter, but the PHP Manual gives instructions on how to install them.

Table 7-5 shows the rough equivalencies between PEAR DB functions and mysqli functions.

Table 7-5. Comparing PEAR DB functions and mysqli functions

PEAR DB function

mysqli function

Comments

$db = DB::connect( DSN)

$db = mysqli_connect(hostname,username, password, database)

$q = $db->query(SQL)

$q = mysqli_query($db,SQL)

There is no placeholder support in mysqli_query( ).

$row = $q->fetchRow( )

$row = mysqli_fetch_row($q)

mysqli_fetch_row( ) always returns numerically indexed arrays. Use mysqli_fetch_assoc( ) for string-indexed arrays or mysqli_fetch_object( ) for objects.

$db->affectedRows( )

mysqli_affected_rows($db)

$q->numRows( )

mysqli_num_rows($q)

$db->setErrorHandling(ERROR_MODE)

None

You can't set automatic error handling with mysqli, but mysqli_connect_error( ) gives you the error message if something goes wrong connecting to the database program, and mysqli_error($db) gives you the error message after a query or other function call fails.

This section doesn't explore the mysqli functions in great detail but shows how to use mysqli to do some of the things you've already seen with PEAR DB. Chapter 3 of Upgrading to PHP 5, by Adam Trachtenberg (O'Reilly) covers the ins and outs of mysqli, including advanced features such as secure connections, parameter binding, and result buffering. Examples Example 7-57 and Example 7-58 contain the necessary changes to Example 7-56 so that it uses PHP's mysqli extension instead of PEAR DB.

The two sections of the program that need to be changed are the top-level database connection code, which is shown in Example 7-57 and the process_form( ) function, which is shown in Example 7-58.

Example 7-57. Connecting with mysqli

$db = mysqli_connect('db.example.com','hunter','w)mp3s','restaurant');
if (! $db) { die("Can't connect: " . mysqli_connect_error( )); }

The code in Example 7-57 replaces the two lines under the // Connect to the database comment in Example 7-56. The mysqli_connect( ) function establishes the database connection, and the next line checks that the connection attempt succeeds.

Example 7-58. A process_form( ) function using mysqli

function process_form( ) {
    // Access the global variable $db inside this function
    global $db;
    
    // build up the query 
    $sql = 'SELECT dish_name, price, is_spicy FROM dishes WHERE ';
    
    // add the minimum price to the query
    $sql .= "price >= '" .
            mysqli_real_escape_string($db, $_POST['min_price']) . "' ";

    // add the maximum price to the query
    $sql .= " AND price <= '" .
            mysqli_real_escape_string($db, $_POST['max_price']) . "' ";

    // if a dish name was submitted, add to the WHERE clause
    // we use mysqli_real_escape_string( ) and strtr( ) to prevent
    // user-entered wildcards from working
    if (strlen(trim($_POST['dish_name']))) {
        $dish = mysqli_real_escape_string($db, $_POST['dish_name']);
        $dish = strtr($dish, array('_' => '\_', '%' => '\%'));
        // mysqli_real_escape_string( ) doesn't add the single quotes
        // around the value so you have to put those around $dish in
        // the query:
        $sql .= " AND dish_name LIKE '$dish'";
    }

    // if is_spicy is "yes" or "no", add appropriate SQL
    // (if it's either, we don't need to add is_spicy to the WHERE clause)
    $spicy_choice = $GLOBALS['spicy_choices'][ $_POST['is_spicy'] ];
    if ($spicy_choice =  = 'yes') {
        $sql .= ' AND is_spicy = 1';
    } elseif ($spicy_choice =  = 'no') {
        $sql .= ' AND is_spicy = 0';
    }

    // Send the query to the database program and get all the rows back
    $q = mysqli_query($db, $sql);

    if (mysqli_num_rows($q) =  = 0) {
        print 'No dishes matched.';
    } else {
        print '<table>';
        print '<tr><th>Dish Name</th><th>Price</th><th>Spicy?</th></tr>';
        while ($dish = mysqli_fetch_object($q)) {
            if ($dish->is_spicy =  = 1) {
                $spicy = 'Yes';
            } else {
                $spicy = 'No';
            }
            printf('<tr><td>%s</td><td>$%.02f</td><td>%s</td></tr>',
                   htmlentities($dish->dish_name), $dish->price, $spicy);
        }
    }
}

The process_form( ) function in Example 7-58 follows the same logical flow as that in Example 7-56, but the database interaction functions are different. Since PEAR DB's placeholders aren't available, the minimum and maximum prices are put directly into the $sql variable holding the query. First, however, they are escaped with mysqli_real_escape_string( ). Similarly, $_POST['dish_name'] is escaped with mysqli_real_escape_string( ). Last, the functions used to pass the query to the database and retrieve the results are different. The mysqli_query( ) function sends the query, mysqli_num_rows( ) reports the number of rows returned, and mysqli_fetch_object( ) retrieves each row in the result set as an object.

< Day Day Up >

7.12 MySQL Without PEAR DB

Table 7-4. Database extensions

Table 7-5. Comparing PEAR DB functions and mysqli functions

Example 7-57. Connecting with mysqli

Example 7-58. A process_form( ) function using mysqli