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:

1. How much time do i have before most of my functions stop working?
2. Is there any current or future way of accessing the current mysqli connection as adding extra parameters to my functions will be a pain?
3. 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?

Answer 1

1. You have all the time in the world since they will never stop working on their own!
2. Yes, there are several ways of doing this.
3. 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).

Answer 2

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.