Saturday, October 16, 2010

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 '
'; } ?>

9 comments:

  1. Hello!
    I have problem. I don't know where put this code from this:
    "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();
    }


    "

    ReplyDelete
  2. I want to say I'm working in 3.08. Kohana like you were working.

    I think it's should be in:
    application/classes/model/news.php

    Then I made 2 last steps and I have error:

    http://imageshack.us/photo/my-images/191/41213026.jpg/

    undefinied property.. I think it's this 30

    ReplyDelete
  3. @Lucas the code goe to the news.php model:
    application/classes/model/news.php

    Check the whole model here:
    https://gist.github.com/1376219

    ReplyDelete
  4. Also check the Controller code here: http://code.google.com/p/kohana-tutorial/source/browse/trunk/kohana/knh/application/classes/controller/news.php

    ReplyDelete
  5. Next Error: APPPATH\classes\model\news.php
    ErrorException [ Fatal Error ]: Class 'DatabaseExpression' not found

    http://imageshack.us/f/522/53899541.jpg/

    ReplyDelete
  6. Problem solved .. AquilaX it should be Database_Expression

    ReplyDelete
  7. Another problem..

    http://imageshack.us/photo/my-images/42/23557806.jpg/

    ReplyDelete
  8. problem solved ... I just added new user with all permission and access .. I don't know why It didn't work on basic root without password :) thanks..

    ReplyDelete
  9. Perhaps I missed something somewhere along the way to get the model loaded (I'm using v3.2) but I had to change the action_index() model in the news controller to be:

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

    ReplyDelete