DesignPatternStep4

Main Page >> Advanced Web Technologies >> Workbook >> Week 04 >> Step 4 - Accessing the database

Step 4 - Accessing the database

And by “doing stuff”, we obviously mean “get data from the database”!

First let's create our Manager class. It contains all the core database functionality: connecting to the server, parsing SQL queries, that sort of things.

It is defined as “abstract”, because it is not meant to be used directly. It serves as a base for more specialized Manager classes to be built on.

Anyway, here is the code:

// Manager.php

require_once("Registry.php");

//############################################################################
// Manager class
//############################################################################
abstract class Manager
{
  static $DB;
  static $stmts = array();
  
  //############################################################################
  // Constructor
  //############################################################################   
  function __construct()
  {
    // Get DB details from registry
    $database_dsn = Registry::get("database_dsn");
    $database_login = Registry::get("database_login");
    $database_password = Registry::get("database_password");
    
    // Create connection
    $pdo = new PDO($database_dsn, $database_login, $database_password);
    self::$DB = $pdo;

    // Set connection attributes    
    self::$DB->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  }

  //############################################################################
  // prepareStatement: prepares anc caches SQL statement
  //############################################################################   
  function prepareStatement($stmt_s)
  {
    // Check if statement is already in cache
    if(isset(self::$stmts[$stmt_s]))
    {
      return self::$stmts[$stmt_s];
    }
    
    // Prepare stement, store in cache and return
    $stmt_handle = self::$DB->prepare($stmt_s);
    self::$stmts[$stmt_s] = $stmt_handle;
    return $stmt_handle;
  }
  
  //############################################################################
  // doStatement: runs SQL statement
  //############################################################################   
  protected function doStatement($stmt_s, $values_a)
  {
    $sth = $this->prepareStatement($stmt_s);
    
    // Closes the cursor, enabling the statement to be executed again.
    $sth->closeCursor();
    
    // Runs the statement
    $db_result = $sth->execute($values_a);
    return $sth;
  }
}

Next, let’s create our Manager class responsible for handling our blog messages. It is called ManagerMessage and “extends” our standard Manager class. It defines the SQL queries needed to handle messages, and adds functions such as getAllMessages and addMessage.

The code:

// ManagerMessage.php

require_once("Manager.php");

//############################################################################
// ManagerMessage class
//############################################################################
class ManagerMessage extends Manager
{
  static $add_message = "INSERT INTO messages(title,message,date_added) values(?,?,?)";
  static $list_messages = "SELECT id,title,message,date_added FROM messages ORDER BY date_added DESC";
  static $search_messages = "SELECT id,title,message,date_added FROM messages WHERE title LIKE ? ORDER BY date_added DESC";
  static $delete_message = "DELETE FROM messages WHERE id = ?";
  
  //############################################################################
  // addMessage: adds a message to the database
  //############################################################################ 
  function addMessage($title, $message)
  {
    $values = array($title, $message, date("Y/m/d H:i:s"));
    $this->doStatement(self::$add_message, $values);
  }
  
  //############################################################################
  // getAllMessages: selects all messages from the database
  //############################################################################ 
  function getAllMessages()
  {
    $stmt = $this->doStatement(self::$list_messages, null);
    $result = $stmt->fetchAll();
    return $result;
  }
  
  //############################################################################
  // searchMessages: searches for a message
  //############################################################################ 
  function searchMessages($keywords)
  {
    $values = array($keywords);
    $stmt = $this->doStatement(self::$search_messages, $values);
    $result = $stmt->fetchAll();
    return $result;
  }
  
  //############################################################################
  // deleteMessage: deletes a message
  //############################################################################ 
  function deleteMessage($id)
  {
    $values = array($id);
    $stmt = $this->doStatement(self::$delete_message, $values);
  }  
  
}

Now we can implement the doExecute function in our command (Command_BlogIndex) so it calls our newly written ManagerMessage class.

Amend the doExecute() function as follow:

// Command_BlogIndex.php

  //############################################################################
  // doExecute
  //############################################################################ 
  function doExecute(Request $request)
  {
    // Create manager object
    $manager = new ManagerMessage();

    // Get data from database object
    $data = $manager->getAllMessages();
      
    // Dump data array to screen
    print_r($data);
  }

Note: We are now referring to the ManagerMessage class, so you will need this line near the top:

require_once("ManagerMessage.php");

Checkpoint

Your website should now be displaying the data from the database in the form an an array:

Array ( [0] => Array ( [id] => 18 [0] => 18 [title] => Test 2 [1] => Test 2 [message] => Hiya [2] => Hiya [date_added] => 2009-07-27 15:06:38 [3] => 2009-07-27 15:06:38 ) [1] => Array ( [id] => 17 [0] => 17 [title] => Test 1 [1] => Test 1 [message] => Hello world! [2] => Hello world! [date_added] => 2009-07-27 15:06:29 [3] => 2009-07-27 15:06:29 ) )

We're nearly there!

>> Onto Step 5

Errors?

If you get this message:

Uncaught exception 'PDOException' with message 
'SQLSTATE[28000] [1045] Access denied for user 'YOURUSER'@'localhost'

Then you have forgotten to specify your own database credentials in the Controller.php file.