Smoothly Migrate your PHP Code using the Old MySQL extension to MySQLi - PHP MySQL to MySQLi package blog

Connecting Resources versus Objects

Migrating to MySQLi Procedural Methods

Dealing With the More Difficult Methods

The Red-Headed Step-Children

Conclusion

Introduction

In this article I will discuss some of the techniques that I use to make the process of migrating MySQL to MySQLi as painless as possible by using the MySQLi procedural style which is very similar to the MySQL extension.

Connecting Resources versus Objects

The first thing we need to look at is that MySQL is a resource and MySQLi is an object. To migrate our code, we really do not need to understand the technical difference, however we must understand that they are different.

The first thing we usually do with MySQL is to connect to and select a database, so let's take a look at mysql_connect and mysql_select_db.

$connection = mysql_connect( 'host', 'username', 'password', new_link,flags);
$database = mysql_select_db( 'database', $link);

$connection is a MySQL link identifier to the resource and $database is just a boolean variable that will contain true on success or false on failure. In most situations your host will be localhost and you will only have supplied your username and password.

Now let's take a look at its counter-part in MySQLi, mysqli_connect.

$connection = mysqli_connect( 'host', 'username', 'password', 'database', 'port', 'socket');

$connection is a link to the MySQLi object for this connection. If your connection using mysql only uses the host, username and password, then updating your code is as simple as changing mysql_connect to mysqli_connect.

You could also go ahead and add the database to connect to right there in the mysqli_connect parameters and eliminate mysql_select_db. This is OK if there is no variable to store the result, however if a variable was used it is possible that there may be logic somewhere deep in the code that will be using this variable to check the valid connection to the database. In these instances I recommend using mysqli_select_db.

$database = mysqli_select_db($link, 'database');

With MySQL, you where not required to provide a link, the last opened connection was used if the link was not specified. When using MySQLi, the link is required and as you can see, it is now the first parameter.

Using our examples, this $link is connection and our database name would remain the same. $database is still a boolean variable, so if it is referenced anywhere else in the code, it will operate as expected.

In case your connection is not the simple standard one we have just gone through, we need to go back and look at mysql_connect again. The host parameter may contain a port number, localhost:3307, or a socket, localhost:/path/to/mysql.sock. When migrating these to mysqli_connect, you would simply move the port or socket to the port and socket parameters.

You may have the new_link flag set, which allowed MySQL to open a new connection instead of using the one previously opened. Then whichever link was being used would be named as the link parameter. When migrating these, we simply create a new MySQLi object with the same link name. To illustrate this...

$connection2 = mysql_connect( 'host', 'username', 'password', true);

would become

$connection2 = mysqli_connect( 'host', 'username', 'password');

You may also have client flags set, MySQLi does not use these and they can be safely removed when generating the MySQLi connection.

You may have a variation to the mysql_connect to establish a persistent connection, which is mysql_pconnect. To establish the same persistent connection in MySQLi, you simply prepend the host with a p: prefix, so localhost becomes p:localhost.

In MySQL we could use mysql_error and mysql_errno to determine if there was an error connecting. Since the MySQLi replacements for these use the link to the object and even if there was a problem connecting an object is returned, we have to use mysqli_connect_error and mysql_connect_errno.

With both of these you do not provide a link, which allows them to be used to check the last connection attempt.

OK, I did promise this would be simple and now that we have gotten a proper MySQLi connection, we have the hardest part out of the way.

Migrating to MySQLi Procedural Methods

MySQLi procedural methods use a parameter that references either an object link or a result object. We have seen the reference to the object link when we dealt with mysqli_select_db. The result object is similar to a MySQL result returned from a query, for example.

Many of the methods in MySQL have very similar procedural methods in MySQLi, and are as simple to migrate as adding the i to mysql and adding or moving the link or result to the first parameter. Remember that MySQLi requires the link for those methods that reference a link. In the following list, the MySQL statement is followed by the replacement MySQLi procedural method.

mysql_affected_rows -> mysqli_affected_rows($link)
mysql_close -> mysqli_close($link)
mysql_data_seek -> mysqli_data_seek( $result, $offset)
mysql_errno -> mysqli_errno( $link)
mysql_error -> mysqli_error( $link)
mysql_fetch_array -> mysqli_fetch_array( $result, $type)
mysql_fetch_assoc -> mysqli_fetch_assoc( $result)
mysql_fetch_lengths -> mysqli_fetch_lengths( $result )
mysql_fetch_object -> mysqli_fetch_object( $result, $class, $params)
mysql_fetch_row -> mysqli_fetch_row( $result)
mysql_field_seek -> mysqli_field_seek( $result, $number)
mysql_free_result -> mysqli_free_result(result)
mysql_get_client_info -> mysqli_get_client_info( $link)
mysql_get_host_info -> mysqli_get_host_info( $link)
mysql_get_proto_info -> mysqli_get_proto_info( $link)
mysql_get_server_info -> mysqli_get_server_info( $link)
mysql_info -> mysqli_info( $link)
mysql_insert_id -> mysqli_insert_id( $link)
mysql_num_rows ->  mysqli_num_rows( $result)
mysql_ping -> mysqli_ping( $link)
mysql_query -> mysqli_query( $link, $query)
mysql_real_escape_string -> mysqli_real_escape_string( $link)
mysql_select_db - > mysqli_select_db( $link, $database)
mysql_set_charset -> mysqli_set_charset( $link, $charset)
mysql_stat -> mysqli_stat( $link)
mysql_thread_id -> mysqli_thread_id( $link)

Dealing With the More Difficult Methods

The bad news, not all methods are easy to migrate as the ones listed above. The good news, these methods are not that common so you may not even have to deal with them. These more difficult methods will require some discussion, so we will go through them one at a time.

mysql_client_encoding -> mysqli_character_set_name( $link)

This is a simple name change.

mysql_create_db

This statement is replaced with the mysqli_query method using the CREATE DATABASE sql...

$result = mysqli_query( $link, 'CREATE DATABASE database_name' );

mysql_db_name

This statement is used in conjunction with the mysql_list_dbs statement to get the requested row from a given result. To migrate it to MySQLi, we have to use the mysqli_data_seek method to locate the requested row and then mysqli_fetch_row to return requested row.

$data = mysql_db_name( $result, $row);

becomes

mysqli_data_seek( $result, $row);
$fetch = mysql_fetch_row( $result );
$data = $fetch[0];

mysql_db_query

In MySQL, this statement selects a database and runs the query. To migrate it to MySQLi, we use the mysqli_select_db method to select the database and then the mysqli_query method to run the query and return the result.

$result = mysql_db_query( 'database', 'query');

becomes

mysqli_select_db( 'database' );
$result = mysqli_query( 'query' );

mysql_drop_db

This statement is replaced with the mysqli_query method using the DROP DATABASE sql...

$result = mysqli_query( $link, 'DROP DATABASE database_name');

mysql_escape_string -> mysql_real_escape_string( $link, 'string')

This is a simple name change.

mysql_fetch_field -> mysqli_fetch_field( $result )

If this statement does not contain the optional offset parameter, then it is a simple name replacement to migrate. If the offset parameter is included, then we have to loop through the result until we find the requested offset.

$fetch = mysql_fetch_field( $result, 5);

becomes

for(x=0; x<5; x++) {
  mysqli_fetch_field( $result );
}
$fetch = mysqli_fetch_field( $result );

mysql_field_len
mysql_field_name
mysql_field_table

In MySQL, these statements return the length, name or table of the specified field. To migrate it we use the MySQLi method mysqli_fetch_field_direct to return an object containing the field data and then return the field length, name or table from that object.

$length = mysql_field_len( $result, 5);
$name = mysql_field_name( $result, 5);
$table = mysql_field_table( $result, 5)

becomes

$fieldInfo = mysqli_fetch_field_direct( $result, 5);
$length = $fieldInfo->length;
$name = $fieldInfo->name;
$table = $fieldInfo->table;

mysql_list_dbs

This statement is replaced with the mysqli_query method using the SHOW DATABASES sql...

$result = mysqli_query( $link, 'SHOW DATABASES');

mysql_list_fields

This statement is replaced with the mysqli_query method using the SHOW COLUMNS FROM sql...

$result = mysqli_query( $link, 'SHOW COLUMNS FROM table_name' );

mysql_list_processes -> mysqli_thread_id( $link )

This is a simple name change.

mysql_list_tables

This statement is replaced with the mysqli_query method using the SHOW TABLES FROM sql...

$result = mysqli_query( $link, 'SHOW TABLES FROM database_name');

mysql_num_fields -> mysqli_field_count( $link )

This statement references the result in MySQL and is replaced with the mysql_field_count method which references the link.

mysql_result

In MySQL, this statement fetches a specified row and optional field from a given result. To migrate it we use the mysqli_data_seek to locate the row and loop through the fields using mysqli_fetch_field to return the field.

$fetch = mysql_result( $result, 3, 'field');

becomes

mysql_data_seek($result, 3);
if( !empty($field) ) {
  while($finfo = mysqli_fetch_field( $result )) {
    if( $field == $finfo->name ) {
      $f = mysqli_fetch_assoc( $result );
      $fetch =  $f[ $field ];
    }
  }
} else {
  $f = mysqli_fetch_array( $result );
  $fetch = $f[0];
}

mysql_tablename

In MySQL, this statement returns the table name in the row of a specified result. To migrate it we use the mysqli_data_seek method to locate the specified row and fetch the name using the mysqli_fetch_array method.

$name = mysql_tablename( $result, 3 );

becomes

mysqli_data_seek( $result, 3 );
$f = mysql_fetch_array( $result );
$fetch = $f[0];

mysql_unbuffered_query

This statement is replaced with the mysqli_query method with the result mode set to MYSQLI_USE_RESULT...

$result = mysqli_query($link, 'query', MYSQLI_USE_RESULT);

The Red-Headed Step-Children

First off I would like to point out that I have nothing against red-headed people or step-children in general. In the United States this is a phrase which refers to a problem that nobody wants to deal with and I just do not know what a politically correct replacement would be, so we are stuck with it.

There are 2 MySQL statements that are a real pain to deal with since they use flags and types that are not supported in MySQLi the same way they where in MySQL. To get these to work, we have to create our own.

mysql_field_flags

$resultFlags = mysql_field_flags( $result, 3);

becomes

$flags = array();
$constants = get_defined_constants( true );
foreach ($constants['mysqli'] as $c => $n)
{
 if (preg_match('/MYSQLI_(.*)_FLAG$/', $c, $m))
  if (!array_key_exists($n, $flags))
   $flags[$n] = $m[1];
  $flags_num = mysqli_fetch_field_direct( $result, $field_offset )->flags;
  $result = array();
  foreach ($flags as $n => $t)
   if ($flags_num & $n)
    $result[] = $t;
  $returnFlags = implode(' ', $result);
  $returnFlags = str_replace( 'PRI_KEY', 'PRIMARY_KEY', $returnFlags);
  $returnFlags = strtolower($returnFlags);
}

mysql_field_type

$resultType = mysql_field_type( $result, 4);

becomes

$type_id = mysqli_fetch_field_direct( $result, $field_offset)->type;
$types = array();
$constants = get_defined_constants(true);
foreach ($constants['mysqli'] as $c => $n)
 if (preg_match('/^MYSQLI_TYPE_(.*)/', $c, $m))
  $types[$n] = $m[1];
$resultType = array_key_exists( $type_id, $types ) ? $types[$type_id] : NULL;

Quickly Migrate to MySQLi with the PHP MySQL to MySQLi package

Keeping your code up to date is the best way to show your loyal users that you care what happens to them. As servers start upgrading to the new PHP 7 release, a lot of open source code is going to become legacy over night and will just stop working.

My number one recommendation is to bite the bullet and get in there and fix that code. Once you get started you will discover it is not as bad as it may first seem.

If you have any questions or better solutions than the ones provided here, please comment.