Chapter 24. Using MySQL with PHP

IN THIS CHAPTER

The MySQLi extension is a rewrite of the widely used MySQL extension that has existed for years. The extension looks to improve upon the great success of its predecessor in a number of ways. This chapter is designed to introduce you to the new extension, as well as discuss the similarities and differences between MySQLi and the MySQL extension.

At first glance, the MySQLi extension looks very similar to its predecessor. You'll be happy to find that most functions that were available in the previous MySQL extension will still be available in the MySQLi extension. These functions have a slight name change (for instance, instead of mysql_query() in the old extension, mysqli_query() is used), and the concept of a default link has been removed. That is to say, in the old MySQL extension (which did not require that you explicitly provide a database link resource), the MySQLi extension requires that a resource be associated with every function call as the first parameter. Listing 24.1 is an example of how the old MySQL extension was used to perform a query.

Listing 24.1. An Example of Using the Old MySQL Extension

<?php

    mysql_connect("localhost", "username", "password");
    mysql_select_db("mydatabase");

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

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

        foreach($row as $key=>$value) {

            echo "$key = $value<BR/>\n";

        }
    }

    mysql_free_result($result);
    mysql_close();  
?>

In PHP 5 using the MySQLi extension, this code would be rewritten as shown in Listing 24.2:

Listing 24.2. Rewritten for the MySQLi Extension

<?php

    $mysqli = mysqli_connect("localhost", "username", "password",
                             "mydatabase", 3306);

    $result = mysqli_query($mysqli, "SELECT * FROM mytable");

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

        foreach($row as $key => $value) {

            echo "$key = $value</BR>\n";

        }

    }

    mysqli_free_result($result);
    mysqli_close($mysqli);

?>

As you can see, for most legacy MySQL code the transition will be quite simple (by design).

As is the case with many of the new extensions within PHP 5, the MySQLi extension also supports a dual procedural/object-oriented syntax. In this syntax, link or result resources do not need to be specified; the methods are called directly from the result variables themselves, as shown in Listing 24.3.

Listing 24.3. Using the Object-Oriented Syntax in MySQLi

<?php

    $mysqli = new mysqli("localhost", "username", "password",
                         "mydatabase", 3306);


    $result = $mysqli->query("SELECT * FROM mytable");

    while($row = $result->fetch_array()) {

        foreach($row as $key => $value) {

            echo "$key = $value<BR/>\n";

        }

    }

    $result->close();
    $mysqli->close();

?>

NOTE

For the sake of simplicity, this chapter will focus only on the procedural syntax of MySQLi. For a complete reference to the object-oriented syntax, consult the PHP manual online at http://www.php.net/mysqli.

With all the improvements in the new MySQLi extension, there is one major backward-compatibility change from its predecessorit is compatible only with MySQL version 4.1 and later. For older versions of MySQL, the old PHP extension must still be used. Although somewhat inconvenient, this change means that many of the latest and greatest advances in the MySQL server can be used. These features, such as prepared statements, transactions, and more will be discussed throughout this chapter. But first, we need to explore the basics of using this powerful extension.