Creating a Database Connection As Far As We Obey to OOP

In this post, we will learn a pretty database connection in PHP. We have to obey the OOP with this case. Firstly, i will have interface file in project directory called IconnectionInfo.php. As below. But we cannot include variables in interfaces, you can include constants.For example, the following interface has constants used in a MySQL connection:

<?php

interface IConnectInfo
{
    const HOST ="localhost";
    const UNAME ="root";
    const DBNAME = "symf0ny";
    const PW = "sem";
    function getConnection();
}
?>

Implement the interface just like any other PHP interface. The values of the constants can then be passed using the scope resolution operator in the implementation:

<?php

include_once('IConnectInfo.php');

class Database implements IConnectInfo
{

    private static $instance = null;
    private $conn;

    private $server = IConnectInfo::HOST;
    private $currentDB = IConnectInfo::DBNAME;
    private $user = IConnectInfo::UNAME;
    private $pass = IConnectInfo::PW;

    private function __construct()
    {
        try {
            $this->conn = new PDO("mysql:host=$this->server;dbname=$this->currentDB", $this->user, $this->pass
            );
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->conn->exec('set names utf8');
            echo $this->server . " connected successfully" . PHP_EOL;
        } catch (PDOException $e) {
            echo "Connection failed: " . $e->getMessage();
            die;
        }
    }

    public static function getInstance()
    {
        if (!self::$instance) {
            self::$instance = new Database();
        }

        return self::$instance;
    }

    public function getConnection()
    {
        return $this->conn;
    }


    public function getSelectQueryResult($query = '')
    {
        try {
            $query = $this->conn->prepare($query);
            $query->execute();
            return $query->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            echo $query . "<br>" . $e->getMessage();
        }
    }
}

The only method is getConnection(), but your interface could be made up of nothing but constants if you wanted. The values are passed to the class properties (private variables in the example) using the name of the interface (IConnectInfo), the scope resolution operator, and the name of the constant.

Well, we need only initializer file. It is named app.php. The following codes means it.

<?php

require __DIR__ . '/class/Database/Database.php';

$dbInstance = Database::getInstance();
$dbConnection = $dbInstance->getConnection();

From now on, we can get database connection.

That's cool! My goal is precisely enlighten our minds with OOP and database connection. I hope, i could do it. In this article, so much for now. We have learned connect any database connection with interfaces and constants.

Comments

[Franco Traversaro]

I strongly disagree with this approach.
Putting connection parameters on the interface is one of the dumbest things I've ever seen: what if you have to connect to two or more databases in the same time? Parameters have to be passed as, you don't say, parameters; use a configuration file or, even better, environment variables.
On the code: why you set private properties from interface constants while your class implements it? In the constructor you can simply use self::DBNAME.
You are trying to have a Singleton, so your constructor must be private. But Singleton are not a good choice: it's vastely considered the most harmful design pattern of the Gang of Four, and I agree. Try to seriously do some unit testing and see.
PDO is already object oriented. Having a private instance is useful only if you are planning to create adapters, different classes for different databases; but PDO can connect to a lot of DBs, so you could simply extend the class PDO, override the constructor if you want manage the parameters in some smart way (but it's better take just the connection string and leave the burden of the organization to the project that will use the class) and add your commodity methods. THAT's OOP.

[Aleksi Kauppila]

Database appears to be singleton, but it's possible to freely create instances of Database via public constructor.

IconnectionInfo provides getConnection() which returns a PDO object but Database also provides getSelectQueryResult()-method for results.

So it seems there's one responsibility too much. Do you want to provide users with database connections or get them results from the database? (Also, i think you should move the code from the constructor inside getConnection().)

Whichever responsibility you choose, i feel it would be more beneficial to provide a Database interface. This interface could be implemented by classes like PostgreSQLDatabase, MySQLDatabase, SQLiteDatabase etc. These implementations could take connection information as arguments because hard coding them is a bad practice.

Interfaces shouldn't be used as configuration. They should have no direct effect on the implementations internals.