PHP Classes
PHP Classes
elePHPant
Icontem

How to Convert MySQL to MySQLi PHP Code to Upgrade to PHP7 Doing the PHP MySQL MySQLi Migration - PHP MySQL to MySQLi package blog

Recommend this page to a friend!
  All package blogs All package blogs   PHP MySQL to MySQLi PHP MySQL to MySQLi   Blog PHP MySQL to MySQLi package blog   RSS 1.0 feed RSS 2.0 feed   Blog How to Convert MySQL ...  
  Post a comment Post a comment   See comments See comments (7)   Trackbacks (0)  

Author:

Updated on: 2017-03-24

Posted on:

Package: PHP MySQL to MySQLi

Have you been putting off migrating you code from the old MySQL extension to the supported MySQLi extension? Is it one of those things that you just are not sure where to start or are you having trouble finding the time?

Read this article to avoid having your code instantly labeled legacy code and learn how to quickly migrate to use the MySQLi extension. It is not going to be as difficult as you may think.




Contents

How to Convert MySQL to MySQLi: MySQL vs MySQLi

Using the PHP MySQL to MySQLi Migration Package

If you are not aware by now that the MySQL extension is being removed in the PHP 7 release, then let me be the first to welcome you to the current century.

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.

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.

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)

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);

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;

Using the PHP MySQL to MySQLi Migration Package

PHP MySQL to MySQLi is package that emulates the mysql extension functions using the mysqli extension. 

It uses these replacement code solutions and can act as a stop-gap while you work on migrating your code. It provides a quick alternative solution for projects that need to migrate to mysqli immediately, but you should consider it a temporary solution while your real mysqli migration is not completed.

Download and Install the PHP MySQL to MySQL 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.

You can download the package going to the PHP MySQL to MySQL page Download tab and download the ZIP archive. You can also install it using the PHP composer tool using the instructions mentioned in that page.

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




You need to be a registered user or login to post a comment

1,449,582 PHP developers registered to the PHP Classes site.
Be One of Us!

Login Immediately with your account on:

FacebookGmail
HotmailStackOverflow
GitHubYahoo


Comments:

4. Another Format Required - Lee Davis (2016-12-20 22:32)
Can You PLEASE Do This With PDO?... - 1 reply
Read the whole comment and replies

3. Or a Quick and Dirty solution - Colin McKinnon (2015-11-23 22:33)
Recreate the mysql_ functions with an include file... - 1 reply
Read the whole comment and replies

2. I've got an error ... - brbaso (2015-11-23 09:25)
Fatal error: Can't use function return value in write context ..... - 1 reply
Read the whole comment and replies

1. Thank you - Fauzi Talha (2015-11-23 06:52)
wonderful article... - 0 replies
Read the whole comment and replies



  Post a comment Post a comment   See comments See comments (7)   Trackbacks (0)  
  All package blogs All package blogs   PHP MySQL to MySQLi PHP MySQL to MySQLi   Blog PHP MySQL to MySQLi package blog   RSS 1.0 feed RSS 2.0 feed   Blog How to Convert MySQL ...