Best practices / most practical ways to implement mysqli connections

I’m working on streamlining a bit our db helpers and utilities and I see that each of our functions such as for example findAllUsers(){....} or findCustomerById($id) {...} have their own connection details for example :

function findAllUsers() {
    $srv = 'xx.xx.xx.xx';
    $usr = 'username';
    $pwd = 'password';
    $db = 'database';
    $port = 3306;
    $con = new mysqli($srv, $usr, $pwd, $db, $port);

    if ($con->connect_error) {
        die("Connection to DB failed: " . $con->connect_error);
    } else {
        sql = "SELECT * FROM customers..."
        .....
        .....
    }

}

and so on for each helper/function. SO I thought about using a function that returns the connection object such as :

function dbConnection ($env = null) {
    $srv = 'xx.xx.xx.xx';
    $usr = 'username';
    $pwd = 'password';
    $db = 'database';
    $port = 3306;
    $con = new mysqli($srv, $usr, $pwd, $db, $port);

    if ($con->connect_error) {
        return false;
    } else {
        return $con;
    }
}

Then I could just do

function findAllUsers() {
    $con = dbConnection();
    if ($con === false) {
        echo "db connection error";
    } else {
        $sql = "SELECT ....
        ...
    }

Is there any advantages at using a function like this compared to a Class system such as $con = new dbConnection() ?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You should open the connection only once. Once you realize that you only need to open the connection once, your function dbConnection becomes useless. You can instantiate the mysqli class at the start of your script and then pass it as an argument to all your functions/classes.

The connection is always the same three lines:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$con = new mysqli($srv, $usr, $pwd, $db, $port);
$con->set_charset('utf8mb4');

Then simply pass it as an argument and do not perform any more checks with if statements.

function findAllUsers(mysqli $con) {
    $sql = "SELECT ....";
    $stmt = $con->prepare($sql);
    /* ... */
}

It looks like your code was some sort of spaghetti code. I would therefore strongly recommend to rewrite it and use OOP with PSR-4.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0