Single php mysql connection for entire system

PHP
1

I am using a small scale custom PHP MVC inspired framework and have reached the point where i have many queries on the site, each query is in its own xxxModel.php file, and each xxxModel.php file extends the database connection class.

The general structure is similar to this:

  1. PostsModel extends CoreModel.
  2. CoreModel extends DatabaseClass.

In DatabaseClass i am connecting to mysql using PDO like this:

    public function __construct()
    {
        parent::__construct("mysql:host=".HOSTNAME.";dbname=".DB_DATABASE."",DB_USER,DB_PASS);
        $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

Until this point it is all working well, as it should.
The only problem is that each time i instantiate a new model, it eventually creates a new database connection since each xxxModel.php file extends Model.php which extends the DatabaseClass.

This at times is causing a “max_user_connections” error, and just connects to the database many times, which seems inefficient.

My question is what is a better practice of connecting to the database, and how should this multiple connections be handled?
Is it ok to have multiple connections (to the same database) or is it better to have one connection (or a limited amount) and just reuse that connection object?

I have googled this but have found quite a few different answers and each answer claims that the other solution is “bad”, e.g., using a singleton database class.
The singleton database class is the most common answer i find, which basically says that if you are going to use the same connection for the whole system, connect to the database in a singleton class, and just use that same object throughout the system. But even in the answers that give the singleton approach as the solution - it is also said that its not a good approach, so i wonder what is?

Any thoughts on this would be great, thanks!

2

Create a connection object and pass it by Dependency Injection to what ever object needs it. Don’t create multiple connections, don’t use a Singleton, Dont extend the DB connection class.

<?php declare(strict_types=1);

/**
 * Class Database
 */
class Database extends PDO
{
    /**
     * Database constructor.
     * @param $dsn
     * @param null $username
     * @param null $password
     * @param array $options
     */
    public function __construct($dsn, $username = NULL, $password = NULL, $options = [])
    {
        parent::__construct($dsn, $username, $password, $options);
    }
}


$host = '127.0.0.1';
$db = 'test';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false,];

$pdo = new PDO($dsn, $user, $pass, $options);

$sql = 'SELECT * FROM user';
$stmt = $pdo->query($sql);

foreach ($stmt as $row)
{
    echo '<pre>', print_r($row, true), '<pre>';
}
3

Thank you, yes this is very clear, but i am wondering how to do this while using the Models system.
All of my queries are in different model files, for example:

PostsModel.php
CommentsModel.php
etc.

Each of these models need a connection to the database.

Now, i have a Database class that extends PDO, but each time i either extend that class, or just instantiate the class, it means that i am creating another connection instead of using the same connection for the whole system.

This is basically what i have set up:

class Database extends PDO
{
    /**
     * Constructor function
     *
     * Construct the parent (PDO) and create a new connection.
     */
    public function __construct()
    {
        parent::__construct("mysql:host=".HOSTNAME.";dbname=".DB_DATABASE."",DB_USER,DB_PASS);
        $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
}

class CoreModel
{
    public function __construct()
    {
        //Instantiate the database
        $this->db = new Database();
    }

    //Common code and functions for Model files
}

class PostsModel extends CoreModel 
{
    /**
     * Constructor function
     *
     * Constructs the Model
     */
    public function __construct()
    {
        parent::__construct();
    }

    //Do stuff for the posts..
}

class PostsController extends BaseController
{
    public function __construct()
    {
        parent::__construct();
        $this->postsModel = new PostsModel();
    }
}

So as you can see, and this may not be a very good way of doing things, but i have a PostsController, this will instantiate the PostsModel, which as a result will create a new connection to the database because it extends CoreModel which instantiates the Database class.
This is the same for all of the other Model files, so they all end up instantiating a new Database class instead of using a common one, which potentially leads to many new connections on a single page depending on how many Model files are being used for that page.

5

I said, do not extend the database class. Instantiate it once and pass the connection object where it is needed.

6

Right, my mistake, i dont extend the DB connection class, but i do instantiate it in the CoreModel file, which is extended by all other Model files which leads to all of the different connection objects.

Your saying that instead of instantiating it in the CoreModel file, i should find a way to instantiate it in a higher (or lower) level and pass that object to each model file i instantiate?

7

If I understand what your doing, yes. Instantiate the db connection then use Dependency Injection to pass the connection to the Core. Do not instantiate the connection in the Core. That is what is creating multiple connections.

If all the models use core, you do not have to pass the connection to every model.

8

OK, i dont know anything about Dependency Injection really so ill do my research about it.
Its actually something that came up in quite a few articles when i read about this subject but none of them really explained what it is, they just mentioned it, so ill read about it.

Do you think that there is any other way of doing this though? Or is Dependency Injection just the best solution?

9

There are, but you are not there, and those other options could be up for debate. No one is going to tell you it is wrong to use DI.

You should be able to grasp it fairly easily. It’s really just like passing parameters to a function.

10

Right, makes sense.

OK, great, ill look in to it.

Thanks for the help!

11

I have been reading around for the past few hours, i believe i understand the concept of DI, its a fairly simple concept, but i haven’t found a way/came up with a way to apply it to my situation.

You are right that all models use the CoreModel, so it makes sense that only the CoreModel needs access to the database.

The question is, how do i get the Database connection object to the CoreModel so that it can be used as one connection for the whole site instead of many separate connections?

I could instantiate the Database class in the CoreController (the controller all other controllers extend), and then pass that object on to what ever model the specific controller is instantiating, and then pass the object on to the CoreModel, but that seems a bit inefficient, plus, there could be some pages that dont need that database connection, so i would have to add additional code to the CoreController to identify which pages should not have the connection.

Any ideas?

12

NO! Re-read my posts. If you have a main config file, Instantiate the DB connection there.
(I am trying to give you oppurtunity to learn this on your own without spoon feeding code. If you really get stuck I will show you.)

  1. Instantiate Connection
  2. Pass connection object to CoreController using Dependency Injection
  3. Your done
13

OK, i have read through it again and read a few more sources, and i think i just don’t understand how to implement this in my current set up.

As far as i can see, for me to set up the database connection for the whole system and pass it on as a DI i would have to instantiate it in my Dispatcher class, and then pass it as a parameter to what ever controller is being instantiated by the Dispatcher.

The framework is initiated in index.php, and goes through Router, Response, Request and Dispatcher classes, the Dispatcher is the one that will actually instantiate the required class, so i would have to instantiate the Database class in the Dispatcher, send it to the controller being used, and from there send it to the model?

If so, then what about pages that go through this same process but don’t require a database connection, it seems not very good to create a new connection even for pages that don’t need it.

14

A couple suggestions…

  1. Step aside from your project and create a new basic project to practice DI with minimal code.

  2. Put your project on a repo such as GitHub so we can view the project as a whole. Your Architecture is likely flawed making things more difficult for you.

  3. You don’t seem to be paying attention to what I have wrote. I cant be any more clear. Read my posts again

15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.