Cara menggunakan convert mysql to mysqli

In PHP 7 or greater, the MySQL extension has been removed completely and so, it is important to convert or upgrade from MySQL to MySQLi to keep your website updated
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

Table of Contents

  • mysql_connect and mysql_select_db
  • mysql_real_escape_string
  • mysql_query
  • mysql_num_rows
  • mysql_fetch_assoc
  • mysql_error
  • mysql_close
  • How can I convert MySQL database to MySQLi in PHP?
  • Can I use both MySQL and MySQLi?
  • How do I enable MySQLi extension?
  • How do I know if MySQLi is installed?

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.

The functions defined below uses the MySQLi Procedural and Object Oriented methods to convert all deprecated MySQL functions to MySQLi

To use these functions, simply copy all the codes below to your database connection file, update your database connection details and you are good to start using them

Code:

<?php
define ('hostnameorservername','localhost'); //Your server name or hostname goes in here
define ('serverusername','root'); //Your database username goes in here
define ('serverpassword',''/); //Your database password goes in here
define ('databasenamed','scmplus'); //Your database name goes in here

$mysqli = new mysqli( hostnameorservername, serverusername, serverpassword, databasenamed );

if ($mysqli->connect_errno)
{
printf("Connection failed: %s\n", $mysqli->connect_error);
exit();
}

if (!$mysqli->set_charset("utf8"))
{
printf("Error loading character set utf8: %s\n", $mysqli->error);
exit();
}

if ( !function_exists('mysql_connect') )
{
function mysql_connect( $sql_host, $sql_username, $sql_password )
{
$mysqli_connect = mysqli_connect( $sql_host, $sql_username, $sql_password );
return $mysqli_connect;
}
}

if ( !function_exists('mysql_select_db') )
{
function mysql_select_db( $database, $connection )
{
$mysqli_select_db = mysqli_select_db( $connection, $database );
return $mysqli_select_db;
}
}

if (!function_exists('mysql_real_escape_string'))
{
function mysql_real_escape_string($string)
{
global $mysqli;
if($string){
$real_escape_string = $mysqli->real_escape_string($string);
return $real_escape_string;
}
}
}

if (!function_exists('mysql_query'))
{
function mysql_query($query)
{
global $mysqli;
if($query) {
$result = $mysqli->query($query);
return $result;
}
}
}

if (!function_exists('mysql_fetch_array'))
{
function mysql_fetch_array($result){
if($result){
$row = $result->fetch_assoc();
return $row;
}
}
}

if (!function_exists('mysql_num_rows'))
{
function mysql_num_rows($result){
if($result){
$row_cnt = $result->num_rows;;
return $row_cnt;
}
}
}

if (!function_exists('mysql_free_result'))
{
function mysql_free_result($result)
{
if($result){
global $mysqli;
$result->free();
}
}
}

if (!function_exists('mysql_data_seek'))
{
function mysql_data_seek($result, $offset){
if($result){
global $mysqli;
return $result->data_seek($offset);
}
}
}

if (!function_exists('mysql_close'))
{
function mysql_close(){
global $mysqli;
return $mysqli->close();
}
}
if (!function_exists('mysql_insert_id'))
{
function mysql_insert_id(){
global $mysqli;
$lastInsertId = $mysqli->insert_id;
return $lastInsertId;
}
}

if (!function_exists('mysql_error'))
{
function mysql_error(){
global $mysqli;
$error = $mysqli->error;
return $error;
}
}
?>

That's it guys...

Recently, I had a to migrate a website that was running PHP 5.6 over to PHP 7. As usual, the first thing I did was to just copy the site over to the new host, and check the logs to see what the errors were. I saw a ton of messages like this:

PHP Fatal error:  Uncaught Error: Call to undefined function mysql_connect()
...
PHP Fatal error:  Uncaught Error: Call to undefined function mysql_query()
...
PHP Fatal error:  Uncaught Error: Call to undefined function mysql_num_rows()
...

I was really lucky! The main problem was that the site relied on database extensions for MySQL that have been deprecated since PHP 5.5, and were removed in PHP 7.

This turned out to be a pretty easy fix. You can check out the documentation for the new MySQLi improved extension over at PHP.net. Here are the steps that I took to rework the code so that the site functioned normally, again.

mysql_connect and mysql_select_db

The previous connection method was mysql_connect, called like this:

$conn = mysql_connect(mysql_server_name, username, password);

I replaced that with this:

$conn = mysqli_connect(mysql_server_name, username, password, database_name);

Since I could pass in the database name, I could then throw out an old call to <a href="https://www.php.net/manual/en/function.mysql-select-db.php">mysql_select_db</a> as well.

mysql_real_escape_string

<a href="https://www.php.net/manual/en/function.mysql-real-escape-string.php">mysql_real_escape_string</a>($str) was replaced with $conn-><a href="https://www.php.net/manual/en/mysqli.real-escape-string.php">real_escape_string</a>($str). I could have also used mysqli_real_escape_string($str).

mysql_query

$results = <a href="https://www.php.net/manual/en/function.mysql-query.php">mysql_query</a>($sql_query) was replaced with $results = $conn-><a href="https://www.php.net/manual/en/mysqli.query.php">query</a>($sql_query).

mysql_num_rows

<a href="https://www.php.net/manual/en/function.mysql-num-rows.php">mysql_num_rows</a>($results) was replaced with $results-><a href="https://www.php.net/manual/en/mysqli-result.num-rows.php">num_rows</a>.

mysql_fetch_assoc

$row = <a href="https://www.php.net/manual/en/function.mysql-fetch-assoc.php">mysql_fetch_assoc</a>($results) was replaced with $results-><a href="https://www.php.net/manual/en/mysqli-result.fetch-assoc.php">fetch_assoc</a>().

mysql_error

$err = <a href="https://www.php.net/manual/en/function.mysql-error.php">mysql_error</a>($conn) was replaced with $err = $conn-><a href="https://www.php.net/manual/en/mysqli.error.php">error</a>.

mysql_close

<a href="https://www.php.net/manual/en/function.mysql-close.php">mysql_close</a>($conn) was replaced with $conn-><a href="https://www.php.net/manual/en/mysqli.close.php">close</a>().

Aside from these problems with using the old MySQL extension, I noticed one other problem. There were a few messages in the PHP logs, like this:

PHP Notice: Undefined variable: blah

This happened when the variable had never been defined before it was used. For example, I saw code like this:

$myArr = getSomething($blah);
...

The variable $blah was only found once in that source code – it had never been initialized. So I just initialized it to NULL, and the notice went away. Probably it was a typo or copy/paste error. I also double-checked to make sure that NULL was an acceptable input, and not a lurking bug.

The PHP notice message didn’t do much harm, but it was cluttering up the logs, making it hard to see the real problems, so getting rid of it was a good thing. As a general rule, I like to get rid of messages like this. That way, if something goes wrong, it’s easier to find new problems by checking the logs.

How can I convert MySQL database to MySQLi in PHP?

To open new connection to the database with MySQLi you need to create new instance of MySQLi class. $mysqli = new \mysqli($host, $user, $password, $dbName); $mysqli->set_charset('utf8mb4');

Can I use both MySQL and MySQLi?

It is possible to include both MySQL and MySQLi when connecting to a single database, but it is incredibly delicate and with large amounts of data being passed through it can get very messy and hard to control. it is best to use MySQLi in general in my opinion because it is much more secure and up to date.

How do I enable MySQLi extension?

how to enable MySQLi extension on web-server with cPanel?.

Step 1 : Log into WHM panel..

Step 2 : Go to Home » Software » EasyApache 4..

Step 3 : Click on “Customize”.

Step 4 : Go to PHP Extensions..

How do I know if MySQLi is installed?

Check if MySQLi is Installed You can do that by visiting a phpinfo() page that you made, or by running this command: php -m | grep mysqli.