Lesson 2 - Database and Models

In the previous lesson we created a bunch of files just to show a simple message in the browser. It may seem stupid but all these files will save us a lot of work.

Now it's time to talk database.



Our application will store the news, comments and user data in the database server, and in my case MySQL server. I created an database for the project called 'khn' and an user with access to the database.

Our first table will contain the news:

CREATE TABLE IF NOT EXISTS `news` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `user_name` varchar(100) NOT NULL,
  `title` varchar(255) NOT NULL,
  `body` text NOT NULL,
  `comments` int(11) NOT NULL DEFAULT '0',
  `points` int(11) NOT NULL DEFAULT '1',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

INSERT INTO `news` (`id`, `user_id`, `user_name`, `title`, `body`, `comments`, `points`, `created`) VALUES
(1, 1, 'test 1', 'Test News 1', 'Test Body 1', 0, 1, '2010-10-16 13:45:49'),
(2, 2, 'test 2', 'Test News 2', 'Test Body 2', 0, 2, '2010-10-16 13:45:49');

One table is enough for this lesson. To let Kohana know that we want to connect to a database, we must enable the database module. Open the bootstrap.php file (located in the application directory) and uncomment the following line:

'database'   => MODPATH.'database',   // Database access

Next copy the khn/modules/database/config/database.php file to the application/config/ directory and edit its content according to your configuration. Mine looks like that:

<?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     server hostname, or socket
    * string   database     database name
    * string   username     database username
    * string   password     database password
    * boolean  persistent   use persistent connections?
    *
    * Ports and sockets may be appended to the hostname.
    */
   'hostname'   => 'localhost',
   'database'   => 'khn',
   'username'   => 'adsms',
   'password'   => 'adsms',
   'persistent' => FALSE,
  ),
  'table_prefix' => '',
  'charset'      => 'utf8',
  'caching'      => FALSE,
  'profiling'    => TRUE,
 )
);

We'll create a model called Model_News, which will handle the news:

application/classes/model/news.php

<?php
/**
 * Description of news
 *
 * @author aquilax
 */
class Model_News extends Model{

}
?>

Let's also add a method to get all the news for the home page:

/*
   * Load the news for the first page
   */
  public function loadFirstPageNews($n){
    return DB::select('id', 'title', 'user_id', 'user_name', 'comments', 'points', new Database_Expression('UNIX_TIMESTAMP(created) AS created'))
      ->from('news')
      ->order_by(new DatabaseExpression('(points - 1) / ((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-UNIX_TIMESTAMP(created))/3600 +2)^1.5'), 'DESC')
      ->order_by('created')
      ->limit($n)
      ->execute()
      ->as_array();
  }

The order_by section is taken from this Hacker News comment.

The DatabaseExpression class is used in the order_by function to prevent Kohana from escaping the expression, which breaks the resulting SQL.

I'm using the Query_Builder functions because I feel more comfortable with them. You can try to use the ORM class or raw SQL, which in this case shoult look like that:

SELECT id, title, user_id, user_name, comments, points, UNIX_TIMESTAMP(created)
FROM news
ORDER BY (points - 1) / ((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-UNIX_TIMESTAMP(created))*3600 +2)^1.5 DESC
LIMIT 30

Now we have a function, which returns news for the home page. Let's call it in the home controller's action_index:

application/classes/controller/news

public function action_index(){
    $this->data['news'] = $this->news->loadFirstPageNews(30);
    $this->render();
  }

and change the view to print the news:

application/views/pages/news/index_tpl.php

<?php
if ($news) {
  echo '
';   foreach ($news as $row) {     echo '
'.html::entities($row['title']).'
';   }   echo '
';
}
?>