Kohana PHP 3.0 (KO3) Tutorial Part 4

Posted by ellisgl on February 1, 2010

Welcome to the fourth part in this series on how to develop with Kohana PHP V3 (KO3). If you haven’t read any of previous parts yet, I would click here and read them before going on. In this tutorial we will be going over how work with models.

So you might be asking your self, what is a model. From Kohana’s 2.x documents:

Models are classes designed to work with information given by or asked for by the controller. For example, you have a guestbook, the controller will ask the model to retrieve the last ten entries, the model returns those entries to the controller who passes them on to a view. The controller might also send new entries to the model, update existing ones or even delete some.

Simply a model is a data handler and manipulator.

The first thing we want to do is identify where and what the data is. Is it an XML feed, CSV, JSON, DB or something else? Well I’m going to make it easy. We are going to deal with our friend MySQL for this. The next step is to setup a MySQL DB connection.

Lets open up your bootstrap file (”application/bootstrap.php”) and find the like that reads “// ‘database’ => MODPATH.’database’, // Database access” and uncomment it. The whole block of code should look like the following:

Kohana::modules(array(
	// 'auth'       => MODPATH.'auth',       // Basic authentication
	// 'codebench'  => MODPATH.'codebench',  // Benchmarking tool
	 'database'   => MODPATH.'database',   // Database access
	// 'image'      => MODPATH.'image',      // Image manipulation
	// 'orm'        => MODPATH.'orm',        // Object Relationship Mapping
	// 'pagination' => MODPATH.'pagination', // Paging of results
	// 'userguide'  => MODPATH.'userguide',  // User guide and API documentation
	));

Now save this. We’ve basically told the bootstrap to load the database module, but we need to configure it. Copy the “database.php” from “modules/database/config/” to “application/config/”. Open up the “application/config/database.php” file and edit accordingly to your settings. Mine looks like this:

<?php defined('SYSPATH') OR die('No direct access allowed.');

return array
(
	'default' => array
	(
		'type'       => 'mysql',
		'connection' => array(
			/**
			 * The following options are available for MySQL:
			 *
			 * string   hostname
			 * integer  port
			 * string   socket
			 * string   username
			 * string   password
			 * boolean  persistent
			 * string   database
			 */
			'hostname'   => 'localhost',
			'username'   => 'root',
			'password'   => FALSE,
			'persistent' => FALSE,
			'database'   => 'mykohana3',
		),
		'table_prefix' => '',
		'charset'      => 'utf8',
		'caching'      => FALSE,
		'profiling'    => TRUE,
	),
	'alternate' => array(
		'type'       => 'pdo',
		'connection' => array(
			/**
			 * The following options are available for PDO:
			 *
			 * string   dsn
			 * string   username
			 * string   password
			 * boolean  persistent
			 * string   identifier
			 */
			'dsn'        => 'mysql:host=localhost;dbname=mykohana3',
			'username'   => 'root',
			'password'   => FALSE,
			'persistent' => FALSE,
		),
		'table_prefix' => '',
		'charset'      => 'utf8',
		'caching'      => FALSE,
		'profiling'    => TRUE,
	),
);

Save this. You’ll notice I have a database setup just this tutorial series named “mykohana3″, you might want to do the same if you can. Now that we have saved, let get a table set up. Here’s the SQL:

CREATE TABLE `posts` (
  `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) DEFAULT NULL,
  `post` TEXT,
  PRIMARY KEY  (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

Go run that in your favorite MySQL client, I personally like SQLYog. You might have noticed the “charset” is set to “utf8″ in both the config and create table statement. This will allow us to deal with i18n (Internationalization) stuff later on.

So let create a new folder under “application/classes” named “model”. Now lets create a new file and make it look like this:

<?php
defined('SYSPATH') or die('No direct script access.');

class Model_Post extends Kohana_Model
 {
    /**
     * Get the last 10 posts
     * @return ARRAY
     */
    public function getLastTenPosts()
     {
        $sql = 'SELECT *'."\n".
               'FROM   `posts`'."\n".
               'ORDER BY `id` DESC'."\n".
               'LIMIT  0, 10';

        return $this->_db->query(Database::SELECT, $sql, FALSE)
                         ->as_array();
     }
 }

Save this as “post.php” under “application/classes/model/”. Here’s an line by line explaination of the code.

        $sql = 'SELECT *'."\n".
               'FROM   `posts`'."\n".
               'ORDER BY `id` DESC'."\n".
               'LIMIT  0, 10';

This is a basic MySQL statement that selects up to ten rows from the DB which are sorted by the ‘id’ in a descending direction.

        return $this->_db->query(Database::SELECT, $sql, FALSE)
                         ->as_array();

This return array of the result from a query. The “query” method in this example take 3 parameters. 1st being what type of query, our being select, we use the constant “Database::SELECT”. There are 3 others, “Database::INSERT”, “Database::UPDATE” and “Database::DELETE”. The “as_array()” will return an array of the results, no having to do “while($row = mysql_fetch_array())”.

Now that we have a model method, I’m sure we would want to put it use. Open up “ko3.php” in “/application/classes/controller” and lets add this into the class:

    public function action_posts()
     {
        $posts                            = new Model_Post();
        $ko3                              = array();
        $this->template->title            = 'Kohana 3.0 Model Test';
        $this->template->meta_keywords    = 'PHP, Kohana, KO3, Framework, Model';
        $this->template->meta_description = 'A test of of the KO3 framework Model';
        $this->template->styles           = array();
        $this->template->scripts          = array();

        // Get the last 10 posts
        $ko3['posts']                     = $posts->getLastTenPosts();
        $this->template->content          = View::factory('pages/posts', $ko3);

     }

Basically we’ve called our model’s “getLastTenPosts()” method and assigned it to an array which we pass to our view. Talking about views, open up a new file and put the following into it:

<?php foreach($posts as $post):?>
  <h1><?php echo $post['title'];?></h1>
  <?php echo $post['post'];?>
 <hr />
<?php endforeach;?>

Save to as “posts.php” under “application/views/pages/”. This view loops through the array we pass to it from the the controller and displays our posts from the DB. Wait, what? There’s no posts in our DB! Here’s some SQL you can run to populate your table:

insert  into `posts`(`id`,`title`,`post`) values (1,'Test Post','This is some sample text.');
insert  into `posts`(`id`,`title`,`post`) values (2,'Another post','Some more text');

Now if you point your browser to “http://yourserver/mykohana3/ko3/posts” you should see the two entries on the screen.

Now lets add something to put data into our DB. Open the the post model (”application/classes/model/post.php”) and add this to the class:

    public function addPost($title, $post)
     {
       $sql = sprintf('INSERT INTO `posts`'."\n".
                      'SET         `title` = %s,'."\n".
                      '            `post`  = %s',
                       $this->_db->escape($title),
                       $this->_db->escape($post));

       $this->_db->query(Database::INSERT, $sql, FALSE);
    }

The above is a pretty simple insert, but you may notice we are using “$this->_db>escape()”. This will wrap your strings in quotes and escape it content for you. Save it and now go back to the “posts.php” from “application/views/pages” and add the following to the bottom:

<form method="POST" action="">
  <table>
    <tr>
     <td>
       Title
     </td>
     <td>
       <input type="text" name="title" style="border: 1px solid #000000;"/>
     </td>
    </tr>
    <tr>
     <td>
      Post
     </td>
     <td>
      <textarea cols="20" rows="5" name="post"></textarea>
      <input type="submit" name="submit" value="Submit"/>
     </td>
  </table>
</form>

Save that and open the ko3 controller back up (”application/classes/controller/ko3.php”) and lets add a new method to it.

    private function _addPost($title, $post_content)
     {
       // Load model
       $post = new Model_Post();

       // Check required fields
       if(empty($title))
        {
           return(array('error' => 'Please enter a title.'));
        }
       elseif(empty($post_content))
        {
           return(array('error' => 'Please enter a post.'));
        }

       // Add to DB
       $post->addPost($title, $post_content);
       return TRUE;
     }

The above code is pretty much a middle man between the “action_posts” and the model that saves the post it self. Lets go back to the “action_posts” method and make it look like this:

   public function action_posts()
     {
        // Load model
        $posts                            = new Model_Post();

        // Setup view stuff
        $ko3                              = array();
        $this->template->title            = 'Kohana 3.0 Model Test';
        $this->template->meta_keywords    = 'PHP, Kohana, KO3, Framework, Model';
        $this->template->meta_description = 'A test of of the KO3 framework Model';
        $this->template->styles           = array();
        $this->template->scripts          = array();
        $ko3['msg']                       = "";

        // Handle POST
        if($_POST)
         {
            $ret = $this->_addPost((isset($_POST['title']) ? $_POST['title'] : ""),
                                  (isset($_POST['post']) ? $_POST['post']  : ""));

            if(isset($ret['error']))
             {
                $ko3['msg'] = $ret['error'];
             }
            else
             {
                $ko3['msg'] = 'Saved.';
             }
         }

        // Get the last 10 posts
        $ko3['posts']                     = $posts->getLastTenPosts();

        // Display it.
        $this->template->content          = View::factory('pages/posts', $ko3);
     }

Save this and reload your browser. Now you should see a pretty ugly form at bottom. Enter some stuff in and click the “submit” button. You post should appear at the top along with the word “Saved”, this is if you entered stuff in both fields, if not you should see an error.

Before I end this tutorial, I want to go back to our little model for saving our posts. There are several ways to do queries in KO3, but want to quickly show you how you can use the query builder to do the same thing.

    public function addPost($title, $post)
     {
        DB::insert('posts', array('title','post'))
          ->values(array($title, $post))
          ->execute();
     }

That’s pretty simple! It does the same thing as the previous one, with less “hassle” There are advantages using the query builder method, like being able to convert between different DB types (MySQL to Oracle to what ever).

While I might not have gone over doing updates with your model, I thought I would give you a homework assignment to see if you can come up with you own update model for this. Until next time when we go over “H” in “HMVC”, happy coding.

Sources used: Unofficial Kohana 3 Wiki, Kohana PHP 2.x Docs, KO3 API Guide

Get the file for this tutorial here