Convert PHP ext/mysql to MySQLi

Migrating away from ext/mysql to MySQLi (or PHP Data Object ([PDO](http://php.net/pdo "PHP Data Objects"))) is important, because the ext/mysql functions are deprecated as of PHP 5.5.0. If you do not update your PHP code, your website will fail soon!
Published on Wednesday, 4 February 2015
mysql mysqli php

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 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 function 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
/*
 * Follow me on Twitter: @Jan_Reilink
 */
}
?>

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 ('', '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 ('', '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
  ('', '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
  ('', '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
  ('', '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
  ('', '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
  ('', '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 there is no mysql_select_db() equivalent. This is because the database name has to 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?

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() );
    }
    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;
      }
    }
  }
// ...
?>

What immediately stands out is mysqli_query() requires the link ($this->rs) before the query, as the first parameter. Fortunately, the rest will be easier.

function recordCount($rs)

This function uses mysql_num_rows(), which as 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. 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, MYSQL_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), as where 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

  • mysql_connect: mysqli_connect
  • mysql_select_db: -
  • 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, that claims to be a pure PHP implementation of mysql_ functions based on mysqli_: https://sourceforge.net/projects/mysqlwithmysqli/.

While I haven't tried this approach that seems like a MySQLi wrapper (not sure if it fully works), 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 protection 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 to the database.

Increase in SQL injection attacks MySQL sleep() attacks

So while your 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 & sanitize user input as well.

Validate MIME types with PHP Fileinfo

Conclusion

This post was all about how to quickly move from ext/mysql to MySQLi (improved MySQL extension). This is important because the ext/mysql functions will be are removed in a future release of PHP 7.

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