ext/mysqli: Part I – Overview and Prepared Statements

March 16, 2004

Tutorials

Intended Audience
Introduction

•  Major Goals
•  Major Features
•  Why Switch?
•  Warnings and Surprises
Show Me the Code!
•  Basic Usage
•  Using the Object-Oriented Interface
Prepared Statements
•  Bound Parameters

•  Bound Results
•  Using Bound Parameters and Bound Results Together
Summary
Glossary
About the Author



Intended Audience

The article is intended for readers with
some experience of using PHP and MySQL. It assumes that the basic concepts
behind databases and programming are understood, and that readers know how to
use a PHP script to send a query to a MySQL server.

Please note that there are footnotes to
help support certain passages, and that there is also a glossary at the end of
the document.

Full instructions on PHP and MySQL
installation are beyond the scope of this article.


Introduction

Since the mid-90s, ext/mysql has served
as the major bridge between PHP and MySQL. Although there have been a few
misfeatures and growing pains over the years, in general, ext/mysql has
performed its duty quite well, and kept pace with the changes both in PHP and in MySQL.

However, since the introduction of PHP 5 and MySQL 4.1, this has changed – a few rather
large cracks are starting to show.

There are existing misfeatures in ext/mysql, most notably
mysql_pconnect()[1]
and the automatic and default connections[2].
Additionally, incompatibilities between the feature set supported by ext/mysql
and that supported by the MySQL client library, which ext/mysql and ext/mysqli
both rely on, have arisen.

In an effort to correct these issues, Georg Richter has created a new MySQL extension
for PHP5 that will support new features in MySQL Version 4.1 and higher.

The extension is called ext/mysqli, with the ‘i’ standing for any one of: improved,
interface, ingenious, incompatible or incomplete.[3]


Major Goals

Some of the major design goals of the new extension were:

  • Easier maintainability The ext/mysql code base has become somewhat complex and messy.
    Major enhancements in the functionality of MySQL required that various feature sets be
    enabled or disabled based on the version of the client library. Other issues
    required that some features be disabled for particular operating systems.

  • Better compatibility The extension needed to conform more closely to the MySQL
    client library, so that future enhancements to the library could be supported more easily in PHP

  • Backwards compatibility Although the compatibility between ext/mysql and ext/mysqli
    is not perfect, effort was made to simplify porting applications from ext/mysql to ext/mysqli.


Major Features

ext/mysqli supports the new features
found in recent versions of MySQL and introduces a few features of its own.

The major features of the extension are:

  • A procedural interface that looks very much like the ext/mysql interface
  • An object-oriented interface that allows for a use idiom that is both more
    convenient and easier to extend than the procedural interface

  • Support for the new MySQL binary protocol that was introduced in MySQL 4.1. (The new
    protocol is more efficient that the old one and allows for the support of a
    broader range of features, such as prepared statements.)

  • Support for the full feature set of the MySQL C client library, including the ability to
    set advanced connection options via mysqli_init() and related functions.

Additionally, the extension has support for additional tracing, debugging, load balancing and
replication functionality.


Why Switch?

Beyond gaining access to the new features of MySQL 4.1+, why would anyone want to switch
to using ext/mysqli?

In addition to the functionality mentioned above, ext/mysqli also has some other serious
benefits:

  • Greater speed. Enhancements in both the extension and in MySQL have made most operations
    faster, with certain operations becoming up to 40 times faster as compared to ext/mysql.

  • Better security. In older versions of the MySQL RDBMS, the possibility existed for an
    attacker to extract weak password hashes from the network and then recreate a
    user’s password. The new authentication procedure is much more robust and
    mirrors the attack-resistant authentication procedure of tools like SSH.


Warnings and Surprises

Some aspects of ext/mysqli are quite
different from the old extension. In an effort to correct certain design flaws
or bug-prone behaviors, specific features have been removed:

  • Default database connection. If you do not explicitly connect to the server,
    ext/mysqli will not do it for you.

  • Default link. The database server connection that you wish to use must be explicitly
    referenced when you use ext/mysqli via its procedural context, i.e.
    mysqli_query($link, $query);

Note that open connections (and similar resources) are automatically destroyed at the end of script execution.
However, you should still close or free all connections, result sets and statement handles as soon as they
are no longer required. This will help return resources to PHP and MySQL faster.


Show Me the Code!

Now that you know why things have
changed, we should start reviewing code that demonstrates how the new extension
looks and operates. All standalone code in this article utilizes the
‘world’ database, which is freely available from
www.mysql.com.


Basic Usage

Here is a simple script that connects to
a MySQL server, sends a query to the server using the established connection,
displays the results of the query, and then discards the query result set and
closes the connection.



<?php

/* Connect to a MySQL server */

$link = mysqli_connect(

            
'localhost',  /* The host to connect to */

            
'user',       /* The user to connect as */

            
'password',   /* The password to use */

            
'world');     /* The default database to query */

if (!$link) {

   
printf("Can't connect to MySQL Server. Errorcode: %s\n", mysqli_connect_error());

   exit;

}

/* Send a query to the server */

if ($result = mysqli_query($link, 'SELECT Name, Population FROM City ORDER BY Population DESC LIMIT 5')) {

    print("Very large cities are:\n");

    /* Fetch the results of the query */

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

        
printf("%s (%s)\n", $row['Name'], $row['Population']);

    }

    /* Destroy the result set and free the memory used for it */

    
mysqli_free_result($result);

}

/* Close the connection */

mysqli_close($link);

?>

The above script should output something like:


Very large cities are:

Mumbai (Bombay) (10500000)
Seoul (9981619)
São Paulo (9968485)

Shanghai (9696300)
Jakarta (9604900)

As the code shows, ext/mysqli and ext/mysql can be quite similar. The only major differences
are that ext/mysqli is slightly more verbose when used in a procedural fashion.

Note that, without error checking, the above script could fail at any point and display an ugly
error message to the user.


Using the Object-Oriented Interface

The object-oriented interface provides a
slightly terser, and less error-susceptible, way to use ext/mysqli. The code
below performs the same tasks as the code above, however there are a few key
differences to note:

  • We do not need to explicitly specify the connection to use for our commands. The
    connection information is stored in our $mysqli and $result
    objects and is accessed as needed when methods are called.

  • When fetching rows of query data from the results set using fetch_assoc()
    we do not have to explicitly specify the result set handle to use. As with the
    connection information, the result handle is stored in the $result object.



<?php

/* Connect to a MySQL server */

$mysqli = new mysqli('localhost', 'user', 'password', 'world');

if (mysqli_connect_errno()) {

   
printf("Can't connect to MySQL Server. Errorcode: %s\n", mysqli_connect_error());

   exit;

}

/* Send a query to the server */

if ($result = $mysqli->query('SELECT Name, Population FROM City ORDER BY Population DESC LIMIT 5')) {

    print("Very large cities are:\n");

    /* Fetch the results of the query */

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

        
printf("%s (%s)\n", $row['Name'], $row['Population']);

    }

    /* Destroy the result set and free the memory used for it */

    
$result->close();

}

/* Close the connection */

$mysqli->close();

?>


Prepared Statements

Now that we have seen the basic use of the extension, let’s examine a few of the new features.

Prepared statements provide developers with the ability to create queries that are more secure, have better
performance, and are more convenient to write.

They come in two flavors: bound parameter prepared statements, and bound result prepared statements.


Bound Parameters

Bound parameter prepared statements allow
query templates to be created and then stored on the MySQL server. When a query
needs to be made, data to fill in the template is sent to the MySQL server, and
a complete query is formed and then executed.

The basic process for creating and using bound parameter prepared statements 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.

When a query needs to be made, data to fill in the template is sent to the MySQL
server, and then a complete query is formed and then executed.

This process has some very important behaviors wrapped up in it.

The body of the query is only sent to the
MySQL server once. On requests to execute the query, only the data to fill in
the template needs to be delivered to the MySQL server.

Most of the work required to validate and parse the query only needs to be done
a single time, instead of each time that the query is executed.

Additionally, for queries that contain a small amount of data, the overhead of
sending the query is greatly reduced. For example, if you have a query like:

INSERT INTO City (ID, Name) VALUES (NULL, ‘Calgary’);

then each time that you execute the query, you will only need to send about 16 bytes
of query data, instead of 60 or more bytes. (These approximate numbers include
overhead for the foo and bar query data like the id of the prepared statement,
the length of the query data for binary safety, etc, but do not include extra
overhead for the query string.)

The data for the query does not need to be passed through a function like
mysql_real_escape_string() to ensure that no SQL injection
attacks[4] occur.
Instead, the MySQL client and server work together to ensure that the sent data
is handled safely when it is combined with the prepared statement.

The query templates look something like:

INSERT INTO City (ID, Name) VALUES (?, ?);



The ‘?’ placeholders can be used in most places that could have literal data, e.g. a
query could be transformed from

SELECT Name FROM City WHERE Name = ‘Calgary’;


to

SELECT Name FROM City WHERE name = ?;



Here is a more complete example that
demonstrates the entire process:



<?php

$mysqli
= new mysqli('localhost', 'user', 'password', 'world');

/* check connection */

if (mysqli_connect_errno()) {

    
printf("Connect failed: %s\n", mysqli_connect_error());

    exit();

}

$stmt = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");

$stmt->bind_param('sssd', $code, $language, $official, $percent);

$code = 'DEU';

$language = 'Bavarian';

$official = "F";

$percent = 11.2;

/* execute prepared statement */

$stmt->execute();

printf("%d Row inserted.\n", $stmt->affected_rows);

/* close statement and connection */

$stmt->close();

/* Clean up table CountryLanguage */

$mysqli->query("DELETE FROM CountryLanguage WHERE Language='Bavarian'");

printf("%d Row deleted.\n", $mysqli->affected_rows);

/* close connection */

$mysqli->close();

?>

Note that bind_param() has a short string as its first parameter.
This is a format string that is used to specify how the data in the bound variables
should be treated.

In the case of the above script, ‘sssd’ indicates that the values of the
first three parameters $code, $language and $official will
be sent as a strings, while the fourth parameter $percent will contain a
double or float value.

For each bound variable in bind_param(), there should be another letter
in the format string that specifies how the variable should be handled. e.g.



$stmt->bind_param('s', $foo);

$stmt->bind_param('si', $foo, $bar);

$stmt->bind_param('sid', $foo, $bar, $baz);

The bind types let the mysqli extension know how to encode the data that it sends for
greater efficiency.

The type definitions are very simple: data in the bound variables will be treated as an
integer value, a rational number (double) or a string.

There is also a special type that allows long blobs to be sent to the MySQL server in chunks.

The following table shows the types and when to use them:



















BIND TYPE

COLUMN TYPE

i

   All INT types

d

   DOUBLE and FLOAT

b

   BLOBs

s

   All other types


Bound Results



Bound result prepared statements allow
the value of variables in a PHP script to be tied to the value of fields of data
in a query result set.

The process of setting up this binding is:

  • Create a query
  • Ask the MySQL server to prepare the query
  • Bind PHP variables to columns in the prepared query
  • Ask the MySQL server to execute the query
  • Request that a new row of data be loaded into the bound variables.

Here is a simple code snippet that illustrates this process:




<?php

$mysqli
= new mysqli("localhost", "user", "password", "world");

if (mysqli_connect_errno()) {

    
printf("Connect failed: %s\n", mysqli_connect_error());

    exit();

}

/* prepare statement */

if ($stmt = $mysqli->prepare("SELECT Code, Name FROM Country ORDER BY Name LIMIT 5")) {

    
$stmt->execute();

    /* bind variables to prepared statement */

    
$stmt->bind_result($col1, $col2);

    /* fetch values */

    
while ($stmt->fetch()) {

        
printf("%s %s\n", $col1, $col2);

    }

    /* close statement */

    
$stmt->close();

}

/* close connection */

$mysqli->close();

?>


Using Bound Parameters and Bound Results Together



Here is a more complete example that
demonstrates the use of both bound parameters and bound
results:




<?php

$mysqli
= new mysqli("localhost", "user", "password", "world");

if (mysqli_connect_errno()) {

    
printf("Connect failed: %s\n", mysqli_connect_error());

    exit();

}

/* prepare statement */

if ($stmt = $mysqli->prepare("SELECT Code, Name FROM Country WHERE Code LIKE ? LIMIT 5")) {

    $stmt->bind_param("s", $code);

    
$code = "C%";

    $stmt->execute();

    /* bind variables to prepared statement */

    
$stmt->bind_result($col1, $col2);

    /* fetch values */

    
while ($stmt->fetch()) {

        
printf("%s %s\n", $col1, $col2);

    }

    /* close statement */

    
$stmt->close();

}

/* close connection */

$mysqli->close();

?>


Summary



In this article, we have provided an overview of the features and architecture of
ext/mysqli, along with a quick summary of its development history. You should now
understand how to use and benefit from MySQL’s prepared statements, and should be
fairly comfortable using the object-oriented interface to ext/mysqli.


Glossary



ext/mysql – PHP’s old MySQL extension. Does not support the full feature set of
MySQL version 4.1

ext/mysqli - The new MySQL extension for PHP 5. Supports the features available
in MySQL versions 3.22 to 5.0

MySQL client library - The component of the MySQL RDBMS[*] that allows programs
to communicate with the RDBMS

MySQL server - The component of the MySQL RDBMS that does the work of processing and
responding to queries, managing the disk-level representation of data within the database, etc.


[1] – The
mysql_pconnect() function was designed to provide a mechanism for reducing the
cost of establishing and closing connections to the MySQL server. Unfortunately,
due to an interaction between the architecture of the Apache server and the
architecture of PHP, high traffic on a site that used pconnects could quickly
clog up the MySQL server with many unused connections that could prevent many of
the active connections from accessing the database.

[2] – The
automatic connection features allowed certain function calls to automatically
connect to the database (as long as valid connection data was stored in the
php.ini configuration file). The default connection feature operated so that the
last opened connection to a MySQL database would be the connection used for
database operations unless a connection parameter were explicitly specified in
the function arguments.

[3] – This
extension is still in development. While the core feature set should be fairly
stable, neither MySQL 4.1 nor PHP 5.0 are stable software releases. Also, the
supporting feature set that does not cleanly map to the MySQL client library is
still in development.

[4] – A SQL
injection attack occurs when data is input into a query that cases the query to
behave in an unexpected and/or malicious way. For example, given a simple query
in a PHP script like “DELETE FROM grades WHERE class_name=’test_$class’”, an
attacker who can gain control over the value of $class can force unintended
deletes to occur by changing the value of $class to something like “oops’ or
class_name LIKE ‘%’”.


About the Authors

Zak Greant is a professional Open Source
advocate, writer and programmer. He works for MySQL AB as their Community
Advocate. Zak is a maintainer of both PHP’s MySQL extensions, and is also
a co-author of
PHP Functions Essential Reference.

Georg Richter is the author of the mysqli
extension, and also maintains the mysql and ncurses extensions. He works for
MySQL AB as a Senior Developer, and is a member of the Apache Software Foundation.

About Cal Evans

Many moons ago, at the tender age of 14, Cal touched his first computer. (We're using the term "computer" loosely here, it was a TRS-80 Model 1) Since then his life has never been the same. He graduated from TRS-80s to Commodores and eventually to IBM PC's. For the past 10 years Cal has worked with PHP and MySQL on Linux OSX, and when necessary, Windows. He has built on a variety of projects ranging in size from simple web pages to multi-million dollar web applications. When not banging his head on his monitor, attempting a blood sacrifice to get a particular piece of code working, he enjoys building and managing development teams using his widely imitated but never patented management style of "management by wandering around". Cal is currently based in Nashville, TN and is gainfully unemployed as the Chief Marketing Officer of Blue Parabola, LLC. Cal is happily married to wife 1.28, the lovely and talented Kathy. Together they have 2 kids who were both bright enough not to pursue a career in IT. Cal blogs at http://blog.calevans.com and is the founder and host of Day Camp 4 Developers

View all posts by Cal Evans

8 Responses to “ext/mysqli: Part I – Overview and Prepared Statements”

  1. _____anonymous_____ Says:

    I’ve been looking everywhere for this. Thanks for creating such a wonderfully illustrated explanation! You made my week!

  2. _____anonymous_____ Says:

    This is a great extension. Great work. You clearly the benefits. Everyone should be encouraged to start using this extension.

  3. _____anonymous_____ Says:

    I am a newbie to both PHP and MySQL. This tutorial is exceptionally well written. It is so easy to read and understand. I also find it very informative. I spent hours going through books and web sites trying to understand the topic. The tutorial has given me the very knowledge I need.

    Thank you very very much.

  4. _____anonymous_____ Says:

    I am a newbie to both PHP and MySQL. This tutorial is exceptionally well written. It is so easy to read and understand. I also find it very informative. I spent hours going through books and web sites trying to understand the topic. The tutorial has given me the very knowledge I need.

    Thank you very very much.

  5. _____anonymous_____ Says:

    A simple, concise, highly informative, no nonsense introduction to mysqli. Excellent work!

  6. _____anonymous_____ Says:

    An Excellent article. I am new to PHP amd MySQL, but I am on fast track after reading this article. Great jpb. Keep it up!
    commander_hyd@yahoo.co.in

  7. fastpro Says:

    Great stuff, thanks for this helpfull tutorial and usefull instructions. I was looking for a solution for stored procedures, PHP 5 and MySql 5.
    Best regards Claudio

  8. nownetcn Says:

    how about db pool ?
    apache 2.2.x is supported db pool
    how about mysqli ?
    Is it support db pool ?