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).