Chapter 7 - Databases

Now we have finally come to the part where we get to play with the databases. For the course of the tutorial I will use PDO as an abstraction layer because we might not always want to use mysql as our database, and I want to do this as modular as possible. So first we want to add next part of code to our config.php

//DATABASES
define ('CONNECTIONS', [
    'DEFAULT' => [
        'DB_TYPE' => 'mysql',
        'DB_HOST' => '127.0.0.1',
        'DB_PORT' => '3306',
        'DB_NAME' => 'mvc',
        'DB_USER' => 'mvcuser',
        'DB_PASS' => 'mvcpassword',
        'DB_CHARSET' => 'utf8'
    ],
]

As you can see, we will be defining our database in a multidim array so if you ever find yourself in need of handling mltiple databases simultaneously just add them to the list.
Now that we have set up the defaults it's time for us to create our database handler, so create file "DB.php" inside of /application/helpers/

<?php

namespace application\helpers;


use PDO;

class DB extends PDO
{

    protected static $instances = array();

    public static function connect($connection = 'DEFAULT')
    {
    		//if database is not explicitly set use default (from config.php)
        ($connection === 'DEFAULT') ? $db = CONNECTIONS['DEFAULT'] : $db = CONNECTIONS[$connection];
        if($db === NULL) die("Connection '$connection' is not defined");

        $type = $db['DB_TYPE'];
        $host = $db['DB_HOST'];
        $name = $db['DB_NAME'];
        $user = $db['DB_USER'];
        $pass = $db['DB_PASS'];

        $id = "$type.$host.$name.$user.$pass";

		//don't create new instance if one is already running
		//if you need more, create new object 
        if (isset(self::$instances[$id])) {
            return self::$instances[$id];
        }

		//create a connection
        $instance = new DB("$type:host=$host;dbname=$name;charset=utf8", $user, $pass);
        $instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        self::$instances[$id] = $instance;

        return $instance;
    }
}

Next we are gonna make method for selecting data from the database

public function select($sql, $array = array(), $fetchMode = PDO::FETCH_OBJ, $class = '', $single = null)
{
    if (stripos($sql, 'select ') !== 0) {
        $sql = 'SELECT ' . $sql;
    }
    $stmt = $this->prepare($sql);
    foreach ($array as $key => $value) {
        if (is_int($value)) {
            $stmt->bindValue("$key", $value, PDO::PARAM_INT);
        } else {
            $stmt->bindValue("$key", $value);
        }
    }
    $stmt->execute();
    if ($single === null) {
        return $fetchMode === PDO::FETCH_CLASS ? $stmt->fetchAll($fetchMode, $class) : $stmt->fetchAll($fetchMode);
    }

    return $fetchMode === PDO::FETCH_CLASS ? $stmt->fetch($fetchMode, $class) : $stmt->fetch($fetchMode);
}

We can also abstract it by adding methods for specific selects like a

//select only single row
public function find($sql, $array = array(), $fetchMode = PDO::FETCH_OBJ, $class = '')
{
    return $this->select($sql, $array, $fetchMode, $class, true);
}

//select only row count
public function count($table, $column= 'id')
{
    $stmt = $this->prepare("SELECT $column FROM $table");
    $stmt->execute();
    return $stmt->rowCount();
}

Following this logic, insert method would look like this:

//table is table name
//data is an assoc array where keys are field names, and values are data we want to insert
public function insert($table, $data)
{
    ksort($data);
    $fieldNames = implode(',', array_keys($data));
    $fieldValues = ':'.implode(', :', array_keys($data));
    $stmt = $this->prepare("INSERT INTO $table ($fieldNames) VALUES ($fieldValues)");
    foreach ($data as $key => $value) {
        $stmt->bindValue(":$key", $value);
    }
    $stmt->execute();
    return $this->lastInsertId();
}

And with that we practically insured that we will always use prepared statements, and amount of code we would be writing is as minimized as it can be because of the abstraction. Let's do that for the update function as well.

public function update($table, $data, $where)
{
    ksort($data);
    $fieldDetails = null;
    foreach ($data as $key => $value) {
        $fieldDetails .= "$key = :$key,";
    }
    $fieldDetails = rtrim($fieldDetails, ',');
    $whereDetails = null;
    $i = 0;
    foreach ($where as $key => $value) {
        if ($i == 0) {
            $whereDetails .= "$key = :$key";
        } else {
            $whereDetails .= " AND $key = :$key";
        }
        $i++;
    }
    $whereDetails = ltrim($whereDetails, ' AND ');
    $stmt = $this->prepare("UPDATE $table SET $fieldDetails WHERE $whereDetails");
    foreach ($data as $key => $value) {
        $stmt->bindValue(":$key", $value);
    }
    foreach ($where as $key => $value) {
        $stmt->bindValue(":$key", $value);
    }
    $stmt->execute();
    return $stmt->rowCount();
}

Call parameters here are the same as with insert with the addition of where parameter, because we need to specify which row we wnat to update. For example, use of update function would look like this:

$data = array(
    'firstName' => 'Joe',
    'lastnName' => 'Smith',
    'email' => 'someone@domain.com'
);
$where = array('memberID' => 2);
$db->update('users', $data, $where);

Now delete() method is somewhat similar to the update() but instead of data parametere we have limit

public function delete($table, $where, $limit = 1)
{
    ksort($where);
    $whereDetails = null;
    $i = 0;
    foreach ($where as $key => $value) {
        if ($i == 0) {
            $whereDetails .= "$key = :$key";
        } else {
            $whereDetails .= " AND $key = :$key";
        }
        $i++;
    }
    $whereDetails = ltrim($whereDetails, ' AND ');
    if (is_numeric($limit)) {
        $uselimit = "LIMIT $limit";
    }
    $stmt = $this->prepare("DELETE FROM $table WHERE $whereDetails $uselimit");
    foreach ($where as $key => $value) {
        $stmt->bindValue(":$key", $value);
    }
    $stmt->execute();
    return $stmt->rowCount();
}

This should cover most use cases, but somtimes we will need a little more flexibility with generating statements. For those cases we can also add this method:

//only use this if you know what you are doing
public function raw($sql)
{
    return $this->query($sql);
}

Now we can integrate our DB helper with the rest of the framework by calling it in our core model class, so let's open /application/core/Model.php

<?php

namespace application\core;


use application\helpers\DB;
use application\helpers\Request;

abstract class Model 
{	
	protected $request;
	protected $db;

    protected static $_table = '';
    protected static $_primaryKey = '';

    protected $columns;

	public function __construct() 
	{
		$this->request = new Request;
        $this->db = DB::connect();

        $this->columns = [];
	}
}

Now it looks a little more like a legit model class. Now when you create new models by extending this class, you will automagically be connected to the database, you just need to specify base table and primary. Let's add basic crud functions to our core model as well.

protected function populate($object)
{
    foreach ($object as $key => $value) {
        $this->set($key, $value);
    }
}

public function set($column,$value)
{
    $this->columns[$column] = $value;
}

public function get($column)
{
    return $this->columns[$column];
}

public function create()
{
    return $this->db->insert(static::$_table, $this->columns);
}

public function update($where = NULL)
{

    return $this->db->update(static::$_table, $this->columns, $where);
}

public function save($where = NULL)
{
    if($where || $this->get(static::$_primaryKey) !== null) $this->db->update(static::$_table, $this->columns, ($where)?$where:[static::$_primaryKey=>$this->get(static::$_primaryKey)]);
    else $this->db->insert(static::$_table, $this->columns);
    return $this;
}

public static function delete($value){
    return DB::connect()->delete(static::$_table, [static::$_primaryKey => $value]);
}

public static function purge()
{
    return DB::connect()->truncate(static::$_table);
}

public static function getAll($condition=array(),$order=NULL,$startIndex=NULL,$count=NULL,$group=NULL){
    $query = "SELECT * FROM " . static::$_table;
    if(!empty($condition)){
        $query .= " WHERE ";
        foreach ($condition as $key => $value) {
            $query .= $key . "=:".$key." AND ";
        }
    }
    $query = rtrim($query,' AND ');
    if($group){
        $query .= " GROUP BY " . $group;
    }
    if($order){
        $query .= " ORDER BY " . $order;
    }
    if($startIndex !== NULL){
        $query .= " LIMIT " . $startIndex;
        if($count){
            $query .= "," . $count;
        }
    }
    foreach ($condition as $key => $value) {
        $condition[':'.$key] = $value;
        unset($condition[$key]);
    }

    return DB::connect()->select($query,$condition);
}

public static function findOne($value){

    $sql = "SELECT * FROM " . static::$_table . " WHERE " . static::$_primaryKey . " = :" . static::$_primaryKey;
    $params = [ ":" . static::$_primaryKey => $value];

    $result = DB::connect()->find($sql, $params);
    return $result;
}

public static function getCount(){
    return DB::connect()->count(static::$_table);
}

And now we have very flexible base model with database support.