6.2. MySQL

MySQL and PHP have become the "bread and butter" of web application builders. It is the combination you are most likely to encounter today and probably for the years to come. Consequently, this is also the first database covered in this chapter.

This chapter focuses on the new mysqlior MySQL Improvedextension that is bundled with PHP 5. As mentioned in the chapter introduction, the mysqli extension requires that you use at least version 4.1.2 of the MySQL server.

6.2.1. MySQL Strengths and Weaknesses

This section contains some information about the strengths and weaknesses of MySQL.

6.2.1.1 Strength: Great Market Penetration

MySQL has the biggest market share of any open source database. Almost any web-hosting company can provide MySQL access, and books and articles about MySQL and PHP are abundant.

6.2.1.2 Strength: Easy to Get Started

After your database is set up and you have access to it, managing the database is straightforward. Initial access needs to be configured by a database administrator (if that person is not you).

Tools such as MySQL Administrator or phpMyAdmin let you manage your database.

6.2.1.3 Strength: Open-Source License for Most Users

MySQL comes with a dual licenseeither GPL or a commercial license. You can use MySQL under the GPL as long as you are not commercially redistributing it.

6.2.1.4 Strength: Fast

MySQL has always been relatively fast, much due to its simplicity. In the last few years, MySQL has gained foothold in the enterprise market due to new "enterprise class" features and general maturity without compromising performance for simple usage.

6.2.1.5 Weakness: Commercial License for Commercial Redistribution

If you bundle MySQL (server or client) with a commercial closed-source product, you need to purchase a license. MySQL AB have published a FOSS (Free or Open-Source Software) exception to MySQL's license that grants all free or open-source products an exception from this restriction.

6.2.1.6 Strength: Reasonable Scalability

MySQL used to be a lightweight database that did not have to drag around most of the expensive reliability features (such as transactions) of systems such as Oracle or IBM DB2. This was, and still is, one of the most important reasons for MySQL's high performance. Today, MySQL has evolved to almost match its commercial seniors in scalability and reliability, but you can still configure it for lightweight use.

6.2.2. PHP Interface

The mysqli PHP extension was written from the ground up to support the new features of the MySQL 4.1 and 5.0 Client API. The improvements from the old mysql extension include the following:

• Native bind/prepare/execute functionality

• Cursor support

• SQLSTATE error codes

• Multiple statements from one query

• Index analyzer

The following sections give an overview of how to use the mysqli extension, and how it differs from the old mysql extension.

Almost every mysqli function has a method or property counterpart, and the following list of functions describes both of them. The notation for the methods is similar to $mysqli->connect() for regular methods, calling connect() in an instance of the mysqli class.

The parameter list is usually the same between mysqli functions and methods, except that functions in most cases have an object parameter first. Following that, function parameter lists are identical to that of their method counterparts. For the sake of brevity, ᭠replaces the method parameter list in the parameter descriptions.

6.2.3. Example Data

This section uses data from the "world" example database, available at http://dev.mysql.com/get/Downloads/Manual/world.sql.gz/from/pick.

6.2.4. Connections

Table 6.1 shows the mysqli functions that are related to connections.

mysqli_connect(...)
$mysqli = new mysqli(...)

mysqli_init()
$mysqli = new mysqli
mysqli_options(...)
$mysqli->options(...)
mysqli_real_connect(...)
$mysqli->real_connect(...)

mysqli_close(...)
$mysqli->close()

mysqli_get_host_info(...)
$mysqli->host_info

Here is a simple example:

<?php

$conn = mysqli_connect("localhost", "test", "", "world");
if (empty($conn)) {
    die("mysqli_connect failed: " . mysqli_connect_error());
}
print "connected to " . mysqli_get_host_info($conn) . "\n";
mysqli_close($conn);

Here, the mysqli_connect() function connects to "localhost" with the user name "test", an empty password, and selects the "world" database as the default database. If the connect fails, mysqli_connect() returns FALSE, and mysqli_connect_error() returns a message saying why it could not connect.

When using the object-oriented interface, you can also specify your connection parameters by passing them to the constructor of the mysqli object:

<?php

$mysqli = new mysqli("localhost", "test", "", "world");
if (mysqli_connect_errno) {
    die("mysqli_connect failed: " . mysqli_connect_error());
}
print "connected to " . $mysqli->host_info . "\n";
$mysqli->close();

Sometimes, you might need some more options when connecting to a MySQL server. In this case, you can use the mysqli_init, mysqli_options, and mysqli_real_connect functions, which allow you to set different options for your database connection. The following example demonstrates how you can use these functions:

<?php

$mysqli = mysqli_init();

$mysqli->options(MYSQLI_INIT_CMD, "SET AUTOCOMMIT=0");
$mysqli->options(MYSQLI_READ_DEFAULT_FILE, "SSL_CLIENT");

$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);

$mysqli->real_connect("localhost", "test", "", "world");
if (mysqli_connect_errno) {
    die("mysqli_connect failed: " . mysqli_connect_error());
}
print "connected to " . $mysqli->host_info . "\n";
$mysqli->close();

The mysqli_options functions allow you to set the options shown in Table 6.2.

MYSQLI_OPT_CONNECT_TIMEOUT

MYSQLI_OPT_LOCAL_INFILE
MYSQLI_INIT_CMD


MYSQLI_READ_DEFAULT_FILE
MYSQLI_READ_DEFAULT_GROUP

6.2.5. Buffered Versus Unbuffered Queries

The MySQL client has two types of queries: buffered and unbuffered queries. Buffered queries will retrieve the query results and store them in memory on the client side, and subsequent calls to get rows will simply spool through local memory.

Buffered queries have the advantage that you can seek in them, which means that you can move the "current row" pointer around in the result set freely because it is all in the client. Their disadvantage is that extra memory is required to store the result set, which could be very large, and that the PHP function used to run the query does not return until all the results have been retrieved.

Unbuffered queries, on the other hand, limit you to a strict sequential access of the results but do not require any extra memory for storing the entire result set. You can start fetching and processing or displaying rows as soon as the MySQL server starts returning them. When using an unbuffered result set, you have to retrieve all rows with mysqli_fetch_row or close the result set with mysqli_free_result before sending any other command to the server.

Which type of query is best depends on the situation. Unbuffered queries save you a lot of temporary memory when the result set is large, and if the query does not require sorting, the first row of results will be available in PHP while the MySQL database is actually still processing the query. Buffered queries are convenient because of the seeking feature; it could provide an overall speedup. Because each individual query would finish faster, the mysqli extension would drain the result set immediately and store it in memory instead of keeping the query active while processing PHP code. With some experience and relentless benchmarking, you will figure out what is best for you.

Another limitation for unbuffered queries is that you will not be able to send any command to the server unless all rows are read or the result set is freed by mysqli_free_result.

6.2.6. Queries

This section describes functions and methods for executing queries see Table 6.3).

mysqli_multi_query(...)
$mysqli->multi_query(...)

The mysqli_query() function returns a result set object. On failure, use the mysqli_error() function or the $conn->error property to determine the cause of the failure:

<?php

$conn = mysqli_connect("localhost", "test", "", "world");

$result = $conn->query("SELECT Name FROM City");
while ($row = $result->fetch_row()) {
    print $row[0] . "<br>\n";
}
$result->free();
$conn->close();

After the query has been executed, memory on the client side is allocated to retrieve the complete result set. To use unbuffered resultset, you have to specify the optional parameter MYSQLI_USE_RESULT:

<?php

$conn = mysqli_connect("localhost", "test", "", "world");

$result = $conn->query("SELECT Name FROM City", MYSQLI_USE_RESULT);
while ($row = $result->fetch_row()) {
    print $row[0] . "<br>\n";
}
$result->free();
$conn->close();

6.2.7. Multi Statements

The mysqli extension enables you to send multiple SQL statements in one function call by using mysqli_multi_query. The query string contains one or more SQL statements that are divided by a semicolon at the end of each statement. Retrieving result sets from multi statements is a little bit tricky, as the following example demonstrates:

<?php

$conn = mysqli_connect("localhost", "test", "", "world");

$query = "SELECT Name FROM City";
$query .= "SELECT Country FROM Country";

if ($conn->multi_query($query)) {
    do {
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                 printf("Col: %s\n", $row[0];
            }
            $result->close();
        }
    } while ($conn->next_result());
}
$conn->close();

6.2.8. Fetching Modes

There are three ways to fetch rows of results, as in the old mysql extension: as an enumerated array, as an associative array, or as an object (see Table 6.4).

mysqli_fetch_row(...)
$mysqli->fetch_row()

mysqli_fetch_assoc(...)
$result->fetch_assoc()

mysqli_fetch_object(...)
$result->fetch_object()

6.2.9. Prepared Statements

One of the major advantages of the mysqli extension as compared to the mysql extension are prepared statements. Prepared statements provide developers with the ability to create queries that are more secure, have better performance, and are more convenient to write.

There are two types of prepared statements: one that executes data manipulation statements, and one that executes data retrieval statements. Prepared statements allow you to bind PHP variables directly for input and output.

Creating a prepared statement is simple. A query template is created and sent to the MySQL server. The MySQL server receives the query template, validates it to ensure that it is well-formed, parses it to ensure that it is meaningful, and stores it in a special buffer. It then returns a special handle that can later be used to reference the prepared statement.

6.2.9.1 Binding Variables

There are two types of bound variables: input variables that are bound to the statement, and output variables that are bound to the result set. For input variables, you need to specify a question mark as a placeholder in your SQL statement, like this:

SELECT Id, Country FROM City WHERE City=?
INSERT INTO City (Id, Name) VALUES (?,?)

Output variables can be bound directly to the columns of the result set. The procedure for binding input and output variables is slightly different. Input variables must be bound before executing a prepared statement, while output variables must be bound after executing the prepared statement.

The process for input variables is as follows:

The process for output variables is as follows:

Executing a prepared statement or fetching data from a prepared statement can be repeated multiple times until the statement will be closed or there are no more data to fetch (see Table 6.5).

mysqli_prepare(...)
$mysqli->prepare()

mysqli_stmt_bind_result(...)
$stmt->bind_result(...)

mysqli_stmt_bind_param(...)
$stmt->bind_result(...)

mysqli_stmt_execute(...)
$stmt->execute

mysqli_stmt_fetch(...)
$stmt->fetch

mysqli_stmt_close(...)
$stmt->close()

Here is an example of a data manipulation query using bound input variables:

<?php

$conn = mysqli_connect("localhost", "test", "", "world");

$conn->query("CREATE TABLE alfas ".
             "(year INTEGER, model VARCHAR(50), accel REAL)");
$stmt = $conn->prepare("INSERT INTO alfas VALUES(?, ?)");
$stmt->bind_param("isd", $year, $model, $accel);

$year = 2001;
$model = '156 2.0 Selespeed';
$accel = 8.6;
$stmt->execute();

$year = 2003;
$model = '147 2.0 Selespeed';
$accel = 9.3;
$stmt->execute();

$year = 2004;
$model = '156 GTA Sportwagon';
$accel = 6.3;
$stmt->execute();

Here is an example of using binding for retrieving data:

<?php

$conn = mysqli_connect("localhost", "test", "", "test");

$stmt = $conn->prepare("SELECT * FROM alfas ORDER BY year");
$stmt->execute();
$stmt->bind_result($year, $model, $accel);
print "<table>\n";
print "<tr><th>Model</th><th>0-100 km/h</th></tr>\n";
while ($stmt->fetch()) {
    print "<tr><td>$year $model</td><td>{$accel} sec</td>\n";
}
print "</table>\n";

Here, we bind $year, $model, and $accel to the columns of the "alfas" table. Each $stmt->fetch() call modifies these variables with data from the current row. The fetch() method returns trUE until there is no more data, then it returns FALSE.

6.2.10. BLOB Handling

BLOB stands for Binary Large OBject and refers to binary data, such as JPEG images stored in the database.

6.2.10.1 Inserting BLOB Data

Previously, with the mysql PHP extension, BLOB data was inserted into the database directly as part of the query. You can still do this with mysqli, but when you insert several kilobytes or more, a more efficient method is to use the mysqli_stmt_send_long_data() function or the send_long_data() method of the stmt class.

Here is an example:

<?php

$conn = mysqli_connect("localhost", "test", "", "test");

$conn->query("CREATE TABLE files (id INTEGER PRIMARY KEY AUTO_INCREMENT, ".
             "data BLOB)");
$stmt = $conn->prepare("INSERT INTO files VALUES(NULL, ?)");
$stmt->bind_param("s", $data);
$file = "test.jpg";
$fp = fopen($file, "r");
$size = 0;
while ($data = fread($fp, 1024)) {
    $size += strlen($data);
    $stmt->send_long_data(0, $data);
}
//$data = file_get_contents("test.jpg");

if ($stmt->execute()) {
    print "$file ($size bytes) was added to the files table\n";
} else {
    die($conn->error);
}

In this example, the test.jpg file is inserted into the file's table by transferring 1,024 bytes at a time to the MySQL server with the send_long_data() method.

This technique does not require PHP to buffer the entire BLOB in memory before sending it to MySQL.

6.2.10.2 Retrieving BLOB Data

Retrieving BLOB data is the same as retrieving regular data. Use any of the fetch function/method variants as you see fit. Here is an example:

<?php

$conn = mysqli_connect("localhost", "test", "", "test");

if (empty($_GET['id'])) {
    $result = $conn->query("SELECT id, length(data) FROM files LIMIT 20");
    if ($result->num_rows == 0) {
        print "No images!\n";
        print "<a href=\"mysqli_blob1.php\">Click here to add one <a>\n";
        exit;
    }
    while ($row = $result->fetch_row()) {
        print "<a href=\"$_SERVER[PHP_SELF]?id=$row[0]\">";
        print "image $row[0] ($row[1] bytes)</a><br />\n";
    }
    exit;
}

$stmt = $conn->prepare("SELECT data FROM files WHERE id = ?");
$stmt->bind_param("i", $_GET['id']);
$stmt->execute();
$data = null;
$stmt->bind_result($data);
if (!$stmt->fetch()) {
    die("No such image!");
}

header("Content-type: image/jpeg");
print $data;