How to reuse a MySQLi connection

Question

I've ran into an issue with user already has more than 'max_user_connections' active connections

This happens because I use functions for the website functionality, each function contains a

$mysqli = connect();

Though at the same time, each function closes the mysqli at the end of function

mysqli_close($mysqli);

I do not want to include $mysqli as a parameter of the function, as the function is dynamic and would be tiresome to include it with each function call.

the connect() function just contains:

$mysqli = new mysqli(...);
return $mysqli;

I believe the error is thrown because the function sometimes calls itself for different executions, based on user input and pre-requisites... even though the mysqli is closed at the end of the function...

I'd like to know if there is a way of re-using a $mysqli connection sort of like in a global way...

I'm not sure whether I've explained myself fully so if you need some more information please leave a comment.

Answer 1

The proper way to reuse the same object across multiple functions/classes is to use dependency injection. Together with DI container, it can take care of providing the necessary dependencies to your functions leaving them clean.

The principle of clean code demands that your functions do not have side effects and they only do what their name states. This means that you should never be using globals in your functions as that would be causing side effects. The proper way to provide data without side effects to your function is via parameters. A connection to the database is the data that is required by your function.

If your code will be used as some kind of public library, it's even more reason to keep it clean. A function cannot have surprising behaviour. The best way to reduce surprises is by having a clear function signature. This means that all dependencies are listed as parameters with their correct types, the function cannot take dynamic arguments, and the return type must be clearly specified.

---

When it comes to mysqli connections (or PDO for that matter which you should prefer over mysqli), the connection should only be opened once during the execution of the script. You should never need to close the connection manually.

---

Here's an example of clean code. Let's imagine that you need to save bookings in the database. You create a service in your model:

<?php

class BookingService
{
    public function __construct(protected mysqli $mysqli)
    {
    }

    public function saveBooking(Booking $booking)
    {
        $stmt = $this->mysqli->prepare('INSERT INTO bookings (date, surname) VALUES (?,?)');
        $stmt->execute([$booking->getDate(), $booking->getSurname()]);
    }
}

Then in your controller, you just use your service as a dependency:

class BookingController
{
    public function __construct(protected BookingService $bookingService)
    {
    }

    public function index(Request $request)
    {
        $boooking = new Booking($request->get('date'), $request->get('surname'));
        $this->bookingService->saveBooking($boooking);
    }
}

Your DI container takes care of instantiating the mysqli and BookingService class. You can only create value objects in your controller. The necessary data is passed via parameters. This makes for a very clean and understandable code. Everyone knows what each function does, there are no surprises and everything has type specified.

Answer 2

There are a number of techniques to instantiate a variable only once. This is an anti-solution. Please do not use solutions listed here!

Static variable

If you only want a variable to be instantiated the first time the function is called, you can use a static variable.

function foo(){
    static $mysqli;
    if ($mysqli === null) {
        $mysqli = connect();
    }
    // call the same function in recursive mode and the same connection will be reused
    foo();
    // do not close the connection manually!
}

This is a bad solution because your function fetches the dependency manually. You should not create functions that have side effects or multiple objectives. A function that connects should not be called from within a function that does something else. This leads to nightmarish code, which is the reason why you are having this problem now.

Using singleton

Singletons are bad for testing and for code readability. Anytime you need to change the dependency, you need to change all the places where the singleton is used. For a better explanation of why they are bad see What are drawbacks or disadvantages of singleton pattern?

Using globals

Globals are very bad for code maintainability. They are the bane of programmers since forever. It's precisely the reason why we have encapsulation and dependency injection.

Using a global would be very similar to using a static variable, except that the side effect is now global.

function foo(){
    // declare a variable as global.
    // this will instantiate the variable to null if it doesn't exist as a global yet
    global $mysqli;
    // if it's null, connect
    if ($mysqli === null) {
        $mysqli = connect();
    }
    // call the same function in recursive mode and the same connection will be reused
    foo();
    // do not close the connection manually!
    // but if you must close the connection, just set the variable back to null
}

Do not use any of the above solutions! These are examples of bad unmaintainable spaghetti code. A programmer's nightmare. This answer is only supposed to be a warning of what not to do!