Using a Persistent Database Object Connection from Other Objects in PHP

This is in reference to a CentOS 7 server running PHP 5.4 and MariaDB 5.5.

I am somewhat new to OOP in PHP. In converting a bunch of scripts from MySQL to MySQLi and from procedural database functions to OOP, I set up this basic Database class:

class Database
{
	private $host = "localhost";
	private $username;
	private $password;
	private $database;
	private $dbconnect;

	function __construct()
	{
		// Load config file for database connection info
		$ini = parse_ini_file("config.ini");

		$this->username = $ini['db.user'];
		$this->password = $ini['db.pword'];
		$this->database = $ini['db'];
	}

	public function connect()
	{
		// Only make a new connection if one not already established. 
		if (empty($this->dbconnect)) {

			$mysql = new mysqli($this->host, $this->username, $this->password, $this->database);

			if ($mysql->connect_errno) {
				throw new appError($mysql->connect_error);
			}

			$this->dbconnect = $mysql;
		}

		return $this->dbconnect;
	}

	public function query($query)
	{
		$db = $this->connect();
		$result = $db->query($query);

		if ($db->errno) return false;
		return $result;
	}

	public function select($query)
	{
		$rows = array();
		$result = $this->query($query);

		if ($result === false) return false;

		// Create array with results
		while ($row = $result->fetch_assoc()) {
			$rows[] = $row;
		}

		return $rows;
	}
}

With the former procedural database functions that used mysql_*, a persistent database connection was made near the start of the script and the resource ID was stored in a global variable, then all queries were run through that one database connection by accessing the global resource variable. Importantly, this worked well from within other functions and objects. Here’s how that function worked:

function db_connect() {

	if (!empty($GLOBALS['DBCONNECT']) && is_resource($GLOBALS['DBCONNECT'])) {
		return $GLOBALS['DBCONNECT'];
	} else {    
		$result = mysql_connect("localhost", DB_USER, DB_PASSWORD);
		$GLOBALS['DBCONNECT'] = $result;
		return $result;
	}
}

So at the start of each script I’d do this…

db_connect(); 

And then run my queries like this…

$result = mysql_query($query, db_connect());

This made sure one database connection was made and that all queries are run through that connection.

With the above new Database class, I instantiate it at the start of my script…

$db = new Database;
$db->connect();

But I don’t understand how to make that Database object accessible to other objects that need to perform database queries so that the same database connection is used by the entire script. What I do now is essentially this…

class MyClass
{
	public function myFunction() 
	{
		$db = new Database; 
		$data = $db->select("SELECT * FROM mydata WHERE id = 888");
        ...
	}
}

This instantiates a new Database object within the above class, which is creating a new and additional connection to the database, because it can’t access the Database $db object created in the parent calling script (that I know of).

Is there a way to use an object to open a persistent MySLQi database connection that all functions and objects loaded by that script can use? Or is this just something that is better done with a procedural function rather than a class and object? Does the solution lie in making the Database properties and its methods static? If I do that, I’m not sure how I load the database username and password from the config.ini file which the connect() function needs and is only done upon instantiation of the Database object.

I guess the basic question here is how do I access the properties or methods in an instantiated object from another object? Or maybe that isn’t the question and I’m completely missing something else. Thanks!

1 Like

The way I do it in my applications is to have a top level app object that makes an instance of the database and stores it as a private property and then that gets passed into the constructor of all objects that are going to be making use of it. It requires some thought and organisation at your top level app class but that will be the way to make it more secure as opposed to having static calls

I use the singleton approach and got the database connection script from a book by Larry Ullman. I just converted over to PDO for I believe his used mysqli.

Personally I tried other methods other than the singleton approach, but reverted back to it since the eggheads that were helping me (It wasn’t from this forum) were acting like dweebs. By that I mean they acted like they were going to give their top secret recipe away like the animated movie Kung Fu Panda. :disappointed:

This is the script →

<?php

namespace website_project\database;

# PDO database: only one connection is allowed. 

use PDO;

class Database {

    private $_connection;
    // Store the single instance.
    private static $_instance;

    // Get an instance of the Database.
    // @return Database: 
    public static function getInstance() {
        if (!self::$_instance) {
            self::$_instance = new self();
        }
        return self::$_instance;
    }

    // Constructor - Build the PDO Connection:
    public function __construct() {
        $db_options = array(
            /* important! use actual prepared statements (default: emulate prepared statements) */
            PDO::ATTR_EMULATE_PREPARES => false
            /* throw exceptions on errors (default: stay silent) */
            , PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
            /* fetch associative arrays (default: mixed arrays)    */
            , PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        );
        $this->_connection = new PDO('mysql:host=' . DATABASE_HOST . ';dbname=' . DATABASE_NAME . ';charset=utf8', DATABASE_USERNAME, DATABASE_PASSWORD, $db_options);
    }

    // Empty clone magic method to prevent duplication:
    private function __clone() {
        
    }

    // Get the PDO connection:    
    public function getConnection() {
        return $this->_connection;
    }

}

It isn’t perfect, but all I have to do know to use it anywhere (even outside classes is this):

        $db = DB::getInstance();
        $pdo = $db->getConnection();

I know the singleton pattern is frown upon, but it the only place where I use it and I didn’t want to spend weeks trying to come up with something better. Besides this is the only place I use the singleton pattern.

You have two options.

if you want to use mysqli just like old mysql, then change your db_connect() to mysqli and then use it the same way simply changing the parameters’ order

$result = mysqli_query(db_connect(), $query);

so there is no need to hassle with OOP and classes.

if you’re able to realize that mysql ext has been deprecated not by a whim, solely to make your life harder out of a blue sky, that there are reasons other than just changing the parameter order, then you should change the whole approach, get rid of your class and start using prepared statements. And in this case use PDO, not mysqli

As of the availability,

  • if your code is procedural, than use that singleton stuff.
  • if your code is OOP, then do what @Andres_Vaquero said:
class MyClass
{
    protected $db;

    public function __construct($db)
    {
        $this->db = $db;
    }
	public function myFunction() 
	{
		$data = $this->db->select("SELECT * FROM mydata WHERE id = 888");
	}
}
1 Like

Thanks, Pepster, I found a very similar solution to yours on StackOverflow which I’ve set up and it’s working perfectly.

Thanks, Colonel Shrapnel. I don’t like having to pass the database object to every class that needs it, that also makes things more complicated for those classes that need other parameters passed to it when being instantiated. I’m going with the “singleton” solution proposed by Pepster above because it does exactly what I want.

I’ll look into PDO, but if it’s vastly different and requires all queries to be re-written then I will probably stick with MySQLi for the time being.

Why did you bother to ask then, if you didn’t mean to change anyway.

If your only intent is to get rid of that warning, there are compatibility packages that will let you keep the old crap as is.

Why are you posting this rude and obnoxious reply? It doesn’t even make any sense. Goodbye.

As far as I’m aware, switching from mysqli to PDO will not require any changes to the queries that you use. The key difference is the way that prepared statements are handled, but this is only a minor aspect of the query.

2 Likes

The switching in question is not from mysqli, but from old mysql ext.
So it is not “mysqli vs.PDO” but “prepared statements vs. raw SQL” question.

Be it minor aspect or not, but prepared statements will require a total change in the approach, from insecure variables right in the query to secure separate binding (the very reason of the whole tumult). And for this goal PDO is excellent, making the task a lot easier. But if the goal is not the better code, but, so to say, to galvanize a cadaver - to make the old code stagger along with modern PHP versions - PDO is unnecessary indeed.

I see, thanks for that insight. I am using PDO now instead of MySQLi, and have it working. But the one thing I like better about MySQLi is that the object collects information about each query and connection to the database that you can easily access, whereas with PDO you have to gather it up yourself using various functions like rowCount() and lastInsertId(). The only PDO advantage I can see is that it has named variables in prepared statements, as you mentioned, which is nicer. Other than that, I actually prefer the MySQLi interface.

I think the other key thing with PDO, which might not be relevant to you (as it is not to me, at least currently), is that it can work with databases other than MySQL.

I’m not sure I see that there’s much difference between me getting the row count using the rowCount() function as opposed to accessing it via mysqli_result->num_rows. But I don’t have a strong preference for one over the other, just that I’ve picked one and I’m sticking with it.

In the last 15 years, I’ve never used any other database other than MySQL, so, it’s not really an issue for me.

Just curious, what are you using for your PHP projects, MySQLi or PDO, and why?

I do like the named variables for PDO prepared statements. That alone is making me lean towards PDO.

mysql/mysqli are bare low-level APIs that require a wrapper class written around their methods. Whereas PDO is already a wrapper, making your own class unnecessary and obsoleted.

Prepared statement is a pain in mysqli and piece of cake in PDO.
Getting different kinds of result from mysqli require you additional code while PDO has built-in methods.
PDO works everywhere but mysqli is usable only with mysqlnd driver.

A detailed comparison:

Of course all the inconveniences of mysqli could be overcome, but with much labor. That’s why PDO is preferred if one just needs to get the job done.

1 Like

I started learning PHP using an old book, so started with the old library calls. Then I read various places about the new versions, and picked PDO as it seemed more flexible - there might have been the odd chance that I’d need to connect to MS SQL Server, or Access or something. I can’t claim any detailed knowledge led me to that selection, it just seemed like a good thing.

1 Like

PDO would seem the natural choice if you are doing OOP. To be honest I’ve only just starter learning OOP, but I’ve been using PDO for a while and really like it.
I also initially learnt from the wrong places, using MySql. When I realised my error I switched to MySqli, thinking it would be an easier transition to make. In a later misinformed decision I did then make the move to PDO to take advantage of prepared statements (I did not now at the time it could be done in mysqli), but I certainly don’t regret that move. PDO is a pleasure to work with compared to mysql(i) and I wouldn’t go back to mysqli.

If possible, you should be well equipped with both libraries. No one has told you, but PDO has a dark side to it. It isn’t just all pretty flowers and rainbows. With PDO, you have to be cautious of how you call your database. I forgot who said this, but you should turn EMULATE_PREPARES off. While you do this, some of your queries will break.

With mysqli_*, you don’t have that problem. The only thing I find that gives mysqli_* the bad reputation is because a lot of people use it as if they were still using mysql_*. And none of them use prepared statements or even care to use prepared statements. Otherwise, I’d suggest equipping yourself with both libraries so you have knowledge in both libraries. This will help you not only in the future, but in your career as well. Such as, what happens when a client requires you to only use mysqli_*? You can’t just say, “I am going to switch you to PDO.” They most likely fire you.

1 Like

Any chance you could post a simple example illustrating the problem? Does not have to be complete. Just a few lines. I never noticed anything that mysqli can do that is more convenient than PDO.

Sadly, but some people base their opinion not on the knowledge and experience but on rumors and superstitions.
Or simply trying to talk about things they don’t really understand.
So did the author of the above post.

Is a blatant disinformation to say the least.

Remarkably, the above post contains no example to prove such a bold statement. And for a reason, as of course not a single query that you can run with mysqli would break with PDO.

He means that with PDO you have to add braces to get the desired value, e.g.

$id = $mysqli->insert_id;
// whereas 
$id = $pdo->lastInsertId(); // note the braces

Or may be he refers to the fact that mysqli object contains a lot of properties that could be dumped out, sort of lazy debugging. But to me it’s rather a disservice as most of these properties are rather useless and would just spam the output.

So for the real life usage there is no difference of course.