Chapter 05: Database connection

Introduction

Almost every application that we develop we’ll consume data from a source, normally a database. For this book, we will be using MariaDB as our database engine although ideally, our application should be database agnostic. We can accomplish this by introducing abstraction layers. We’ll build a database class that uses PDO internally. PDO provides one abstraction layer, and our class will simplify working with that extension.

Database class

<?php
define('DEVELOPMENT_ENVIROMENT', true);
const CONFIG = [
    'DB_SERVER' => 'localhost',
    'DB_USER' => 'root',
    'DB_PASSWORD' => '',
    'DB_DATABASE' => 'simplemvc'
];
define('SITE_BASE', 'http://localhost/simplemvc/');
define('APP_SESSION_ID', 'xeFLcX4gfj1h7WM6Yfrl');

We still don’t have the database simplemvc. That’s ok, we don’t need it just yet.

Now, in the core folder, put the following content in the db.php file:

<?php
namespace core;

class db {
  private $_connection;

  public function __construct(array $CONFIG)
  {        
    try {
      $this->_connection = new \PDO(
        "mysql:host=".$CONFIG["DB_SERVER"].";dbname="
          .$CONFIG["DB_DATABASE"], 
        $CONFIG["DB_USER"], 
        $CONFIG["DB_PASSWORD"]);
      $this->_connection->setAttribute(
        \PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    } catch (\PDOException $e) {
      trigger_error("Failed connecting to database: " 
        . $e->getMessage(), E_USER_ERROR);
    }
  }
}

We use our constructor to get a new PDO instance. We take a parameter named CONFIG, which is an array. Note that we are using type hinting to let php know what kind of parameter we should expect. Also, take note of the inverted slash \ at the beginning of the PDO class name. We need this because we are working in the core namespace, so every reference to a class we’ll be interpreted as a reference to a class in that namespace. The slash indicates that we are referencing a class in the global namespace.

There is not much in this function. We,ve wrapped everything in a try catch block, and we trigger an error if the connection fails. trigger_error creates a user-level error message. At this point, you could also write an entry in a log file. But it’s enough for now.

Let’s add another method.

    /**
     * Get pdo connection
     * @return \pdo pdo connection
     */
    public function getConnection()
    {        
        return $this->_connection;
    }

In this function we simply return the connection.

Now, we will build a method to return a resultset, containing all the records from a table.

/**
* Get all records of the specified table
* @param string table
* @param array $arrFields
* @return array the resulset
*/
public function getAll(string $table, array $arrFields)
{
    if (!empty($arrFields)) {
        $fields = implode(',', $arrFields);
        $sqlQuery = "SELECT $fields FROM $table";
    } else {
        $sqlQuery = "SELECT * FROM $table";
    }
    $conn = $this->getConnection();
    $stmt = $conn->prepare($sqlQuery);
    $stmt->execute();
    $stmt->setFetchMode(\PDO::FETCH_ASSOC);
    return $stmt->fetchAll();
}

We receive the table name, and optionally an array of field names. We build the query string to return all or some columns of the records, we retrieve the connection, prepare a sentence with the query string, execute it and return the result. Note that we are setting the fetch mode to FETCH_ASSOC, this means we can reference every column of the rows as properties, so we can have something like this:

foreach ($clients as $client) {
echo $client->name;
}

Now, on to the next method:

public function getOne(
  string $table, array $arrFields, string $key, int $value
)
{
  if (!empty($arrFields)) {
    $fields = implode(',', $arrFields);
    $sqlQuery = "SELECT $fields FROM $table WHERE $key = :value";
  } else {
    $sqlQuery = "SELECT * FROM $table WHERE $key = :value";
  }

  $conn = $this->getConnection();
  $stmt = $conn->prepare($sqlQuery);
  $stmt->bindParam(":value", $value, \PDO::PARAM_INT);
  $stmt->execute();
  $stmt->setFetchMode(\PDO::FETCH_ASSOC);
  return $stmt->fetch();
}

Now, this method is pretty similar to the previous one (we could definitely use a refactor but we leave it for later), except that we return a single row, based on some field (normally the id).

The next one is a little bit more complex:

public function save(
  array $data, string $table, $key = null, $value = null
)
{
  $sqlQuery = " $table SET ";

  foreach ($data as $k => $v) {
    $sqlQuery .= "$k = '$v',";
  }

  $sqlQuery = rtrim($sqlQuery, ",");

  if ($key) {
    $sqlQuery = "UPDATE " . $sqlQuery . " WHERE $key = :value";
  } else {
    $sqlQuery = "INSERT INTO " . $sqlQuery;
  }

  $conn = $this->getConnection();
  $stmt = $conn->prepare($sqlQuery);        

  if ($key) {
    $stmt->bindParam('value', $value);
  }

  $stmt->execute();

  return true;
}

Let’s analyze this part:

foreach ($data as $k => $v) {
    $sqlQuery .= "$k = '$v',";
}

We use the parameter $data, which is an associative array, to build the part of the query where we assign values to the fields. This relies on the fact that the array $data has keys that correspond to the field names. But, as we’ll see later, we rarely use the database class directly. Our models will deal with it.

In this other block:

if ($key) {
  $sqlQuery = "UPDATE " . $sqlQuery . " WHERE $key = :value";
} else {
  $sqlQuery = "INSERT INTO " . $sqlQuery;
}

We determine if we are dealing with an insert or an update, based on the presence or not of a key.

Now we’ll present two other short methods:

public function update(
    array $data, string $table, $key, $value
)
{
    $this->save($data, $table, $key, $value);
}

public function insert(array $data, string $table)
{
    $this->save($data, $table);
}

We are building upon the save method. This is always good, as we should reuse as much as possible.

The last one:

public function delete(
  string $table, string $key, int $value
)
{
  $conn = $this->getConnection();

  $stmt = $conn->prepare("DELETE FROM $table WHERE $key = :value");

  $stmt->bindParam(":value", $value, \PDO::PARAM_INT);

  if ($stmt->execute()) {
    return true;
  }

  return false;
}

We delete a record based on the value of the key.

And that 's it. We have everything we need for any kind of interaction with the database.

In the next chapter, we will focus on the Model, View and Controller classes. But, just to be sure everything is right, here is the corresponding gist of the db class.

See you in the next chapter.