Upgrading to PHP 5 | O'Reilly Media

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

PHP 4 is a wildly popular web programming language. Web servers on over 15,000,000 domains support PHP. PHP is the most popular Apache module by almost a 2-to-1 margin. But if PHP's so great, why do we need PHP 5?

Well, PHP 4 isn't perfect. While it makes developing web applications quick and easy, it's occasionally weighted down by its legacy baggage. This makes tackling some problems unnecessarily difficult.

In particular, PHP's version of object-oriented programming (OOP) lacks many features, the MySQL extension doesn't support the new MySQL 4.1 client protocol, and XML support is a hodgepodge.

Fortunately, PHP 5 improves on PHP 4 in three major areas:

Object-oriented programming
MySQL
XML

These items have all been completely rewritten, turning them from limitations into star attractions. While these changes alone warrant a new version of PHP, PHP 5 also provides:

SQLite for an embedded database
Iterators
Error handling using exceptions
Streams
SOAP

Some of these features, such as iterators and exceptions, are available only due to fundamental changes in PHP's core. Others, such as streams and SQLite, are PHP 4.3 features that have matured into prime-time use in PHP 5.

This book shows you how to take advantage of these new features in your applications. Additionally, it places a special emphasis on not just telling you what's new, but showing you how and why it's new.

Whenever possible, there's a direct comparison between the PHP 4 method of solving a task and the PHP 5 solution. The PHP 5 way is frequently shorter, more elegant, and provides you with greater flexibility. The before-and-after examples demonstrate in clear code what's better about PHP 5 and provide you with concrete examples to ease the transition from PHP 4 to PHP 5.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

To understand why PHP 5 came to exist, it's necessary to quickly review the evolution of PHP as a language.

When Rasmus Lerdorf created PHP back in 1995, it wasn't even called PHP—his Personal Home Page/Forms Interpreter language was known as PHP/FI. At the time, PHP/FI's main focus was solving small-time web tasks: guest books, hit counters, and basic forms processing. Its major benefit was its simplicity; PHP/FI made it easy to handle all the messy tasks thrown at a web developer. Additionally, it's C-like syntax was already understood by many programmers.

Over the next two years, PHP/FI gradually grew in popularity. However, by 1997, PHP/FI was already showing its age. As the Internet gathered steam, programmers began to create more complex applications, such as e-commerce shopping carts. PHP/FI's quirks and limitations hindered development. It was too slow and was missing some basic features, such as for and foreach loops.

These problems caused Zeev Suraski and Andi Gutmans, of Zend fame, to begin work on a new version of PHP/FI. This version, which became PHP 3, solved many difficulties faced by PHP/FI developers while remaining true to the essential nature of PHP/FI.

In particular, PHP 3 was faster and more efficient than PHP/FI. The new parser also provided the opportunity to iron out some language oddities, making PHP more consistent. PHP 3's other major advance was an easy-to-use extension API. Developers from all over contributed extensions to PHP, effectively turning PHP from a programming language into an entire web development environment.

PHP 3 retained PHP/FI's procedural syntax, but it also introduced a very simplistic object-oriented syntax. Originally the result of a weekend hack, developers flocked to objects, much to the surprise of Zeev and Andi. Unfortunately, PHP 3 was ill-equipped to provide all the object-oriented features developers demanded.

A few months after PHP 3 went final in June of 1998, work started on PHP 4. Again, the problem was speed. The new extension infrastructure provided the opportunity to create larger and more complex web sites than ever imagined. In the words of Alan Greenspan, "irrational exuberance" was at hand.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Better object-oriented features aren't the only new features of PHP 5. Many extensions have been rewritten to take advantage of PHP 5's new capabilities, and many new extensions have been added to the distribution.

The MySQL database is PHP's partner in crime. Many developers power their web sites with MySQL, yet PHP's original MySQL extension dates back to the days of PHP/FI. It's showing its age.

In retrospect, some design decisions for the MySQL extension weren't the best solution after all. Also, the latest versions of MySQL, 4.1 and 5.0, introduce many new features, some of which require significant changes to the extension.

As a result, PHP 5 comes with a completely new and improved MySQL extension. Dubbed MySQLi, for the MySQL Improved extension, MySQLi offers prepared statements, bound parameters, and SSL connections. It even takes advantage of PHP 5's new object-oriented support to provide an OO interface to MySQL. This extension is covered in Chapter 3.

While MySQL is greater than ever, it's actually "too much database" for some jobs. SQLite is an embedded database library that lets you store and query data using an SQL interface without the overhead of installing and running a separate database application. It's the topic of Chapter 4.

PHP 5 bundles SQLite, providing developers with a database that's guaranteed to work on all PHP 5 installations. Despite the name, SQLite is a nowhere close to a "lite" database. It supports transactions, subqueries, triggers, and many other advanced database features.

Like MySQLi, SQLite also comes with dual procedural and OO interfaces.

XML is a key part of web development, so PHP 5 offers a full selection of new XML tools. A major goal of XML in PHP 5 is interoperability among each of the different XML extensions, making them a unified unit instead of individual agents.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You can download PHP 5 from http://www.php.net/downloads.php. The installation process is identical to PHP 4; however, PHP 5 does have some new configuration options. See Table C-1 in Appendix C for a complete list.

During the transition from PHP 4 to PHP 5, it's common to want to run both versions of PHP simultaneously. This lets you easily test code under PHP 5 without switching completely away from PHP 4. It also allows you to slowly migrate scripts from PHP 4 to PHP 5, as you can control which version of PHP parses specific sets of pages.

One option is to run two separate web servers, either on different machines or on different ports on the same machine. The primary advantage of using two web servers is that you can run both PHP 4 and PHP 5 as a module. The disadvantages are that you either need multiple computers at your disposal or need to be familiar with installing Apache (or your particular web server of choice). It's also a lot of work for a temporary situation.

Another solution is to continue using your existing web server and install one version of PHP as a module and another as a CGI. This gets you up and running with both versions with minimal fuss; however, the CGI version of PHP lacks certain features included in the Apache module.

Appendix C provides detailed instructions for setting up PHP 4 and PHP 5 under Apache on both Unix and Windows. It shows how to configure Apache to switch between the versions both on a directory-by-directory basis and by setting up a virtual server on another port.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter introduces object-oriented programming (OOP) and explains all the object-oriented (OO) features in PHP 5. The chapter assumes no prior knowledge of OOP, so if this is your first time learning it, that's okay.

However, there's lots here for PHP 4 OO programmers, too. PHP 5, besides adding many OO bells and whistles, modifies fundamental parts of PHP 4's OO behavior. Running PHP 4 programs under PHP 5 will result in unexpected results and errors if you're not up-to-date on all the changes.

Additionally, the new features allow you to implement many OOP best practices that just aren't possible in PHP 4. This chapter shows you how and why you should modify your existing code to take full advantage of PHP 5.

Early versions of PHP were strictly procedural: you could define functions, but not objects. PHP 3 introduced an extremely rudimentary form of objects, written as a late-night hack. Back in 1997, nobody expected the explosion in the number of PHP programmers, nor that people would write large-scale programs in PHP. Therefore, these limitations weren't considered a problem.

Over the years, PHP gained additional object-oriented features; however, the development team never redesigned the core OO code to gracefully handle objects and classes. As a result, although PHP 4 improved overall performance, writing complex OO programs with it is still difficult, if not nearly impossible.

PHP 5 fixes these problems by using Zend Engine 2. The first version of the Zend Engine was written for PHP 4 to handle PHP's core functionality, such as what type of objects you can use, and to define the language's syntax.

Zend Engine 2, which powers PHP 5, enables PHP to include more advanced object-oriented features, while still providing a high degree of backward compatibility to the millions of PHP scripts already written.

If you don't have experience with object-oriented programming outside of PHP, then you're in for a bit of a surprise. While some of the new features allow you to do things more easily, many features don't let you do anything new at all. In many ways, they

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Object-oriented programming is a way to group functions and data together into a prepackaged unit. This unit is known as an object.

Many people prefer OOP because it encourages a behavior known as encapsulation. Inevitably, whenever you write code, there's some part—the way you store the data, what parameters the functions take, how the database is organized—that doesn't work as well as it should. It's too slow, too awkward, or doesn't allow you to add new features, so you clean it up.

Fixing code is a good thing, unless you accidently break other parts of your system in the process. When a program is designed with a high degree of encapsulation, the underlying data structures and database tables are not accessed directly. Instead, you define a set of functions and route all your requests through these functions.

For example, you have a database table that stores names and email addresses. A program with poor encapsulation directly accesses the table whenever it needs to fetch a person's email address:

$name   = 'Rasmus Lerdorf';
$db     = mysql_connect( );
$result = mysql_query("SELECT email FROM users 
                        WHERE name  LIKE '$name'", $db);
$row    = mysql_fetch_assoc($r);
$email  = $row['email'];

A better-encapsulated program uses a function instead:

function getEmail($name) {
    $db = mysql_connect( );
    $result = mysql_query("SELECT email FROM users 
                            WHERE name  LIKE '$name'", $db);
    $row    = mysql_fetch_assoc($r);
    $email  = $row['email'];
    return $email
}

$email = getEmail('Rasmus Lerdorf');

Using getEmail( ) has many benefits, including reducing the amount of code you need to write to fetch an email address. However, it also lets you safely alter your database schema because you only need to change the single query in getEmail( ) instead of searching through every line of every file, looking for places where you SELECT data from the users table.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In PHP 4, when you copy a variable or pass it to a function, you don't transfer the original variable. Instead, you pass a copy of the data stored in the variable. This is known as pass-by-value because you're copying the variable's values and creating a duplicate.

As a result, the new variable is completely disassociated from the original. Modifying one doesn't affect the other, similar to how calling $zeev->setName( ) didn't affect $rasmus in the earlier example.

Objects in PHP 5 behave differently from other variables. You don't pass them by value, like you do with scalars and arrays. Instead, you pass them by reference. A reference, or object reference, is a pointer to the variable. Therefore, any alterations made to the passed object are actually made to the original.

Here's an example:

$rasmus = new Person;
$rasmus->setName('Rasmus Lerdorf');

$zeev = $rasmus;
$zeev->setName('Zeev Suraski');

print $rasmus->getName( );
Zeev Suraski

In this case, modifying $zeev does change $rasmus!

This is not what occurs in PHP 4. PHP 4 prints

Rasmus
Lerdorf

because $zeev = $rasmus causes PHP to make a copy of the original object and assign it to $zeev.

However, in PHP 5, this command assigns $zeev a reference to $rasmus. Any changes made to $zeev are actually made to $rasmus.

A similar behavior occurs when you pass objects into functions:

function editName($person, $name) {
    $person->setName($name);
}

$rasmus = new Person;
$rasmus->setName('Rasmus Lerdorf');

setName($rasmus, 'Zeev Suraski');
print $rasmus->getName( );
Zeev Suraski

Normally, changes made inside of editName( ) don't alter variables outside of the function, and to update the original object you need to return the modified variable. That's what you need to do in PHP 4.

Since PHP 5 passes objects by reference, changing them inside a function or a method alters the original object. There's no need for you to pass them explicitly by reference or return the modified copy. This action is also referred to as

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

To define a class, use the class keyword followed by the class name:

class Person {

}

This code creates a Person class. This is not a very exciting class, because it lacks methods or properties. Class names in PHP are case-insensitive, so you cannot define both a Person and a PERSON class.

Use this class name to instantiate a new instance of your object:

$rasmus = new Person;

Alternatively, to determine a class from an object instance, use get_class( ):

$person = new Person;
print get_class($person)
Person

Even though class names are case-insensitive, PHP 5 preserves their capitalization. This is different from PHP 4, where PHP converts the name to lowercase. In PHP 4, calling get_class( ) on an instance of the Person class produced person. PHP 5 returns the correct class name.

List class properties at the top of the class:

class Person {
    public $name;
}

This creates a public property named name. When a property is public, it can be read from and written to anywhere in the program:

$rasmus = new Person;
$rasmus->name = 'Rasmus Lerdorf';

Properties in PHP 4 are declared using a different syntax: var. This syntax is deprecated in favor of public, but for backward compatibility, var is still legal. The behavior of a property declared using public and var is identical.

Never use public properties. Doing so makes it easy to violate encapsulation. Always use accessor methods instead.

You don't need to predeclare a property inside the class to use it. For instance:

$rasmus = new Person;
$rasmus->email = 'rasmus@php.net';

This assigns rasmus@php.net to the email property of $rasmus. This is valid, even though email was not mentioned in the class definition.

Even though you don't have to, always predeclare your properties. First, these properties are implicitly public, so they're already bad. Second, predeclaring properties forces you to think about the best way to handle data. It also makes it easier for anyone reading the class (including yourself two months later) to see all of the class's properties without wading through the entire code of the class.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The topics in the previous section covered the limit of PHP 4's object-oriented abilities. This section introduces a few concepts new to PHP 5: interfaces, type hinting, and static methods and properties.

In object-oriented programming, objects must work together. Therefore, you should be able to require a class (or more than one class) to implement methods that are necessary for the class to interact properly in your system.

For instance, an e-commerce application needs to know a certain set of information about every item up for sale. These items may be represented as different classes: Book, CD, DVD, etc. However, you need to know that your application can find the name, price, and inventory number of each object, regardless of its type.

The mechanism for forcing classes to support the same set of methods is called an interface. Defining an interface is similar to defining a class:

interface Sellable {
    public function getName( );
    public function getPrice( );
    public function getID( );
}

Instead of using the keyword class, an interface uses the keyword interface. Inside the interface, define your method prototypes, but don't provide an implementation.

This creates an interface named Sellable. Any class that's Sellable must implement the three methods listed in the interface: getName( ), getPrice( ), and getID( ).

When a class supports all the methods in the interface, it's called implementing the interface. You agree to implement an interface in your class definition:

class Book implements Sellable {

    public function getName( ) { ... }
    public function getPrice( ) { ... }
    public function getID( ) { ... }
}

Failing to implement all the methods listed in an interface, or implementing them with a different prototype, causes PHP to emit a fatal error.

A class can agree to implement as many interfaces as you want. For instance, you may want to have a Listenable

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Besides encapsulation, another major advantage of object-oriented code is reuse. Reusing code reduces development time and the number of bugs. Object-oriented programming promotes reuse through a process known an inheritance.

With inheritance, you can modify a class by adding or rewriting its methods. This allows your new class to be more specific than the original one, while still allowing you access to all the methods of the first class. The original class is known as the parent, and the new class is called the child.

Creating a child class is also called extending a class or subclassing an object. The original class that's extended can also be called a super class or a base class.

For instance, you can extend Person to create an Employee class, where an Employee is a Person with a salary.

When extending a class, abide by the "is a" rule. You should always be able to say, "Child class is a Parent class." Following this rule leads to clean relationships between your classes. This example is okay because an Employee is a Person:

class Person {
    private $name;
    
    public function setName($name) {
        $this->name = $name;
    }

    public function getName( ) {
        return $this->name;
    }
}

class Employee extends Person {
    private $salary;

    public function setSalary($salary) {
        $this->salary = $salary;
    }

    public function getSalary( ) {
        return $this->salary;
    }

}

$billg = new Employee;
$billg->setName('Bill Gates');
$billg->setSalary(865114); // Actual 2003 salary (excluding stock)

This code creates a new Employee class; instantiates a new instance of Employee, $billg; and sets his name and salary.

The extends keyword at the top of the class definition indicates to PHP that this class should inherit all the properties and methods of the parent class. This allows you to interact with a child object in the same way as its parent because when PHP cannot find a property or method in the child class, it searches the parent class.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

PHP 5 has a few methods that are implicitly invoked behind the scenes. You've already seen two,

_
_construct( )

and _ _destruct( ), but these are not the only special methods in PHP 5.

There are seven special methods, and they are as follows:

_ _construct( ): Called when instantiating an object
_ _destruct( ): Called when deleting an object
_ _get( ): Called when reading from a nonexistent property
_ _set( ): Called when writing to a nonexistent property
_ _call( ): Called when invoking a nonexistent method
_ _toString( ): Called when printing an object
_ _clone( ): Called when cloning an object

These methods are easy to spot, since they all begin with two underscores (_ _).

You've read a lot about the benefits of encapsulation and why your classes must have accessor methods. Writing these methods, however, induces numbness as you repeatedly create methods for each property in your class. PHP 5 has two special methods,

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

PHP 5 sports a new MySQL extension. While this extension is similar in spirit to the original MySQL extension, it has many new features and other improvements. To differentiate it from the original version, this extension is called the "Improved MySQL extension," or mysqli for short.

The changes to mysqli come from two places. The majority are from new features available in MySQL 4.1. However, PHP 5 also allows mysqli to add an object-oriented interface.

Here's a list of the major advances in mysqli:

Compatibility with MySQL 4.1 and above
Prepared statements and bound parameters
Object-oriented interface
Secure connections using SSL

Additionally, MySQL 4.1 has new SQL-level capabilities that you can use from PHP. They include:

Subselects
Transactions
Fulltext searching
Unicode support
Geospacial support (GIS)

On the downside, there are a few wrinkles if you want to start using mysqli with your existing PHP projects:

Its client libraries are not bundled with PHP 5.
It does not work with MySQL 4.0 and below.
It's missing some mysql functions.

In a nutshell, if all your projects use MySQL 4.1 and later, and you have no legacy MySQL code, then all you need to do is download the MySQL client libraries and start using mysqli. On the other hand, you'll have some work to do if you:

Want to use MySQL 4.1, but have existing mysql-based code
Must write code that runs under MySQL 4.0 (and below) and 4.1 (and above)
Want to have some applications use a MySQL 4.0 database and others use a MySQL 4.1 database

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You must download and install MySQL 4.1 or greater to use mysqli. In particular, if you use the MySQL library bundled with PHP 4, you now must download and install a new version of MySQL.

While PHP 4 bundles a copy of the MySQL client libraries, PHP 5 does not. As of MySQL 4.1, the entire MySQL application is licensed under the GNU General Public License (GPL). Since PHP uses a different type of open source license, PHP was legally unable to bundle the libraries without modifying their license.

MySQL eventually added a special license exemption for many free and open source products, including PHP. However, the PHP Group decided not to restore the libraries. Since many people already had the libraries on their systems, bundling the libraries actually caused conflicts when the bundled library differed from the preinstalled version.

As a result, if you don't already have a copy of the MySQL client libraries, you need to install one. You can download MySQL from http://www.mysql.com/. If you're using mysqli, be sure to use a copy of MySQL 4.1.2 or greater, as earlier versions won't work with PHP.

Before installing MySQL 4.1, you should read the Section 3.10. You may have migration problems, particularly if you're upgrading directly from MySQL 3.2.x and skipping MySQL 4.0. Reading this section isn't necessary if MySQL 4.1 is your first version of MySQL.

After you've installed MySQL, you can enable the mysqli extension with the --with-mysqli flag during the PHP configure process.

Unlike for mysql, you don't tell PHP where MySQL is located by adding the path to the MySQL base installation. Instead, PHP uses MySQL's new mysql_config file.

Here's an example of the difference between the configuration options for mysql and mysqli for a MySQL installation in /usr/local/mysql:

// mysql
--with-mysql=/usr/local/mysql

// mysqli
--with-mysqli=/usr/local/mysql/bin/mysql_config

You can enable both mysql

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The procedural interface to mysqli is largely identical to the older mysql extension. Except, of course, all the function names begin with mysqli instead of mysql:

$db = mysqli_connect($server, $user, $password) or 
    die("Could not connect: " . mysqli_error( ));
mysqli_select_db($db, "users");

$result = mysqli_query($db, "SELECT username FROM users");

while ($row = mysqli_fetch_assoc($result)) {
    print $row['username'] . "\n";
}

mysqli_free_result($result);
mysqli_close($db);

This code connects to a MySQL database, selects the users table, makes a query, and then fetches each row as an associative array. These functions all behave the same as their mysql counterparts, except that the mysqli functions require a database handle as their first argument instead of optionally allowing one as the final argument. Section 3.10 covers all the API changes in detail.

There is also a minor change in mysqli_fetch_array( ). When there are no additional rows, it returns NULL. The original extension returns false. This difference won't affect code like that shown here, where it only assigns the result to a variable, but if you use

!=
=

to do a strict check against false, you must now check against NULL.

If you prefer different MySQL fetch methods, they're also in mysqli. Given the same query of

SELECT
username FROM users

, these example functions all print the same results:

// Fetch numeric arrays:
while ($row = mysqli_fetch_row($result)) {
    print $row[0] . "\n";
}

// Alternative syntax:
while ($row = mysqli_fetch_array($result, MYSQLI_NUM)) {
    print $row[0] . "\n";
}

// Alternative associative array syntax:
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    print $row['username'] . "\n";
}

// Both numeric and associative:
while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)) {
    print $row[0] . "\n";
    print $row['username'] . "\n";
}

// Fetch as "object"
while ($row = mysqli_fetch_object($result)) {
    print $row->username . "\n";
}

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Before you can issue queries, you need to connect to the MySQL server. This sounds so basic that you'd think it would be the same in both versions of the MySQL extension, but there are quite a few changes.

Connecting isn't just specifying the location of your database and providing a username and password. You also specify a variety of configuration options, such as whether to use SSL and the number of seconds before the connection times out.

The mysql connection functions take five parameters:

mysql_connect(server, username, password, new_link, client_flags)

All of these parameters are actually optional, because the extension defaults to values specified in a series of MySQL-related configuration directives, such as mysql.default_host.

The server parameter is usually the same as the host, but you can also append a port name or a path to a socket. For example, if your database runs on port 3307 on db.example.org:

mysql_connect('db.example.org:3307');

Separate the hostname and port with a colon (:) so PHP can tell them apart.

The username and password variables are not the username and password for your local account, but for MySQL's account system.

By default, if you try to reconnect to the same database with the same set of credentials, PHP will reuse the existing connection. Setting new_link to true forces PHP to always make another link to MySQL.

Use the final parameter, client_flags, to control the session. You can modify it by passing any combination of the following three constants: MYSQL_CLIENT_COMPRESS, MYSQL_CLIENT_IGNORE_SPACE, and MYSQL_CLIENT_INTERACTIVE. These tell MySQL to compress the connection, ignore spaces after functions, and modify how it determines when to close the connection, respectively.

Under mysqli

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The mysqli extension also allows you to manipulate the data using an object-oriented interface. Everything that can be done using the procedural interface is available this way. Here is the same sample transaction from the last section translated into the OO style:

$mysqli = new mysqli('db.example.org', 'web', 'xyz@123');
$mysqli->select_db('users');

$result = $mysqli->query("SELECT username FROM users");

while ($row = $result->fetch_assoc( )) {
    print $row['username'] . "\n";
}

$result->close( );

With the OO interface, there's no need to pass database handles as the first parameter to a mysqli method. Instead, the extension stores that handle as part of the mysqli object.

If you're porting code from mysql and use the default link option, you may find it easier to switch to the OO syntax because it doesn't require you to modify the argument list of every mysql function.

You can also use mysqli_init( ) and mysqli_real_connect( ):

$mysqli = new mysqli( );
$mysqli->init( );
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 120);
$mysqli->options(MYSQLI_OPT_LOCAL_INFILE, false);

$mysqli->real_connect('db.example.com', 'web', 'xyz@123', 'users', 
                       3306, NULL, MYSQLI_CLIENT_COMPRESS);

A new mysqli object does not automatically call mysqli_init( ) in its constructor. This is still your responsibility.

Certain mysqli functions are object properties instead of methods, in particular, mysqli_error( ),

mysqli_errno(
)

, and mysqli_insert_id( ). Here's an example using

mysqli_insert_id(
)

:

// place new e-mail address on list:
$email = 'rasmus@php.net';
$list  = 'php-general';

// escape data
$email = $mysqli->real_escape_string($email);
$list =  $mysqli->real_escape_string($list);

$mysqli = new mysqli($server, $user, $password);
$mysqli->query("INSERT INTO addresses VALUES(NULL, '$email')");
$id = $mysqli->insert_id; // no ( ) necessary!
$mysqli->query("INSERT INTO lists VALUES($id, '$list')");

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

One way to speed up MySQL is to use prepared statements. Every time you make an SQL query, MySQL parses the request, checks its validity, and executes it. While MySQL does this efficiently, prepared statements make this process even faster.

A prepared statement is a way to tell MySQL what your query will look like before you actually execute it. This description doesn't contain the exact query; it's more like a query template. Most of the query is hardcoded, but there are placeholders where you want to customize the information.

You pass this template to MySQL. It parses and validates the query, and returns a statement handle. You then use that handle to execute the request.

In contrast to a traditional query, when you execute this type of request, there's no need to parse the SQL. As a result, MySQL executes the query faster. If you make the same query more than once, it's faster to use a prepared statement than a direct query. Additionally, prepared statements automatically escape quotes, so you don't need to worry about stray characters. This is a big benefit that makes prepared statements worthwhile even for single queries.

Prepared statements also let you control data retrieval. There's no longer a need to first retrieve a row into an array and then assign each element to an individual variable. You can instruct MySQL to place each piece of data directly into a variable.

Example 3-1 demonstrates the traditional way to insert data into a table.

Example 3-1. Executing a traditional query

// User-entered data
$username = 'rasmus';
$password = 'z.8cMpdFbNAPw';
$zipcode  = 94088;

// Escape data
$username = mysqli_real_escape_string($db, $username);
$password = mysqli_real_escape_string($db, $password);
$zipcode  = intval($zipcode);

// Create SQL query
$sql = "INSERT INTO users VALUES ('$username', '$password', $zipcode)";

// Make SQL query
mysqli_query($db, $sql) or die('Error');

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Subselects are a popular database feature that's available in MySQL 4.1. A subselect, or a subquery, is a query that occurs within the context of another query. You then use the subselect's results in the main query.

Many developers like subselects because they allow them to chain queries together to winnow results. It's often possible to rewrite a query to eliminate a subselect; however, this is not always straightforward or efficient. Additionally, sometimes, without a subselect, you'll be forced to make multiple requests.

The following sections contain a few examples that show how a subselect can solve problems. Many of them use the following programs table:

mysql> DESCRIBE programs;
+------------+-----------------+------+-----+---------+----------------+
| Field      | Type            | Null | Key | Default | Extra          |
+------------+-----------------+------+-----+---------+----------------+
| id         | int(5) unsigned |      | PRI | NULL    | auto_increment |
| title      | varchar(50)     |      |     |         |                |
| channel_id | int(5) unsigned |      |     | 0       |                |
+------------+-----------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM programs;
+----+-----------------+------------+
| id | title           | channel_id |
+----+-----------------+------------+
|  1 | Oprah           |         60 |
|  2 | Sex and the City|        201 |
|  3 | The Sopranos    |        201 |
|  4 | Frontline       |         13 |
+----+-----------------+------------+
4 rows in set (0.00 sec)

A common database-related task is finding rows that match a set of criteria. Normally, these specifications are known ahead of time:

// Find the names and address of all people 
// who have an email address ending in "php.net".
SELECT name, email FROM users WHERE email LIKE '%.php.net';

// Find the title of all TV programs that air on channel 13
SELECT title
  FROM programs, channels
 WHERE channel.name = 'HBO' 
   AND program.channel_id = channel.id;

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

MySQL 4.0 supports database transactions. Transactions allow you to group together a sequence of database queries to ensure that MySQL either completes the entire set of actions or fails to perform any of them. There's no in-between state.

A perfect example demonstrating the importance of transactions is transferring money from a bank account. Removing money takes two steps: first, the program checks whether the account contains enough funds; if it does, it then subtracts the money from the account and places it in another.

You don't want a second withdrawal to occur in between checking the account balance and removing the funds, because the other transaction could completely empty the account, leaving no more money for your request. This is a big problem for banks.

In order to support transactions, MySQL introduced a new database table format, called InnoDB. The original format, MyISAM, can't be used with transactions. If you're running MySQL 4.0 or higher, you should have support for InnoDB tables. To check, run the following MySQL query:

mysql> SHOW VARIABLES LIKE 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | YES   |
+---------------+-------+

It should say YES. By default, MySQL enables InnoDB tables as of Version 4.0, so if this variable is set to NO, you should rebuild MySQL and remove the --without-innodb flag from your configuration.

MySQL also supports transactions with Berkeley DB tables in MySQL 3.23, but that's not covered here.

To create an InnoDB table, add TYPE = InnoDB to the end of the CREATE statement. For example, to replicate the programs table:

CREATE TABLE   programs (id int(5) unsigned AUTO_INCREMENT, 
                         title VARCHAR(50) not null,
                         channel_id int(5) unsigned not null,
                         primary key(id))
        TYPE = InnoDB;

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In PHP 4, when you need to make more than one query in a row, you're forced to call

mysql_query(
)

once for each query. This often happens when you need distinct pieces of information from MySQL or when you want to create a fresh set of tables.

This isn't a big problem when you know your queries ahead of time, because it's easy to loop through and send them to MySQL one at a time. However, for some situations, this is not the case. For example, you're writing a PHP frontend to MySQL, such as phpMyAdmin (http://www.phpmyadmin.net), and want the ability to take a database dump and recreate the information.

Without the ability to send the entire dump at once, you're required to parse the data into individual statements. That's not as easy as it sounds, because you can't just split on the semicolon (;). For example, it's perfectly valid to have a line like

INSERT INTO users
VALUES('rasmus', 'pass;word');

. Since pass;word is inside single quotes, MySQL knows it doesn't signal the end of a statement, but a simple split isn't smart enough to understand this. As a result, you're effectively forced to write a MySQL parser in PHP.

The restriction of one query per request is lifted in MySQLi. This actually wasn't a limitation in PHP, but a deficit in the protocol used by earlier versions of MySQL. Another benefit of MySQL 4.1's updated protocol is the ability to introduce a mysqli_multi_query( ) function for these types of cases.

While this is good news, it also introduces a greater potential for SQL injection attacks. An SQL injection attack is when a malicious person passes in data that alters your SQL so that you end up issuing a different (and probably more dangerous) SQL query than what you expected.

Here's an example that's supposed to return the bank account information for only a single user:

$sql = "SELECT account_number, balance FROM bank_accounts 
        WHERE secret_code LIKE '$_GET[secret_code]'";

You know that it's important to use a secret code so that people can't access other people's information by incrementing a primary key ID number, because those numbers are easy to guess. However, you've forgotten to call

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You can now encrypt the connections between PHP and MySQL. Normally, MySQL does all communication in plain text because it's the fastest way to send data. However, MySQL 4.0 lets you use SSL encryption to prevent people from spying on traffic between PHP and MySQL, and MySQL 4.1 extends this to include replication over SSL.

An SSL-enabled version of MySQL doesn't require you to use SSL for all your connections. You can set different permission levels on your accounts, so you can place varying restrictions as you see fit. For some accounts, you may not want the hassle of dealing with SSL and authentication.

Once all your systems are configured, it's quite easy to use SSL with MySQLi. Getting everything up and running can be a bit of a struggle because you need to add OpenSSL support to both MySQL and PHP, create SSL certificates for MySQL, and also properly configure your MySQL user accounts and configuration files.

Here's a list of what you need to do:

Install OpenSSL if your system doesn't already have it.
Add SSL support to MySQL and reinstall.
Add SSL support to PHP, link against the new MySQL client, and reinstall.
Create SSL certificates for the MySQL server.
Add SSL certificate information to your my.cnf files.
Restart MySQL.
Edit the MySQL GRANT table to require SSL.
Connect to MySQL from PHP using SSL.

Before you can do anything, you need to have OpenSSL on your machine. Most systems come with OpenSSL preinstalled, but you can download a copy from http://www.openssl.org/.

The second step is to check whether your MySQL server already supports SSL. Issue the following command:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Although the new mysqli extension has many benefits over the older extension, they come at a cost. Depending upon your code, porting to the new extension can be a surprisingly tiresome task. If you've used a database abstraction layer—such as PEAR DB, ADOdb, or MDB—this task is simplified considerably. Even though you're only switching from one version of MySQL to another, the new extension is sufficiently different that quite a bit of work is necessary to make the switch.

However, you probably will want to use mysqli for your new projects, and that requires an upgrade to MySQL 4.1. Since an out-of-the-box MySQL 4.1 is incompatible with mysql, this presents a bit of a dilemma. You want the benefits of mysqli without worrying about legacy code.

Another option is just to make the switch to a database abstraction layer. This has some advantages and some disadvantages. The primary benefit is portability, but this comes at a cost of speed and features. For instance, you cannot bind output parameters with PEAR DB, nor does DB support all of the latest MySQLi options, such as mysqli_multi_query( ) and

ssl_set(
)

.

This section presents a variety of options for handling the conversion process. Each one has various positive and negative attributes. Some require more hours of work; others are faster to implement, but at a cost of execution speed. None of these solutions are bad in and of themselves, but, given certain circumstances, some should be preferable to others. In the end, you must weigh the trade-offs and make the decision for yourself.

The biggest problem is that you're really making three migrations instead of one. You're migrating from:

PHP 4 to PHP 5
MySQL 3.2.x (or MySQL 4.0) to MySQL 4.1
mysql to mysqli

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Substituting text files for a database is like cutting a fish with a hammer. You might get it to work, but it's going to be a really messy process. When your application needs a server-side storage mechanism but you can't rely upon the presence of a specific database, turn to SQLite. It correctly handles locking and concurrent accesses, the two big headaches with home-brewed flat files.

Since the SQLite database is bundled with PHP 5, now every PHP 5 script can read, write, and search data using SQL. SQLite differs from most databases because it is not a separate application. Instead, SQLite is an extension that reads from and writes to regular files on the hard drive. Any PHP users who have permission to manipulate files can use SQLite to edit a database, just like they can use GD to edit images.

Although the name SQLite hints at a less than full-featured product, SQLite actually supports almost all of SQL92, the SQL standard specification. Besides the usual INSERTs and SELECTs, with SQLite you can also use transactions, query using subselects, define your own functions, and invoke triggers.

SQLite actually performs most actions more quickly than many other popular databases. In particular, SQLite excels at SELECTing data. If your application does an initial (or periodic) data INSERT and then reads many times from the database, SQLite is an excellent choice. The PHP web site uses SQLite to handle some forms of searches.

Unfortunately, SQLite has some downsides. Specifically, when you update the database by adding new data, SQLite must lock the entire file until the alteration completes. Therefore, it does not make sense in an environment where your data is constantly changing. SQLite does not have any replication support, because there's no master program to handle the communication between the master database and its slaves.

Additionally, SQLite has no concept of access control, so the GRANT and REVOKE keywords are not implemented. This means you cannot create a protected table that only certain users are allowed to access. Instead, you must implement access control by using the read and write permissions of your filesystem.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

It's easy to get up and running with SQLite. Its design eliminates the need for any configuration variables, such as a database server name or a database username and password. All you need is the name of a file where the data is stored:

$db = sqlite_open('/www/support/users.db');
sqlite_query($db, 'CREATE TABLE users(username VARCHAR(100),
                                      password VARCHAR(100))');

This creates a users table stored in the database file located at /www/support/users.db. When you try to open a database file that doesn't already exist, SQLite automatically creates it for you; you don't need to execute a special command to initialize a new database.

If you cannot seem to get SQLite to work, make sure you have both read and write permission for the location on the filesystem where you're trying to create the database.

SQLite has even fewer data types than PHP—everything's a string. While you can define a column as INTEGER, SQLite won't complain if you then INSERT the string PHP into that column. This feature (the SQLite manual declares this a feature, not a bug) is unusual in a database, but PHP programmers frequently use this to their advantage in their scripts, so it's not a completely crazy idea. A column's type matters only when SQLite sorts its records (what comes first: 2 or 10?) and when you enforce UNIQUEness (0 and 0.0 are different strings, but the same integer).

The table created in this example has two columns: username and password. The columns' fields are all declared as VARCHARs because they're supposed to hold text. Although it doesn't really matter what type you declare your fields, it can be easier to remember what they're supposed to hold if you give them explicit types.

Add new rows to the database using INSERT and sqlite_db_query( ):

$username = sqlite_escape_string($username);
$password = sqlite_escape_string($password);

sqlite_query($db, "INSERT INTO users VALUES ('$userna

Table of Contents