Efficiently use mysqli in object oriented setting

Question

I have come to the conclusion that using mysqli in an OO approach is better than a procedural approach. (Source: Why is object oriented PHP with mysqli better than the procedural approach?). But I'm not quite sure if what I am doing is really all that more efficient than what I was doing before.

I have a function that runs sql queries. This is what my block of code looked like:

Database connection:

function connectDB(){
     $con = mysqli_connect(server, username, password, database);
     return $con;
}

Query function:

function executeQuery($payload){
     $con = connectDB;
     $result = mysqli_query($con, $payload);
     return $result;
}

As you can see, that's not very efficient because I'm creating a new database connection every time executeQuery is called. So I figured I'd try it using OOP.

Database connection (OOP):

function connectDB(){
     $con = new mysqli(server, username, password, database);
     return $con;
}

Database query (OOP):

function executeQuery($payload){
     $con = connectDB();
     $result = $con->query($payload);
     return $result;
}

Now to me, it seems that I am obviously doing something wrong. Each time a query is called I am re-instantiating the mysqli class and I assume that mean's that I am making another database connection.

So how do I do this properly and efficiently?

Answer 1

So how do I do this properly and efficiently?

This really has nothing to do with using MySQLi in a procedural versus OOP way.

What this has to do with is the following line:

$con = connectDB();

This will recreate the database connection on every query. Which, as you noted, is not efficient.

There are many ways to solve this. For example:

• Use the mysqli class directly.
• Pass $con to executeQuery() (Dependency Injection)
• Create a DB class with both connectDB() and executeQuery().

I usually use mysqli directly as I see no reason to wrap the native class. I create the connection object globally (in a config file) and use Dependency Injection when other objects/functions need it.

Answer 2

Although your procedural approach can be solved pretty easily

function connectDB(){
     return mysqli_connect(server, username, password, database);
}
function executeQuery($payload){
    static $con;
    id (!$con)
    { 
        $con = connectDB();
    }
    return $con->query($payload);
}

an OOP approach would be indeed better. I am not an OOP pro, but you can take a look at my approach which at least using encapsulation to hide all the dirty job inside and provide concise methods to get the data already in desired format like this:

// to get a username
$name = $db->getOne('SELECT name FROM table WHERE id = ?i',$_GET['id']);
// to get an array of data
$data = $db->getAll("SELECT * FROM ?n WHERE mod=?s LIMIT ?i",$table,$mod,$limit);
// to get an array indexed by id field
$data = $db->getInd('id','SELECT * FROM ?n WHERE id IN ?a','table', array(1,2));
// to get a single dimensional array
$ids = $db->getCol("SELECT id FROM tags WHERE tagname = ?s",$tag);

// a simple code for the complex INSERT
$data = array('offers_in' => $in, 'offers_out' => $out);
$sql = "INSERT INTO stats SET pid=?i,dt=CURDATE(),?u ON DUPLICATE KEY UPDATE ?u";
$db->query($sql,$pid,$data,$data); 

Answer 3

As a solution for your exact problem : "You do not want to instantiate a new MySQL connection for each time a query is executed" ,

Well, we can think about the following :

You need to make your connection variable ($con) in GLOBAL scope, such that when accessed through any function you can grab THAT variable you set before, not instantiate a new one.

we can do this using keyword "global" , as following :

---

The connection function :

function &connectDB(){
     global $con;
     if(empty($con)) {
         $con = new mysqli(server, username, password, database);
     }
     return $con;
}

And for more performance , we avoid cloning/copying the connection variable/resource by using reference function ( &connectDB ),

---

Query Execution function

Now we've set the connection function in a flexible way , to set the queryExecution function , we can use more than one solution :

First solution :

function executeQuery($payload){
     $con = &connectDB(); // do not forget the () , it's good practice
     return $con->query($payload);
}

In this solution we made use of "reference" , so the expression :

$con = &connectDB();

will set the variable $con as a reference/shortcut for global $con (i.e : just pointing to the global variable $con)

or

Second solution :

function executeQuery($payload){
     global $con;
     return $con->query($payload);
}

but for the second solution : Function "connectDB()" MUST be called at least once before any calling to "executeQuery()", In order to make sure that there a connection has been established with the database,

Keep in mind that, according to this solution , calling "connectDB()" more than once will not create more than one connection , once it is called , connection is created, if called again it will return the PREVIOUSLY created connection.

Hope it helps :)

by the way : stay with the OOP approach for database connection , it has much more benefits over the procedural ways, & I recommend using PDO, it is much more portable.