PHP Classes

How to Convert MySQL to MySQLi PHP Code to Upgrade to PHP7 Doing the PHP MySQL MySQLi Migration Using PHP with MySQLi Extension - 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 (34)   Trackbacks (0)  

Author:

Updated on: 2023-11-25

Viewers: 6,699

Last month viewers: 2,445

Package: PHP MySQL to MySQLi

Have you been putting off migrating your code from the old MySQL extension to the supported MySQLi extension? Is it one of those things you are unsure 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 migrate to use the MySQLi extension quickly. It is not going to be as difficult as you may think.




Loaded Article

Contents

How to Convert MySQL to MySQLi: MySQL vs MySQLi

How to Convert MySQL to MySQLi PHP Code to Upgrade to PHP7 Doing the PHP MySQL MySQLi Migration Tutorial Video Summary

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.

How to Convert MySQL to MySQLi PHP Code to Upgrade to PHP7 Doing the PHP MySQL MySQLi Migration Tutorial Video Summary

The tutorial video transcription follows below.

Hello this is Dave Smith author of how to convert MySQL to MySQLi and developer of the PHP MySQL to MySQLi package, both of which can be found on the PHPClasses.org Web site. The links will be available in the description below.

PHP 7 no longer supports the mysql extension, so any legacy code using the mysql extension will need to be migrated to either PDO or mysqli before it can run on a server running PHP 7 or above.

This article will give you examples of how to connect using mysqli and how to migrate the mysql methods to the mysqli procedural methods giving code examples for some of the simple changes to some of the more difficult ones too, even for the ones that do not have a replacement in mysqli we give you code that will help you to actually operate the mysql as if in a mysqli environment.

I also want to take a moment to talk about the mysql to mysqli package. This package was intended to be included in legacy code using the mysql extension that can be included in PHP versions 5.6 and below and it will just sit there and it waits for the mysql extension to go away.

So when the server updates to PHP 7 or above then the package will take over and it will keep the mysql legacy code from crashing your scripts.

It is intended as a stop gap is intended as to keep things running until you get an opportunity to actually get in and change and do the migration from mysql to mysqli once again it is recommended that you use this only as a stopgap that you do eventually perform the migration from mysql to either PDO or mysqli.

I hope that you find the article informative. Remember that you can also comment if you have any problems and I will get right back to you and help you work them out. Thank
you for your time. Bye.

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.

mysqli_connect in PHP

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

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

What Do You to Change to Use the mysqli_connect PHP function

$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.

What Can You Do If PHP mysqli_connect Not Working In Your PHP Environment

When the mysqli_connect function fails, it returns false. In that case, you can call the mysqli_connect_error function to get a string with an error message that can give more details about the cause of the mysqli_connect function failure.

$hostname = 'localhost';
$username = 'your_mysql_user_name';
$password = 'your_mysql_password';
if( mysqli_connect( $hostname, $username, $password) )
{
    die( mysqli_connect_error() );
}

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

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: PHP mysqli_query example on using php mysqli query support to execute SQL queries like with 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_escape_string -> mysql_real_escape_string( $link, 'string')

This is a simple name change.

mysql_fetch_field

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

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

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

Login Immediately with your account on:



Comments:

15. the link variable - Erik Veim (2022-10-31 02:03)
.... - 0 replies
Read the whole comment and replies

14. mysqli_fetch_object - Gary G Johnson (2019-04-18 21:14)
Severity: Error --> Class '' not found etc... - 0 replies
Read the whole comment and replies

13. Fantastic library, thanks. - Foo Bar (2019-02-24 01:53)
Nice and easy.... - 0 replies
Read the whole comment and replies

12. need help in converting deprecated php code to php 7.2 - jack walroth (2019-02-23 01:15)
replaced all "mysql" instances with "mysql1" but not working yet... - 2 replies
Read the whole comment and replies

11. Perfect, thank you! - Adilson B (2018-10-26 11:36)
Just saying thanks... - 0 replies
Read the whole comment and replies

10. MYSQLI-resulttype - Christian Wassmer (2018-10-12 12:17)
Use mysqli's $resulttype in mysqli_fetch_array... - 2 replies
Read the whole comment and replies

9. Really helpful- thanks! - Andrew Steele (2018-05-23 18:00)
Just saying thanks...... - 0 replies
Read the whole comment and replies

8. Fantastic but... - Adam Jones (2018-04-25 14:58)
Fantastic but...... - 0 replies
Read the whole comment and replies

7. PHP MySQL/MySQLi/PDO Function Maps - Ray Paseur (2017-11-20 13:49)
Here's a three-column summary... - 3 replies
Read the whole comment and replies

6. Will this block SQL Injections & Shell scripts - Udara Madushan (2017-08-28 15:11)
Will this block SQL Injections & Shell scripts... - 9 replies
Read the whole comment and replies



  Post a comment Post a comment   See comments See comments (34)   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 ...