Moving from mysql connection to mysqli

Question

I have a function I used to use with my scripts. openDBConn() and closeDBConn() These both called the mysql_connect() function. Used as seen below

openDBConn();
$car_model = getCarModel($car_id);
$car_color = getCarColor($car_id);
closeDBConn();

doing it this way also allows me to do the following

openDBConn();
mysqlStartTranscation();

upgradeCarColor($car_id);
addNewWing($car_id);

mysqlCommit();
closeDBConn();

Dilemma now is, if I move to mysqli, I will need to pass the connection link.

I have also read that mysql_* is being deprecated the questions I have are:

How much time do i have before most of my functions stop working?
Is there any current or future way of accessing the current mysqli connection as adding extra parameters to my functions will be a pain?
Is there any proper coding way of accessing the current mysqli connection link in a procedural manner. If not in procedural, whats the best in an OOP manner?

The `mysql_` family of functions will be deprecated in 5.5, and may be removed entirely in any subsequent *major* version. Considering 5.5 isn't even in beta yet, and the release *after* that is a while off, you don't need to panic. Take it nice and slow, and learn at your own pace. Also, check out every single last one of those questions on the **Related** sidebar. They'll be some help. — Charles, Dec 08 '12 at 11:21
Also, take a serious look at [PDO](http://php.net/book.pdo) instead of mysqli. They're both just as capable, but the prepared statement syntax of PDO is much, much friendlier. PDO is OO-*only*, and has no procedural fallback like mysqli. — Charles, Dec 08 '12 at 11:36

Sherif · Accepted Answer · 2012-12-08T11:46:05.620

You have all the time in the world since they will never stop working on their own!
Yes, there are several ways of doing this.
Yes, but there is no one-size-fits-all solution. Every situation is different and what's proper for you particular situation may not be proper for every situation.

First, the old ext/mysql is deprecated as of PHP 5.5.0, but it will never stop working entirely as the extension itself will eventually be moved into the PHP PECL repository (when it comes time to remove it). However, we're not there yet and you can only be affected when and if you chose to upgrade to that version of PHP. There is no exact time determined for the removal of the extension.

Second, you can use a variable to store the database connection just as the old ext/mysql extension was doing for you behind the scenes. The trick was you weren't aware of what it was doing (it uses the last open connection you created when you called mysql_connect and uses that everytime you call something like mysql_query to access the database).

You can do this with a static variable in your function using procedural style....

function openDBConn() {
    static $link;
    if (!isset($link)) {
      $link = mysqli_connect('localhost', 'my_user', 'my_password', 'my_db');
    }
    return $link; // returns the link
}

Or you can do this with a Class Static Variable using OOP...

Class MyDBConnect {

    public static $link;

    public function openDBConn() {
        if (!isset(static::$link)) {
            static::$link = new mysqli('localhost', 'my_user', 'my_password', 'my_db');
        }
    }

}

I want to encourage you for using the newer APIs and I commend you for it, but I also want to caution you as you move forward. When you start to port your functions over from the old ext/mysql functions to the new mysqli extension be careful not to also port over the bad practices of the old extension as well (such as using the old SQL string concatenation and escaping techniques ext/mysql offered). Instead take advantage of MySQLi prepared statements and parameterized queries.

What I do want to direct your attention to are the benefits of using the newer APIs to interface with your MySQL database (namely PDO and MySQLi).

Thanks for this, much appreciated. I always thought i could use mysqli_init() to access the last connection link but all it returns is an object with an empty thread_id — Jms Bnd, Dec 08 '12 at 12:09
You're most welcome, but that's not what [mysqli_init](http://php.net/mysqli.init) does. It's used for obtaining an object to hand to [mysqli_real_connect](http://php.net/mysqli.real-connect), which does something very different from [mysqli_connect](http://php.net/mysqli.construct). Please read the relevant manual links for details. — Sherif, Dec 08 '12 at 12:38
I tried your code but when i call it in the function which carry out the query, the link is empty. Is there anything i am missing? — Jms Bnd, Dec 08 '12 at 12:55
If i was to have 2 types of connections, such as openDBConn('read') and openDBConn('write), how would this differ? — Jms Bnd, Dec 08 '12 at 13:21
If you want more than one connection your issue becomes a lot more complex to solve using this approach and actually becomes both harder to read, harder to write, and harder to debug. You really are better off just instantiating the individual mysqli objects and carrying them around (passing them to your functions) as needed instead. — Sherif, Dec 08 '12 at 13:44

score 3 · Answer 2 · edited Jun 20 '20 at 09:12

First of all I give +1 to your question as it is something good that you start your migration.

OOP and argument

About OOP programming and arguments passing, here is A solution that is based upon Singleton Factory pattern :

class MysqliConnection{
   private static $connection = null;
   public static function getConnection(){
       
      
       if(self::$connection === null){
           //Here get some routine to make your configuration extern
           self::$connection = new Mysqli(/*args*/);
       }
       return self::$connection;
}

When you want to send a query, you can use it like that :

 function methodWhichSendsAQuery(){
      $mysqli = MysqliConnection::getConnection();
      $query = $mysqli->prepare(/*your query*/);
      /*some param bindings with $query->bindParam()*/
      $query->execute();
      return $query->fetchAll(); //an array with all results
  }

I prefere this solution over the others as it is clean, readable and does not rely on global variables. Single Responsability Principle is respected and it's OK.

Refactoring mysql_ to mysqli

The refactoring can be really difficult if you were used to handle mysql_ the old way, I mean without ressource argument, mysql_escape_stringetc and so on.

Mysql provided a script that is kind a powerful to migrate from mysql_ to mysqli here and you can get more informations here.

What I can say to you is that mysqli brings a functionality called "prepared statement" that is really powerful and strong about security. The way mysqli handles prepared statement is less user friendly than the way PDO does but the properties are the same.

Moving from mysql connection to mysqli

2 Answers2

OOP and argument

Refactoring mysql_ to mysqli