Convert PHP ext/mysql to MySQLi

Migrating away from ext/mysql to MySQLi (or PHP Data Objects (PDO)) is important, because the ext/mysql functions were deprecated as of PHP 5.5.0 and are gone in PHP 7. If you do not update your PHP code, your website will fail. In this post I convert an old PHP MySQL class to MySQLi, step by step.

Learn to convert a PHP MySQL class to MySQLi and migrate away from ext/mysql

Years ago, I wrote a simple PHP MySQL class for my employer at the time, Vevida. The class was basically meant to show customers how easy it is to create such a class, to ease MySQL database operations in PHP. Don't shoot me for my code... I'll use that PHP MySQL class to convert the ext/mysql functions to MySQLi. To make the migration easier, I'll use the procedural style.

By example.

PHP ext/mysql functions summary

The old MySQL extension (also called ext/mysql) API has a lot of functions. Many website builders who created their website years ago don't know they're using ext/mysql as their database interface, until they see the functions used.

The ext/mysql functions include:

• mysql_connect()
• mysql_select_db()
• mysql_query()
• mysql_fetch_array()
• mysql_error()

Got it? We need to get rid of those.

PHP ext/mysql class and MySQL test database

The old PHP MySQL class I'll be using in this example is as follows (code comments are in Dutch...):

<?php
$dbhost = "mysql hostname";
$dbuser = "mysql username";
$dbpassword = "mysql password";
$dbname = "mysql database name";
 
class mydb {
  public $rs, $result, $sql, $table_prefix, $tstart,
      $executedQueries, $queryTime, $dumpSQL, $queryCode;
 
  public function mydb() {
    global $dbhost, $dbuser, $dbpassword, $dbname;
    $this->dbconfig['dbhost'] = $dbhost;
    $this->dbconfig['dbname'] = $dbname;
    $this->dbconfig['dbuser'] = $dbuser;
    $this->dbconfig['dbpass'] = $dbpassword;
  }
 
  private function destruct__ () {
    //unset
    unset ($this);
  }
 
  public function getMicroTime() {
     list($usec, $sec) = explode(" ", microtime());
     return ((float)$usec + (float)$sec);
  }
 
  private function dbConnect() {
    /**
     * functie om verbinding te maken met de database.
     */
    $tstart = $this->getMicroTime();
    if( @!$this->rs = mysql_connect( $this->dbconfig['dbhost'], $this->dbconfig['dbuser'], $this->dbconfig['dbpass'] ) ) {
      /* connectie mislukt. */
      die( "Not connected : " . mysql_error() );
    }
    else {
      mysql_select_db($this->dbconfig['dbname'], $this->rs);
      $tend = $this->getMicroTime();
      $totaltime = $tend-$tstart;
      if( $this->dumpSQL ) {
        $this->queryCode .= sprintf( "Database connection was created in %2.4f s", $totaltime )."";
      }
      $this->queryTime = $this->queryTime+$totaltime;
    }
  }
 
  public function dbQuery( $query ) {
    /*
     * functie om de database te raadplegen. Controleert de verbinding
     * en maakt deze als dat nodig is.
     */
    if( empty( $this->rs ) ) {
      $this->dbConnect();
    }
    $tstart = $this->getMicroTime();
    if( @!$result = mysql_query( $query, $this->rs ) ) {
 
      /* query failed */
      die( "Execution of a query to the database failed " .$query ." " .mysql_error() );
    }
    else {
      $tend = $this->getMicroTime();
      $totaltime = $tend-$tstart;
      $this->queryTime = $this->queryTime+$totaltime;
      $this->executedQueries = $this->executedQueries+1;
      if( count( $result ) > 0 ) {
        return $result;
      } else {
        return false;
      }
    }
  }
 
  public function recordCount( $rs ) {
  /* functie om het aanral rows in een recordset te tellen. */
    return mysql_num_rows($rs);
  }
 
  public function fetchRow( $rs, $mode='assoc' ) {
    if( ( $mode=='assoc' ) || ( $mode == '' ) ) {
      return mysql_fetch_assoc( $rs );
    } elseif( $mode=='num' ) {
      return mysql_fetch_row( $rs );
    } elseif( $mode=='both' ) {
      return mysql_fetch_array( $rs, MYSQL_BOTH );
    }
    else {
      /* whoops, wrong mode */
      die( "Unknown get type ( $mode ) specified for fetchRow - must be empty, 'assoc', 'num' or 'both'." );
    }
  }
 
  public function affectedRows( $rs ) {
    return mysql_affected_rows( $this->rs );
  }
 
  public function insertId( $rs ) {
    return mysql_insert_id( $this->rs );
  }
 
  public function freeResult( $resultset ) {
    return mysql_free_result( $resultset );
  }
 
  public function serverVersion() {
    return mysql_get_server_info();
  }
 
  public function dbClose() {
    /* functie om de database-verbinding te sluiten */
    if( $this->rs ) {
      mysql_close( $this->rs );
    }
  }
 
// end class
}
?>

For this post, I also created an example database to test the PHP ext/mysql to MySQLi migration. The database is based on an old PHP guestbook I once created, a loooong time ago...

CREATE TABLE example_guestbook (
    id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user VARCHAR(255) NOT NULL,
    mail VARCHAR(255) NOT NULL,
    post TEXT NOT NULL,
    url VARCHAR(255) NOT NULL,
    date VARCHAR(32) NOT NULL
);

INSERT INTO `example_guestbook`
  (id, user, mail, post, url, date) VALUES (NULL, 'Foo Bar', 'foobar@example.com', 'test message #1', 'http://www.example.com', '2015-02-04');
INSERT INTO `example_guestbook`
  (id, user, mail, post, url, date) VALUES (NULL, 'Hannibal Smith', 'h.smith@example.net', 'I love it when a plan comes together', 'http://en.wikipedia.org/wiki/John_%22Hannibal%22_Smith', '2015-02-04');
INSERT INTO `example_guestbook`
  (id, user, mail, post, url, date)
VALUES
  (NULL, 'B.A', 'b.a.baracus@example.com', 'You fool!', 'http://en.wikipedia.org/wiki/B._A._Baracus', '2015-02-04');
INSERT INTO `example_guestbook`
  (id, user, mail, post, url, date)
VALUES
  (NULL, 'Foo Bar', 'foobar@example.com', 'test message #1', 'http://www.example.com', '2015-02-04');
INSERT INTO `example_guestbook`
  (id, user, mail, post, url, date)
VALUES
  (NULL, 'Sam', 'sam.ronin@example.net', 'They gave me a grasshopper', 'http://www.imdb.com/character/ch0008040/', '2015-02-04');
INSERT INTO `example_guestbook`
  (id, user, mail, post, url, date)
VALUES
  (NULL, 'Vincent', 'vincent.ronin@example.net', 'Everyone\'s your brother until the rent comes due.', 'http://www.imdb.com/character/ch0008043/', '2015-02-04');
INSERT INTO `example_guestbook`
  (id, user, mail, post, url, date)
VALUES
  (NULL, 'Neil McCauley', 'n.mccauley@example.org', 'That\'s the discipline.', 'http://www.imdb.com/character/ch0003876/quotes', '2015-02-04');

Yes, I love the movies Ronin and Heat 🙂 Do you too?

Migrate ext/mysql to MySQLi, step by step, one function at a time

How to migrate the PHP MySQL class to MySQLi (procedural style). Now everything is in place, you can start to convert your PHP class functions.

function dbConnect()

To convert your dbConnect function, you need to get rid of the functions mysql_connect() and mysql_select_db(). Have a look at the original function:

<?php
// ...
private function dbConnect() {
  $tstart = $this->getMicroTime();
  if(@!$this->rs = mysql_connect($this->dbconfig['dbhost'], $this->dbconfig['dbuser'], $this->dbconfig['dbpass'])) {
    die("Not connected : " . mysql_error());
  }
  else {
    mysql_select_db($this->dbconfig['dbname'], $this->rs);
    $tend = $this->getMicroTime();
    $totaltime = $tend-$tstart;
    if($this->dumpSQL) {
      $this->queryCode .= sprintf("Database connection was created in %2.4f s", $totaltime)."";
    }
    $this->queryTime = $this->queryTime+$totaltime;
  }
}
// ...
?>

In the MySQLi documentation, you'll find that you don't need a mysql_select_db() equivalent here (although mysqli_select_db() exists). This is because the database name can be provided in the mysqli_connect() function as a parameter. Let's rewrite your dbConnect() function:

<?php
// ...
private function dbConnect() {
  $tstart = $this->getMicroTime();
  if(@!$this->rs = mysqli_connect($this->dbconfig['dbhost'], $this->dbconfig['dbuser'], $this->dbconfig['dbpass'], $this->dbconfig['dbname'])) {
    die('Connect Error (' . mysqli_connect_errno() . ') '
            . mysqli_connect_error());
  }
  $tend = $this->getMicroTime();
  $totaltime = $tend-$tstart;
  if($this->dumpSQL) {
    $this->queryCode .= sprintf("Database connection was created in %2.4f s", $totaltime)."";
  }
  $this->queryTime = $this->queryTime+$totaltime;
}
// ...
?>

That was easy, now wasn't it?

function dbQuery($query)

The dbQuery($query) function is called from within your PHP scripts. The function first checks if a database connection exists, and calls dbConnect() if that's not the case.

The rewritten dbQuery($query) looks like:

<?php
// ...
  public function dbQuery( $query ) {
    if( empty( $this->rs ) ) {
      $this->dbConnect();
    }
    $tstart = $this->getMicroTime();
    if(@!$result = mysqli_query( $this->rs, $query ) ) {
      die( "Execution of a query to the database failed " .$query ." " .mysqli_error( $this->rs ) );
    }
    else {
      $tend = $this->getMicroTime();
      $totaltime = $tend-$tstart;
      $this->queryTime = $this->queryTime+$totaltime;
      $this->executedQueries = $this->executedQueries+1;
      return $result;
    }
  }
// ...
?>

What immediately stands out is that mysqli_query() requires the link ($this->rs) before the query, as the first parameter. The same goes for mysqli_error(). And because mysqli_query() returns either a result object or true on success, the old count($result) check is gone: on PHP 8, count() on a result object throws a TypeError. Fortunately, the rest will be easier.

function recordCount($rs)

This function uses mysql_num_rows(), which has a MySQLi equivalent: mysqli_num_rows(). The rewritten function then becomes:

<?php
// ...
  public function recordCount( $rs ) {
    return mysqli_num_rows( $rs );
  }
// ...
?>

function fetchRow($rs, $mode='assoc')

The old ext/mysql functions mysql_fetch_assoc(), mysql_fetch_row() and mysql_fetch_array() all have their own MySQLi equivalents. Note that the constant MYSQL_BOTH becomes MYSQLI_BOTH. This makes rewriting the function fetchRow($rs, $mode='assoc') easy:

<?php
// ...
  public function fetchRow( $rs, $mode='assoc' ) {
    if( ( $mode=='assoc' ) || ( $mode == '') ) {
      return mysqli_fetch_assoc( $rs );
    } elseif( $mode=='num' ) {
      return mysqli_fetch_row( $rs );
    } elseif( $mode=='both' ) {
      return mysqli_fetch_array( $rs, MYSQLI_BOTH );
    }
    else {
      die( "Unknown get type ($mode) specified for fetchRow - must be empty, 'assoc', 'num' or 'both'." );
    }
  }
// ...
?>

Convert other ext/mysql functions to MySQLi?

Following the examples above, you can easily migrate the other functions too, as they all have procedural style equivalents:

<?php
// ...
  public function affectedRows( $rs ) {
    return mysqli_affected_rows ( $this->rs );
  }
 
  public function insertId( $rs ) {
    return mysqli_insert_id( $this->rs );
  }
 
  public function freeResult( $resultset ) {
    return mysqli_free_result( $resultset );
  }
 
  public function serverVersion() {
    return mysqli_get_server_info( $this->rs );
  }
 
  public function dbClose() {
    if( $this->rs ) {
      mysqli_close( $this->rs );
    }
  }
 
// end class
}
?>

Class usage example

The PHP/MySQLi class is saved as, for example, mysqli.class.php. You can use this in your scripts by simply including this file and instantiating the class:

<?php
  require_once("mysqli.class.php");
  $mydb = new mydb();
 
  /* boolean */
  $mydb->dumpSQL = true;
 
  $sql = "SELECT * FROM `example_guestbook`";
  $result = $mydb->dbQuery($sql);
 
  while ($row = $mydb->fetchRow($result)) {
    // $row = $mydb->fetchRow($result, $mode='num')
    // $row = $mydb->fetchRow($result, $mode='both')

    printf ("%s (%s)\n", $row["user"], $row["post"]);
    // printf ("%s (%s %s)\n", $row[0], $row[1], $row[3]);
  }
  
  // debug info:
  if ($mydb->dumpSQL === true) {
    echo $mydb->executedQueries ." queries executed, took ".sprintf("%2.4f", $mydb->queryTime)." s.\r\n";
    echo $mydb->serverVersion();
  }
 
  $mydb->dbClose();
?>

Our output will be:

Foo Bar (test message #1)
Hannibal Smith (I love it when a plan comes together)
B.A (You fool!)
Foo Bar (test message #1)
Sam (They gave me a grasshopper)
Vincent (Everyone's your brother until the rent comes due.)
Neil McCauley (That's the discipline.)
1 queries executed, took 0.0057 s.

MySQLi equivalents for ext/mysql

Moving from ext/mysql to MySQLi is pretty straightforward. You have to keep in mind that MySQLi requires the connection object as first parameter (where needed), whereas ext/mysql uses the second parameter for the connection information.

Here you'll find a summary of the most used mysql_ functions and their mysqli_ equivalents, or counterparts.

MySQLi equivalents for ext/mysql functions

ext/mysql	MySQLi
mysql_connect	mysqli_connect
mysql_select_db	mysqli_select_db (or pass the database name to mysqli_connect)
mysql_query	mysqli_query
mysql_num_rows	mysqli_num_rows
mysql_fetch_assoc	mysqli_fetch_assoc
mysql_fetch_row	mysqli_fetch_row
mysql_fetch_array	mysqli_fetch_array
mysql_affected_rows	mysqli_affected_rows
mysql_insert_id	mysqli_insert_id
mysql_free_result	mysqli_free_result
mysql_get_server_info	mysqli_get_server_info
mysql_close	mysqli_close
mysql_real_escape_string	mysqli_real_escape_string

Pure PHP implementation of mysql_* functions based on mysqli_*

On SourceForge you can find the Mysql using Mysqli project (Wayback Machine), which claims to be a pure PHP implementation of mysql_ functions based on mysqli_.

I haven't tried this approach, which seems like a MySQLi wrapper (not sure if it fully works), but you might want to if you need to support MySQLi fast.

Secure your MySQL database from SQL injection attacks too!

I can't stress it enough: securing and protecting against SQL injection attacks is important! Really important! An attacker can bring down a website or MySQL database server without trouble by injecting SleeP(3) commands into the database. Read more about MySQL sleep() attacks.

So while you're making your code adjustments, why don't you add prepared statements too? For prepared statements, you'll use mysqli_prepare(). And don't forget to properly validate and sanitize user input as well.

Accepting file uploads? Learn how to validate MIME types with PHP Fileinfo.

Conclusion

This post was all about how to quickly move from ext/mysql to MySQLi (the MySQL Improved extension). This is important because the ext/mysql functions were removed in PHP 7.0.

On PHP's MySQL Drivers and Plugins page, you'll find all necessary information and examples about ext/mysql, MySQLi and PDO.

Using PDO instead? Here is how to use SSL in PHP Data Objects (PDO) mysql. And with MySQLi in place, you can optimize all tables with PHP MySQLi multi_query.