PHP OOP database connect

Question

I have just started learning the concept of Object oriented programming and I've written this class with functions.

It works fine, but I'm interested in knowing if I have done this correctly...

Here is my code:

class Database{
    const DB_HOSTNAME = 'localhost';
    const DB_USERNAME = 'root';
    const DB_PASSWORD = 'password';
    const DB_NAME = 'shop';
    protected $_db_connect;
    protected $_sql;
    protected $_result;
    protected $_row;

    function db_connect(){
        $this->_db_connect = mysql_connect(self::DB_HOSTNAME,self::DB_USERNAME,self::DB_PASSWORD) or die(mysql_error());
    }

    function slect_db(){
        mysql_select_db(self::DB_NAME) or die(mysql_error());
    }

    function sql(){
        $this->_sql = 'SELECT * FROM users';
    }

    function query(){
        $this->_result = mysql_query($this->_sql);
    }

    function fetch_array(){
        while($this->_row = mysql_fetch_array($this->_result)){
            $username = $this->_row['user_USERNAME'];

            echo "<ul>";
                echo "<li>".$username."</li>";
            echo "</ul>";
        }
    }

    function db_close(){
        mysql_close($this->_db_connect);
    }
}

$database = new Database();
$database->db_connect();
$database->slect_db();
$database->sql();
$database->query();
$database->fetch_array();
$database->db_close();

Answer 1

Please use PDO or MySQLi as it's more secure and mysql_* functions are deprecated as stated above, I've provided some generic code using PDO to help you with that new venture. As stated in the comments, you really shouldn't be echoing out the data if you're looking for an object oriented design, what you should be doing is after you do the query, return the rows that you fetched and from there use something such as a foreach loop to then display your data. Doing your database class this way will also make sure that multiple database connections aren't open at once! Note, this code is just for you to reference, and should be tested before use in production or anything live.

config.php:

<?php
    //Enter your database connection details here.
    $host = 'localhost'; //HOST NAME.
    $db_name = 'databasename'; //Database Name
    $db_username = 'root'; //Database Username
    $db_password = ''; //Database Password

    try
    {
        $pdo = new PDO('mysql:host='. $host .';dbname='.$db_name, $db_username, $db_password);
    }
    catch (PDOException $e)
    {
        exit('Error Connecting To DataBase');
    }
?>

database.class.php:

<?php
    class database
    {
        function __construct($pdo)
        {
            $this->pdo = $pdo;
        }

        function getData()
        {
            $query = $this->pdo->prepare('SELECT * FROM database');
            $query->execute();
            return $query->fetchAll();
        }
    }
?>

index.php:

<?php
    require_once 'config.php';
    require_once 'database.class.php';
    $db = new database($pdo);
    $rows = $db->getData();
?>

Answer 2

It is possible to improve the way you connect to databases using autoloading and dependency injection containers. Here is a way of using Auryn to connect to your database while being sure there is only one connection opened and not having to manually require files throughout your application.

I will cover only PDO and Auryn here. There are other dependency injection containers and notably the mysqli extension to connect to database, but the content should help you using another container if you wish to do so.

The database class

Having a database class is superfluous. The \PDO class is already providing all necessary methods to query the database. Having a database class makes you repeat the functions it provides and limits your actions (or makes you create many functions) when you want to for example use multiple different fetch styles depending on your needs in a specific method.

Dependency Injection

If you haven't already, have a read on dependency injection. The point is that when a class needs to access the database, it should not have to bother constructing the \PDO object, it should be constructed with it:

class Mapper {
    private $pdo;
    public function __construct(\PDO $pdo) {
        $this->pdo = $pdo;
    }
    public function createFromId($id) {
        $stmt = $this->pdo->prepare("SELECT name FROM foo WHERE id=:id");
        $stmt->execute([
            ":id" => $id,
        ]);
        return $stmt->fetchObject();
    }
}

Notice that I directly pass the \PDO object, not a wrapper class. That way, I always have access to all of its capabilities, not only a subset of user-defined functions.

Dependency Injection Container

A dependency injection container helps build your classes, giving them the objects they need, and giving you great flexibility on how to actually build those objects. Here I'll only focus on configuring and sharing a \PDO object through the use of Auryn.

I assume you have installed the required Auryn class, the easier way is using composer. This is out of the scope of this answer, there are multiple resources on how to use it.

• Create the injector

  $injector = new \Auryn\Injector();
  

• Define the \PDO class parameters

  $injector->define("PDO", [
      ":dsn" => "mysql:host=localhost;charset=utf8;dbname=dbname",
      ":username" => "user",
      ":passwd" => "passwd",
      ":options" => [
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
          PDO::ATTR_EMULATE_PREPARES => false,
      ],
  ]);
  

  You can either write the configuration parameters directly here, or get them from a config file. I like having a config.ini file and using parse_ini_file() to get my configuration options, as I can easily switch databases by editing a config file.

• Share the \PDO object

  $injector->share("PDO");
  

  This part is really important. This lines makes the injector give the same \PDO object every time it is tasked with constructing a class that needs a connection. Note that the order of the lines is not important, you can share the class before defining it, only make sure to create you database needing classes after writing both those lines.

• Create your objects

  $mapper = $injector->make("Mapper");
  

  That's it. The injector will create your mapper object, creating the \PDO object if it haven't already been, passing the existing instance if it has.

Autoloading

Assuming you have used composer, you can make use of its great autoloader. Otherwise you can also roll you own autoloader.

The point here is to stop having require() everywhere in your code, especially if you have complex class hierarchies, which you should have in a single responsibility compliant class system.

Wrapping up

With this set up, you now can use the \PDO object in your classes while being assured there will only be one instance per request, without the need to require files everywhere, and without using a singleton anti-pattern.