1.5. Connecting classes for working with the database and templates

We’ve created a structure for our framework, and now it’s time to think about storing data: news, products. The object for working with the database should be able to:

• Manage the connection to the database
• Provide a lightweight abstraction over the database
• Cache queries
• Make common database operations easier

To do this, we will create the object at Registry/objects/db.class.php:

<?php

/**
 * Database management
 * Provides lightweight abstraction over the database
 */
class database {

  /**
   * Allows multiple connections to the database
   * Rarely used but sometimes useful
   */
  private $connections = array();

  /**
   * Tracks the active connection
   * setActiveConnection($id) allows changing the active connection
   */
  private $activeConnection = 0;

  /**
   * Queries that have been executed and cached for future use
   */
  private $queryCache = array();

  /**
   * Data that has been retrieved and cached for future use
   */
  private $dataCache = array();

  /**
   * Record of the last executed query
   */
  private $last;

  /**
   * Constructor
   */
  public function __construct()
  {

  }

  /**
   * Creates a new connection
   * @param String database hostname
   * @param String database username
   * @param String database password
   * @param String the database we're using
   * @return int the ID of the new connection
   */
  public function newConnection( $host, $user, $password, $database )
  {
    $this->connections[] = new mysqli( $host, $user, $password, $database );
    $connection_id = count( $this->connections ) - 1;
    if( mysqli_connect_errno() )
    {
      trigger_error('Error connecting to host. '.$this->connections[$connection_id]->error, E_USER_ERROR);
    }

    return $connection_id;
  }

  /**
   * Closes the active connection
   * @return void
   */
  public function closeConnection()
  {
    $this->connections[$this->activeConnection]->close();
  }

  /**
   * Changes the active connection
   * @param int the new connection ID
   * @return void
   */
  public function setActiveConnection( int $new )
  {
    $this->activeConnection = $new;
  }

  /**
   * Saves a query to the cache
   * @param String the query string
   * @return int pointer to the cached query
   */
  public function cacheQuery( $queryStr )
  {
    if( !$result = $this->connections[$this->activeConnection]->query( $queryStr ) )
    {
      trigger_error('Error executing and caching query: '.$this->connections[$this->activeConnection]->error, E_USER_ERROR);
      return -1;
    }
    else
    {
      $this->queryCache[] = $result;
      return count($this->queryCache)-1;
    }
  }

  /**
   * Returns the number of rows in the cache
   * @param int the query cache pointer
   * @return int the number of rows
   */
  public function numRowsFromCache( $cache_id )
  {
    return $this->queryCache[$cache_id]->num_rows;
  }

  /**
   * Returns rows from the cache
   * @param int the query cache pointer
   * @return array the row
   */
  public function resultsFromCache( $cache_id )
  {
    return $this->queryCache[$cache_id]->fetch_array(MYSQLI_ASSOC);
  }

  /**
   * Saves data to cache
   * @param array the data
   * @return int pointer to the data in the cache
   */
  public function cacheData( $data )
  {
    $this->dataCache[] = $data;
    return count( $this->dataCache )-1;
  }

  /**
   * Retrieves data from cache
   * @param int data cache pointer
   * @return array the data
   */
  public function dataFromCache( $cache_id )
  {
    return $this->dataCache[$cache_id];
  }

  /**
   * Deletes records from a table
   * @param String the table
   * @param String the condition for which rows to remove
   * @param int number of rows to remove
   * @return void
   */
  public function deleteRecords( $table, $condition, $limit )
  {
    $limit = ( $limit == '' ) ? '' : ' LIMIT ' . $limit;
    $delete = "DELETE FROM {$table} WHERE {$condition} {$limit}";
    $this->executeQuery( $delete );
  }

  /**
   * Updates records in a table
   * @param String the table
   * @param array of changes field => value
   * @param String the condition
   * @return bool
   */
  public function updateRecords( $table, $changes, $condition )
  {
    $update = "UPDATE " . $table . " SET ";
    foreach( $changes as $field => $value )
    {
      $update .= "`" . $field . "`='{$value}',";
    }

    // remove trailing comma
    $update = substr($update, 0, -1);
    if( $condition != '' )
    {
      $update .= " WHERE " . $condition;
    }

    $this->executeQuery( $update );

    return true;
  }

  /**
   * Inserts records into a table
   * @param String the database table
   * @param array data to insert field => value
   * @return bool
   */
  public function insertRecords( $table, $data )
  {
    $fields  = "";
    $values = "";

    foreach ($data as $f => $v)
    {
      $fields  .= "`$f`,";
      $values .= ( is_numeric( $v ) && ( intval( $v ) == $v ) ) ? $v."," : "'$v',";
    }

    $fields = substr($fields, 0, -1);
    $values = substr($values, 0, -1);

    $insert = "INSERT INTO $table ({$fields}) VALUES({$values})";
    $this->executeQuery( $insert );
    return true;
  }

  /**
   * Executes a query
   * @param String the query
   * @return void
   */
  public function executeQuery( $queryStr )
  {
    if( !$result = $this->connections[$this->activeConnection]->query( $queryStr ) )
    {
      trigger_error('Error executing query: '.$this->connections[$this->activeConnection]->error, E_USER_ERROR);
    }
    else
    {
      $this->last = $result;
    }
  }

  /**
   * Gets rows from the last query (not from cache)
   * @return array
   */
  public function getRows()
  {
    return $this->last->fetch_array(MYSQLI_ASSOC);
  }

  /**
   * Returns the number of affected rows
   * @return int
   */
  public function affectedRows()
  {
    return $this->connections[$this->activeConnection]->affected_rows;
  }

  /**
   * Sanitizes input data
   * @param String data to sanitize
   * @return String sanitized data
   */
  public function sanitizeData( $data )
  {
    return $this->connections[$this->activeConnection]->real_escape_string( $data );
  }

  /**
   * Destructor, closes connections
   */
  public function __deconstruct()
  {
    foreach( $this->connections as $connection )
    {
      $connection->close();
    }
  }
}
?>

Before we connect to the database, let’s see what this class does. We can perform simple add, update, and delete operations through the class methods:

// Insert
$registry->getObject('db')->insertRecords( 'products', array('name'=>'Mug' ) );
// Update
$registry->getObject('db')->updateRecords( 'products', array('name'=>'Red mug' ), 'ID=2' );
// Delete
$registry->getObject('db')->deleteRecords( 'products', "name='Red mug'", 5 );

The class also supports caching.

Now let's add another object for template management at Registry/objects/template.class.php

<?php

// Constant defined in index.php to prevent direct access
if ( ! defined( 'FW' ) )
{
  echo 'This file can only be called from index.php and not directly';
  exit();
}

/**
 * Class for working with templates
 */
class template {

  private $page;

  /**
   * Constructor
   */
  public function __construct()
  {
    // Later we will add this Page class
    include( APP_PATH . '/Registry/objects/page.class.php');
    $this->page = new Page();
  }

  /**
   * Adds a template bit (partial) to the page
   * @param String $tag the tag where we insert the template, e.g. {hello}
   * @param String $bit path to the template
   * @return void
   */
  public function addTemplateBit( $tag, $bit )
  {
    if( strpos( $bit, 'Views/' ) === false )
    {
      $bit = 'Views/Templates/' . $bit;
    }
    $this->page->addTemplateBit( $tag, $bit );
  }

  /**
   * Inserts templates into the page
   * Updates page content
   * @return void
   */
  private function replaceBits()
  {
    $bits = $this->page->getBits();
    foreach( $bits as $tag => $template )
    {
      $templateContent = file_get_contents( $template );
      $newContent = str_replace( '{' . $tag . '}', $templateContent, $this->page->getContent() );
      $this->page->setContent( $newContent );
    }
  }

  /**
   * Replaces tags with new content
   * @return void
   */
  private function replaceTags()
  {
    $tags = $this->page->getTags();
    foreach( $tags as $tag => $data )
    {
      if( is_array( $data ) )
      {
        if( $data[0] == 'SQL' )
        {
          // Replace tags with data from cached query
          $this->replaceDBTags( $tag, $data[1] );
        }
        elseif( $data[0] == 'DATA' )
        {
          // Replace tags with cached data
          $this->replaceDataTags( $tag, $data[1] );
        }
      }
      else
      {
        // Replace simple tag
        $newContent = str_replace( '{' . $tag . '}', $data, $this->page->getContent() );
        $this->page->setContent( $newContent );
      }
    }
  }

  /**
   * Replace tags with data from DB
   * @param String $tag token/tag
   * @param int $cacheId ID of the cached query
   * @return void
   */
  private function replaceDBTags( $tag, $cacheId )
  {
    $block = '';
    $blockOld = $this->page->getBlock( $tag );

    while ($tags = Registry::getObject('db')->resultsFromCache( $cacheId ) )
    {
      $blockNew = $blockOld;
      foreach ($tags as $ntag => $data)
      {
        $blockNew = str_replace("{" . $ntag . "}", $data, $blockNew);
      }
      $block .= $blockNew;
    }
    $pageContent = $this->page->getContent();
    $newContent = str_replace( '<!-- START ' . $tag . ' -->' . $blockOld . '<!-- END ' . $tag . ' -->', $block, $pageContent );
    $this->page->setContent( $newContent );
  }

  /**
   * Replace page content with data from cache
   * @param String $tag
   * @param int $cacheId data cache ID
   * @return void
   */
  private function replaceDataTags( $tag, $cacheId )
  {
    $block = $this->page->getBlock( $tag );
    $blockOld = $block;
    while ($tags = Registry::getObject('db')->dataFromCache( $cacheId ) )
    {
      foreach ($tags as $tag => $data)
      {
        $blockNew = $blockOld;
        $blockNew = str_replace("{" . $tag . "}", $data, $blockNew);
      }
      $block .= $blockNew;
    }
    $pageContent = $this->page->getContent();
    $newContent = str_replace( $blockOld, $block, $pageContent );
    $this->page->setContent( $newContent );
  }

  /**
   * Get the page object
   * @return Object
   */
  public function getPage()
  {
    return $this->page;
  }

  /**
   * Set content from multiple templates
   * @return void
   */
  public function buildFromTemplates()
  {
    $bits = func_get_args();
    $content = "";
    foreach( $bits as $bit )
    {
      if( strpos( $bit, 'skins/' ) === false )
      {
        $bit = 'Views/Templates/' . $bit;
      }
      if( file_exists( $bit ) == true )
      {
        $content .= file_get_contents( $bit );
      }
    }
    $this->page->setContent( $content );
  }

  /**
   * Converts an array of data to tags
   * @param array the data
   * @param string prefix added to field names to create tag names
   * @return void
   */
  public function dataToTags( $data, $prefix )
  {
    foreach( $data as $key => $content )
    {
      $this->page->addTag( $key.$prefix, $content);
    }
  }

  /**
   * Parses and replaces page title
   */
  public function parseTitle()
  {
    $newContent = str_replace('<title>', '<title>'. $this->page->getTitle(), $this->page->getContent() );
    $this->page->setContent( $newContent );
  }

  /**
   * Parses and replaces all tags, tokens and title
   * @return void
   */
  public function parseOutput()
  {
    $this->replaceBits();
    $this->replaceTags();
    $this->parseTitle();
  }

}
?>

We also called the Page object in the templating system, so we need to define it in Registry/objects/page.class.php:

<?php

/**
 * Our Page class
 * This class allows adding several useful things,
 * such as password-protected pages, adding JS/CSS files, etc.
 */
class page {

  private $css = array();
  private $js = array();
  private $bodyTag = '';
  private $bodyTagInsert = '';

  // Future functionality
  private $authorised = true;
  private $password = '';

  // Page elements
  private $title = '';
  private $tags = array();
  private $postParseTags = array();
  private $bits = array();
  private $content = "";

  /**
   * Constructor...
   */
  function __construct() { }

  public function getTitle()
  {
    return $this->title;
  }

  public function setPassword( $password )
  {
    $this->password = $password;
  }

  public function setTitle( $title )
  {
    $this->title = $title;
  }

  public function setContent( $content )
  {
    $this->content = $content;
  }

  public function addTag( $key, $data )
  {
    $this->tags[$key] = $data;
  }

  public function getTags()
  {
    return $this->tags;
  }

  public function addPPTag( $key, $data )
  {
    $this->postParseTags[$key] = $data;
  }

  /**
   * Parse post-processing tags
   * @return array
   */
  public function getPPTags()
  {
    return $this->postParseTags;
  }

  /**
   * Add a template bit to the page
   * @param String the tag where the template is added
   * @param String the template file name
   * @return void
   */
  public function addTemplateBit( $tag, $bit )
  {
    $this->bits[ $tag ] = $bit;
  }

  /**
   * Get all template bits
   * @return array array of template tags and template file names
   */
  public function getBits()
  {
    return $this->bits;
  }

  /**
   * Get a block of content from the page
   * @param String the tag that wraps the block ( <!-- START tag --> block <!-- END tag --> )
   * @return String the content block
   */
  public function getBlock( $tag )
  {
    preg_match ('#<!-- START '. $tag . ' -->(.+?)<!-- END '. $tag . ' -->#si', $this->content, $tor);

    $tor = str_replace ('<!-- START '. $tag . ' -->', "", $tor[0]);
    $tor = str_replace ('<!-- END '  . $tag . ' -->', "", $tor);

    return $tor;
  }

  public function getContent()
  {
    return $this->content;
  }

}
?>

Now that we have created the classes for working with the database and templates, let's connect these classes.

Create the method storeCoreObjects() in Registry/registry.class.php:

    public function storeCoreObjects()
    {
      $this->storeObject('database', 'db' );
      $this->storeObject('template', 'template' );
    }

In this method we define which core classes are being loaded.

Let's also populate some data — specifically, let's create a users table. This table will have three fields: id, name, and email. I’ll include an SQL file with a sample database on GitHub.

Now let's display the homepage. For this, we need to create a template: Views/Templates/main.tpl.php:

<html>
<head>
    <title> Powered by PCA Framework</title>
</head>
<body>
<h1>Our Members</h1>
<p>Below is a list of our members:</p>
<ul>
<!-- START members -->
<li>{name} {email}</li>
<!-- END members -->
</ul>
</body>
</html>

As you can see, we defined the members tag and tokens {name}, {email}. In one of the upcoming articles, I’ll go into more detail about how the templating engine works. Now let's go back to index.php and connect the template and database.

Now our index.php looks like this:

<?php
/**
 * Framework
 * Framework loader - entry point to our framework
 */
  
// Start the session
session_start();

error_reporting(E_ALL);
// Define some constants
// Define the root of the framework for easy access in any script
define( "APP_PATH", dirname( __FILE__ ) ."/" );
// We will use this to prevent scripts from being called outside our framework
define( "FW", true );

/**
 * Magic autoload function
 * Automatically loads the required controller when needed
 * @param String the name of the class
 */
function __autoload( $class_name )
{
    require_once('Controllers/' . $class_name . '/' . $class_name . '.php' );
}

// Load our registry
require_once('Registry/registry.class.php');
$registry = Registry::singleton();

// Store all core objects in the registry
$registry->storeCoreObjects();

// Add your DB credentials here
$registry->getObject('db')->newConnection('localhost', 'root', '', 'framework');

// Load the homepage template
$registry->getObject('template')->buildFromTemplates('main.tpl.php');

// Query the users table
$cache = $registry->getObject('db')->cacheQuery('SELECT * FROM users');

// Add the 'users' tag to be used in the template,
// fields from the users table will be accessible via tokens {name}, {email}
$registry->getObject('template')->getPage()->addTag('users', array('SQL', $cache) );

// Set the page title
$registry->getObject('template')->getPage()->setTitle('Our users');

// Parse the page, process tags and tokens, and display the output
$registry->getObject('template')->parseOutput();
print $registry->getObject('template')->getPage()->getContent();

// Display the name of the framework to verify it’s working
print $registry->getFrameworkName();

exit();
?>

If everything goes well and there are users in the database, you should see something like this:

If something goes wrong and you encounter errors, I may not have yet corrected some code from earlier articles. You can find working code on GitHub.

Here are some errors I encountered while writing this article.

Changed the class name for working with the database in Registry/objects/db.class.php:

Index: Registry/objects/db.class.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- Registry/objects/db.class.php (revision b1ffa3bbfce4e95ace7ed735e9412e9332e17d50)
+++ Registry/objects/db.class.php (revision )
@@ -4,7 +4,7 @@
  * Database Management
  * Provides a small abstraction over the DB
  */
-class database {
+class db {

   /**
    * Allows multiple database connections
\ No newline at end of file

Defined static methods where needed and renamed the database class in Registry/registry.class.php:

Index: Registry/registry.class.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- Registry/registry.class.php (revision b1ffa3bbfce4e95ace7ed735e9412e9332e17d50)
+++ Registry/registry.class.php (revision )
@@ -69,15 +69,15 @@
      * @param String $key the key for the array
      * @return void
      */
-    public function storeObject( $object, $key )
+    public static function storeObject( $object, $key )
     {
-        require_once('objects/' . $object . '.class.php');
+        require_once('Registry/objects/' . $object . '.class.php');
         self::$objects[ $key ] = new $object( self::$instance );
     }

     public function storeCoreObjects()
     {
-      $this->storeObject('database', 'db' );
+      $this->storeObject('db', 'db' );
       $this->storeObject('template', 'template' );
     }

@@ -86,7 +86,7 @@
      * @param String $key the array key
      * @return object
      */
-    public function getObject( $key )
+    public static function getObject( $key )
     {
         if( is_object ( self::$objects[ $key ] ) )
         {
\ No newline at end of file

Had to create a controller db with db.php inside:

Controllers/db/
Controllers/db/db.php

Fixed an error in the template engine in Registry/objects/template.class.php:

Index: Registry/objects/template.class.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- Registry/objects/template.class.php (revision b1ffa3bbfce4e95ace7ed735e9412e9332e17d50)
+++ Registry/objects/template.class.php (revision )
@@ -194,7 +194,7 @@

   public function parseTitle()
   {
-    $newContent = str_replace('<title>', '<title>'. $this->$page->getTitle(), $this->page->getContent() );
+    $newContent = str_replace('<title>', '<title>'. $this->page->getTitle(), $this->page->getContent() );
     $this->page->setContent( $newContent );
   }

\ No newline at end of file