DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
  1. DZone
  2. Data Engineering
  3. Databases
  4. How to Convert MySQL to MySQLi

How to Convert MySQL to MySQLi

In PHP 7, the MySQL extension is completely removed. Learn how to easily convert a MySQL extension into MySQLi without it.

Saquib Rizwan user avatar by
Saquib Rizwan
·
Apr. 18, 17 · Tutorial
Like (7)
Save
Tweet
Share
122.58K Views

Join the DZone community and get the full member experience.

Join For Free

One of the most important developments in the PHP world was the backward compatibility break for the PHP MySQL extension, which leaves us with two methods to connect to the database: MySQLi and PDO.

Image title

In PHP 7, the MySQL extension is completely removed. Thus, in this article, I will discuss how to convert a MySQL extension into MySQLi. The first thing you should understand is that MySQL works as a resource whereas MySQLi works as a resource and an object. While you really do not need to know the technical differences, you must understand that these two are a lot different from each other.

MySQL to MySQLi Procedural Methods

I will first start with the most commonly used MySQL methods. The only thing that changed in the following method is the addition of "i" in MySQL and some changes of the positions of link and result in the parameter of the method.

Following are the popularly used methods of MySQL, followed by the equivalent methods in MySQLi.

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)

If you want to convert your script from a MySQL extension to a MySQLi extension manually, you must start with the top of the script and start converting all the methods one by one.

For this, open the script in any text editor and use the find-and-replace tool to replace mysql_ with mysqli. But that's not all. You must manually check and verify the parameters of the method, as well.

To automate the process of converting the script from MySQL to MySQLi, I can use the following online tools. I will create a sample insertion and retrieval code and convert it using one of these tools.

Sample Code for Conversion

I am going to use the following database and code for testing the online conversion tools.

Database SQL

CREATE TABLE `info`.`student` 
  ( 
     `id`        INT NOT NULL auto_increment, 
     `name`      VARCHAR(40) NOT NULL, 
     `last name` VARCHAR(40) NOT NULL, 
     `email`     VARCHAR(40) NOT NULL, 
     PRIMARY KEY (`id`) 
  ) 
engine = innodb; 

Connection.php

<?php
$servername = "localhost" ;
$username = "root";
$pass = "";
$con = mysql_connect($servername,$username,$pass) or die("Problem occur in connection");
$db = mysql_select_db("info",$con);
?>

Insert.php

<?php
require_once("connection.php");
$query = "Insert into student values('','Saquib','Rizwan','saquib.rizwan@cloudways.com')";
$result = mysql_query($query);
if(isset($result))
{
 echo "Data has been inserted";
}
?>

Retrieve.php

<?php
require_once("connection.php");
$query = "select * from Student ";
$result = mysql_query($query);
while($data = mysql_fetch_array($result))
{
 echo "<br/><br/>";
 echo "ID : $data[0]";
 echo "Name : $data[1]  ";
 echo "Last Name : $data[2]  ";
 echo "Email : $data[3]  ";
}
?>

Here is the basic PHP code which uses MySQL extension to create the database connection, select database, create and execute a query and fetch data from the database.

Now I will convert the script using an online tool.

ICT Academie

In order to convert the code using ICT Academie, all you need to do is paste your code snippet enclosed in PHP opening and closing tags.

For example, if you want to see how mysql_connect() gets converted, paste <?php mysql_connect() ?>>".

Code conversion result from the ICT Academie for the above code is as follows.

Connection.php

<?php  
$servername = "localhost" ;  
$username = "root"; 
$pass = "";  
$con = ($GLOBALS["___mysqli_ston"] = mysqli_connect($servername, $username, $pass)) or die("Problem occur in connection");  
$db = ((bool)mysqli_query($con, "USE " . info));  
?> 

Insert.php

<?php  
require_once("connection.php"); 
$query = "Insert into student values('','Saquib','Rizwan','saquib.rizwan@cloudways.com')";  
$result = mysqli_query($GLOBALS["___mysqli_ston"], $query);  
if(isset($result)) 
{ 
    echo "Data has been inserted";  
} 
?> 

Retrieve.php

<?php  
require_once("connection.php"); 
$query = "select * from Student ";  
$result = mysqli_query($GLOBALS["___mysqli_ston"], $query);  
while($data = mysqli_fetch_array($result)) 
{ 
    echo "<br/><br/>";  
    echo "ID : $data[0]    "; 
    echo "Name : $data[1]  "; 
    echo "Last Name : $data[2]  "; 
    echo "Email : $data[3]  ";  
} 
?> 

To test the converted code, it is clear that all the MySQL methods have been successfully converted into MySQLi. It only changes the PHP code, but the result remains the same.

Note: Before starting the conversion process, create backups of all the files and test run all the code before live deployment. The converter only converts the code. It does not add any security measures to prevent SQL injections.

MySQL Convert (command)

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • How To Set Up and Run Cypress Test Cases in CI/CD TeamCity
  • AWS CodeCommit and GitKraken Basics: Essential Skills for Every Developer
  • Microservices 101: Transactional Outbox and Inbox
  • mTLS Everywere

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: