Connecting and prepared statements with the mysqli extension

April 18, 2006

Change is afoot in the PHP/MySQL world. In quite a short space of time, we have moved from MySQL 3.23.x and PHP 4.x, to PHP 5.x and MySQL 4.1.x and now 5.0.x. I have written about some of changes in new versions of PHP, as well as in MySQL. The more grown-up a platform becomes, the more complex change becomes, as more and more attention is paid to legacy needs. Many hosts and installations are still using PHP4. However, MySQL's increased functionality has created a need for a new set of functions to take advantage of the increased functionality. So currently there are the MySQL functions (read the DatabaseJournal article here), used in legacy code and still quite a lot of new development. Then there is the mysqli extension, aimed at MySQL 4.1 and beyond. Finally, there is the PDO extension, which ships with PHP 5.1, and is available as a PECL extension for PHP 5.0.

Many legacy developments are stuck with the old functions, of which there is ample documentation. PHP 5.1 is still very new, and as of yet not too many hosts are offering it. Therefore, this article will focus on the mysqli extension. In particular, we look at the minor changes when connecting, and then, more interestingly, at prepared statements.

I-dentical

Procedural coders will find the basics almost identical. Where before you would used a function such as mysql_connect(), the new function is simply mysqli_connect(). Most of the old mysql_x functions have equivalent mysqli_x versions. Here is a sample code snippet demonstrating connecting to a database, performing a simple query, returning a result set, displaying the results and tidying up afterwards:

// connect to the server and select a database
$dbh = mysqli_connect($hostname,$username,$password,$dbname) or 
  die("Problem connecting: ".mysqli_error());
//run a query, returning a result set
$result = mysqli_query($dbh,"SELECT field1,field2 FROM tablename");
// loop through the result set and display the results
while ($row = mysqli_fetch_assoc($result)){
  echo $row['field1'].$row['field2]."\n";

//free the memory and close the connection
mysqli_free_result($result);
mysqli_close($dbh);

The other functions for returning results also have equivalents in mysqli. These include mysqli_fetch_row(), mysqli_fetch_array() (where you can choose either or both of numeric or associative results), and mysqli_fetch_object().

mysqli_connect

mysqli_connect() is not quite identical to the old mysql_connect. Instead of 5 parameters, it takes 6, all optional.

mysqli_connect (string host, string username, string password, string dbname, int port, string socket)

As you can see from the earlier code snippet, one change, a vast improvement in my opinion, is that mysqli_connect() now takes a database name as an argument. Since you can directly connect to a particular database from the command line, it makes sense that PHP accommodates this too, without the need for a separate call to mysql_select_db, or mysqli_select_db() (which exists too).

Other changes are that the port and socket can no longer be added in the host string - they must now be included as the fifth or sixth arguments (you would only need to include either a port or a socket). Also, mysql_connect() originally had an optional fifth argument for client flags. They haven't disappeared with mysqli, but if you want to use them, you will need to connect in a slightly different way, using a combination of mysqli_init() and mysql_real_connect()

// create connection object, not yet connected
$dbh = mysqli_init();
//connect to server
mysqli_real_connect($dbh,$hostname,$username,$password,$dbname,$port,$socket,$flags);
?>

The following flags are supported. All but one were supported (using their MYSQL equivalents) by the old MySQL functions.

Name

Description

MYSQLI_CLIENT_COMPRESS

Use compression protocol

MYSQLI_CLIENT_FOUND_ROWS

Return number of matched rows instead of affected rows

MYSQLI_CLIENT_IGNORE_SPACE

Allow spaces after function names (making them all reserved words). This one was not supported by mysql_connect()

MYSQLI_CLIENT_INTERACTIVE

Use interactive_timeout seconds instead of wait_timeout seconds when determining the wait before closing connections.

MYSQLI_CLIENT_SSL

Use SSL encryption

One of the ongoing debates floating around has been that around the merits of mysql_connect as opposed to mysql_pconnect. No longer! There is no function for calling persistent connections, as the consensus seems to have been that on high-volume sites, they did more harm than good, and there is no need for them on low-volume sites. The connection resource for MySQL has always been quite light.

mysqli and object-orientation

Being a child of PHP 5, mysqli is designed with an object-oriented paradigm in mind, and allows a developer to work with an object-oriented interface. It is quite similar, although slightly more elegant (even for this old procedural-hacker) in that there is no need to pass the database handle for every function call, as the instantiated object takes care of that. Here is an example of it in action:

$mysqli = new mysqli($host,$username,$password,$dbname);
$result = $mysqli->query("SELECT field1,field2 FROM tablename");
while ($row = $result->fetch_assoc( )) {
  echo $row['field1'].$row['field2]."\n";

$result->close( );

Further options when connecting

You can set further options when connecting, using either the mysqli_options() function, or the options method. These include:

Name

Description

MYSQLI_OPT_CONNECT_TIMEOUT

Set the connection timeout, in seconds

MYSQLI_OPT_LOCAL_INFILE

Allow (true) or disallow (false) LOAD LOCAL INFILE

MYSQLI_INIT_COMMAND

Run this command after connecting to the server.

MYSQLI_READ_DEFAULT_FILE

Read mysql variables from the named file rather than the usual config file (usually my.cnf).

MYSQLI_READ_DEFAULT_GROUP

Read mysql variables from the named group from the config file or the file specified with MYSQL_READ_DEFAULT_FILE.

Here are some examples, in both a procedural and object-oriented style. First procedurally:

$dbh = mysqli_init( );
// Set a new config file, disallow LOAD LOCAL INFILE and set the timeout to 600 seconds
mysqli_options($dbh, MYSQLI_READ_DEFAULT_FILE, '/home/phpb/mynew.cnf');
mysqli_options($dbh, MYSQLI_OPT_LOCAL_INFILE, false);
mysqli_options($dbh, MYSQLI_OPT_CONNECT_TIMEOUT, 600);
// Connect with the above options, as well as SSL
mysqli_real_connect($dbh,$host,$username,$password,$dbname,$port,$socket, MYSQLI_CLIENT_SSL);

And then in an object-oriented style:

// Instantiate object
$mysqli = new mysqli( );
//Call the init method to allow setting of options
$mysqli->init( );
// Set a new config file, disallow LOAD LOCAL INFILE and set the timeout to 600 seconds
$mysqli->options(MYSQLI_READ_DEFAULT_FILE, '/home/phpb/mynew.cnf');
$mysqli->options(MYSQLI_OPT_LOCAL_INFILE, false);
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 600);
// Connect with the above options, as well as SSL
$mysqli->real_connect($host,$username,$password,$dbname,$port,$socket,MYSQLI_CLIENT_SSL);

Prepared Statements: Bound parameters

A completely new enhancement, bringing MySQL/PHP in line with many other platforms, is the ability to run prepared statements. Before, PHP could only pass a fully formed SQL statement to MySQL as a query to be run. Now, PHP can pass a query without any data to MySQL. It does this once, and MySQL does some of the work in preparing to run the query at this point. Next, you associate variables to the columns, which is called binding the parameters. Finally, the query is executed. Why go to this trouble? It can provide a substantial performance increase, is more convenient for the developer, as well as more secure. It may seem a bit strange if this is the first time you are encountering it, but it is actually quite simple. Here is an example:

// Connect to the server and select a database
$dbh = mysqli_connect($hostname,$username,$password,$dbname) or 
  die("Problem connecting: ".mysqli_error());
  
//Create the SQL statement, with ? to reflect the parameters to be supplied later.  
$sql = 'INSERT INTO tablename VALUES(?, ?)';
//  Allocates and initializes a statement object suitable for mysqli_stmt_prepare().
$stmt = mysqli_stmt_init($dbh);
// Prepare statement, bind parameters (an integer and a string) and execute the statement
// 'is' tells MySQL you're passing an integer(i) and a string (s)
if (mysqli_stmt_prepare($stmt, $sql)) {
  mysqli_stmt_bind_param($stmt, 'is', $field1, $field2);
  
  // Set parameters and execute 1
  $field1 = 1;
  $field2 = 'first';
  mysqli_stmt_execute($stmt);
  
  // Set parameters and execute 2
  $field1 = 2;
  $field2 = 'second';
  mysqli_stmt_execute($stmt);

Everything seems the same up until the first question mark character in the SQL statement. The ? character is used to indicate a parameter that will later be bound to the query. The mylsqi_stmt_init() function must be called once to initialize everything. The mysqli_stmt_bind_param() function probably needs the most explanation. It binds the parameters to the query, and lets the database know what type those parameters are. The second argument ('is' in the above example) lists the types for each parameter. i tells MySQL the first parameter (field1) is an integer, and s that the second one (field2) is a string. Here is the full list of possibilities.

Character

Description

i

integer

d

double

s

string (and other types)

b

blob

Therefore, there needs to be one letter corresponding to each parameter. Finally, the statement is executed, and INSERT will actually take place.

Here is the same functionality in an object-oriented style:

// Connect to the server and select a database
$mysqli = new mysqli($host,$username,$password,$dbname);
//Prepare the SQL statement, with ? to reflect the parameters to be supplied later.
$stmt = $mysqli->prepare("INSERT INTO tablename VALUES (?, ?)");
// Bind parameters (an integer and a string). 'is' tells MySQL you're passing an integer(i) and a string(s)
$stmt->bind_param('is', $field1,$field2); 
// Set parameters and execute 1
$field1 = 1;
$field2 = 'first';
$stmt->execute(); 
// Set parameters and execute 2
$field1 = 2;
$field2 = 'second';
$stmt->execute(); 

This may seem to be a great amount of effort to accomplish something normally done much more simply. So why do it? Security is one aspect. SQL injection attacks occur where the developer has not been careful enough to sanitize data supplied by a user. Prepared statements separate the data from the logic, and since MySQL knows exactly what type it is expecting, and which field this must correspond to, the risk of an SQL injection attack is minimized.

Secondly, the performance improvement. In the above examples, there were only two queries executed for the prepared statement. In practice, if you were running a loop with multiple queries, the performance benefit may be substantial, since the data sent to the server will be less (you're sending just the parameters each time, not the whole query), and the parsing time is also less, since much of the preparation work on the query has already been done, and won't need to be repeated.

Prepared Statements: Bound results

Bound results (also called bound output parameters, as opposed to the bound input parameters we have just looked at) are similar, except that you are binding the results from a query to a set of variables. Previously you had to explicitly assign results to a variable. Now, the mysqli_stmt_fetch() function will automatically populate the previously bound output parameters with the results. Here is an example:

// Connect to the server and select a database
$dbh = mysqli_connect($hostname,$username,$password,$dbname) or 
  die("Problem connecting: ".mysqli_error());
  
// Prepare statement
$sql = 'SELECT field1,field2 FROM tablenames';
// Allocates and initializes a statement object suitable for mysqli_stmt_prepare().
$stmt = mysqli_stmt_init($dbh);
// Prepare statement, bind result variables, execute and place results into bound result variables
if (mysqli_stmt_prepare($stmt, $sql)) {
  mysqli_stmt_bind_result($stmt, $field1,$field2);
  mysqli_stmt_execute($stmt);
  while (mysqli_stmt_fetch($stmt)) {
    echo "$field1 : $field2\n";
  }

And, in OO style:

// Connect to the server and select a database
$mysqli = new mysqli($host,$username,$password,$dbname);
// Prepare statement, execute, bind result variables, and place results into bound result variables
if ($stmt = $mysqli->prepare("SELECT field1,field2 FROM tablenames")) { 
  $stmt->execute();
  $stmt->bind_result($field1, $field2);
  while ($stmt->fetch()) { 
    echo "$field1 : $field2\n";
  }
// Connect to the server and select a database
$dbh = mysqli_connect($hostname,$username,$password,$dbname) or 
  die("Problem connecting: ".mysqli_error());
  
// Prepare statement
$sql = 'SELECT field1,field2 FROM tablenames';
// Allocates and initializes a statement object suitable for mysqli_stmt_prepare().
$stmt = mysqli_stmt_init($dbh);
// Prepare statement, bind result variables, execute and place results into bound result variables
if (mysqli_stmt_prepare($stmt, $sql)) {
  mysqli_stmt_bind_result($stmt, $field1,$field2);
  mysqli_stmt_execute($stmt);
  while (mysqli_stmt_fetch($stmt)) {
    echo "$field1 : $field2\n";
  }

Conclusion

Prepared statements are a huge leap forward for PHP and MySQL developers. With their added performance, security and convenience they make large-scale developments that much simpler. We will take a further look at the mysqli extension in the month's to come.

» See All Articles by Columnist Ian Gilfillan








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers