Develop new module - Part 4 - Connect database

From TomatoCMS Documentation

Jump to: navigation, search

At this step we will connect database and display data on our page.

Step 1: Retrieve data

At model section, we implement most of operations with database.
Assume that we have a table t_contact with its fields are: contact_id, first_name, last_name, tel, address, email.
First, create file Contact.php inside model folder with content like this: 

<?php 
class Tomato_Modules_Contact_Models_Contact extends Tomato_Core_Model_Entity
{
  protected $_properties = array (
          'contact_id' => null,
          'first_name' => null,
          'last_name'  => null,
          'tel'        => null,
          'address'    => null,
          'email'      => null
  );
}

This file describe properties ( or fields) of this table. You can see this class extend from Tomato_Core_Model_Entity, this is a already library which we was created and ready to use.

To use difference database, you must create difference folder for corresponding database as 

TomatoCMS_Root_Folder
|___app
    |___modules
        |___contact
	    |__models
		|__dao
		   |__mysql
		   |__pdo
		   |	|__mysql
		   |__pgsql
		   |__sqlsrv

Inside each of folder, we add Contact.php file to query data.
With mysql database, insert following code 

class Tomato_Modules_Contact_Models_Dao_Mysql_Contact
	extends Tomato_Core_Model_Dao
	implements Tomato_Modules_Contact_Models_Interface_Contact
{
	public function convert($entity) 
	{
		return new Tomato_Modules_Contact_Models_Contact($entity); 
	}
        public function getContacts()
        {
          $sql = sprintf("SELECT * FROM ".$this->_prefix."contact 
						order('contact_id DESC')");
		$rs = mysql_query($sql);
		mysql_free_result($rs);
		return $rs;
  }
}

With pdo_mysql database, insert following code 

class Tomato_Modules_Contact_Models_Dao_Pdo_Mysql_Contact
	extends Tomato_Core_Model_Dao
	implements Tomato_Modules_Contact_Models_Interface_Contact
{
	public function convert($entity) 
	{
		return new Tomato_Modules_Contact_Models_Contact($entity); 
	}
        public function getContacts()
        {
          $select = $this->_conn
            ->select()
            ->from($this->_prefix.'contact')
            ->order('contact_id DESC');
          $rs = $select->query()->fetchAll(); 
          return new Tomato_Core_Model_RecordSet($rs, $this);
        }
}

With postgreSQL database, insert following code 

class Tomato_Modules_Category_Models_Dao_Pgsql_Category
	extends Tomato_Core_Model_Dao
	implements Tomato_Modules_Category_Models_Interface_Category
{
	public function convert($entity) 
	{
		return new Tomato_Modules_Category_Models_Category($entity); 
	}
        public function getContacts()
        {
           $sql = 'SELECT * FROM '.$this->_prefix.'contact';
           $sql .= ' ORDER BY contact_id DESC';
           $rs = pg_query($sql);
           $rows = array();
           while ($row = pg_fetch_object($rs)) {
                $rows[] = $row;
           }  
           pg_free_result($rs);
           return new Tomato_Core_Model_RecordSet($rows, $this);
        }
}

With SQLServer database, insert following code 

class Tomato_Modules_Category_Models_Dao_Sqlsrv_Category
	extends Tomato_Core_Model_Dao
	implements Tomato_Modules_Category_Models_Interface_Category
{
	public function convert($entity) 
	{
		return new Tomato_Modules_Category_Models_Category($entity); 
	}
        public function getContacts()
        {
          $sql = 'SELECT * FROM '.$this->_prefix.'contact ORDER BY contact_id DESC';
          $stmt = $this->_conn->prepare($sql);
          $stmt->execute();
          $rows = $stmt->fetchAll();
          $stmt->closeCursor();
          return new Tomato_Core_Model_RecordSet($rows, $this);
        }
}

So, how to display this result on our list page.
We will implement at Controller and View.

Step 2: Display data

At controller section, at here, controller is Index and action is list, so insert following code into list action 

<?php
class Contact_IndexController extends Zend_Controller_Action
{
  public function listAction()
  {
    $conn = Tomato_Core_Db_ConnectionFactory::factory()->getMasterConnection();
    $contactDao = Tomato_Core_Model_Dao_Factory::getInstance()->setModule('contact')->getContactDao();
    $contactDao->setDbConnection($conn);
    $contact = $ccontactDao->getContact();
 
    $this->view->assign('contacts', $contact);
  }
}

and at view, insert following code into list.phtml file 

<h1>List of contacts</h1>
 
<style type="text/css">
.dataTable{
  border: 1px solid;
}
.dataTable td{
  border: 1px solid;
  padding: 5px;
}
.dataTable th{
  border: 1px solid;
  padding: 5px;
  background: #000;
  color: #fff;
}
</style>
 
<table class="dataTable">
  <thead>
    <tr>
      <th><?php echo $this->translator('contact_list_ID'); ?></th>
      <th><?php echo $this->translator('contact_list_first_name'); ?></th>
      <th><?php echo $this->translator('contact_list_last_name'); ?></th>
      <th><?php echo $this->translator('contact_list_tel'); ?></th>
      <th><?php echo $this->translator('contact_list_address'); ?></th>
      <th><?php echo $this->translator('contact_list_email'); ?></th>
    </tr>
  </thead>
  <tbody >
    <?php foreach($this->contacts as $contact):?>
    <tr >
      <td><?php echo $contact->contact_id; ?></td>
      <td><?php echo $contact->first_name; ?></td>
      <td><?php echo $contact->last_name; ?></td>
      <td><?php echo $contact->tel; ?></td>
      <td><?php echo $contact->address; ?></td>
      <td><?php echo $contact->email; ?></td>
    </tr>
    <?php endforeach; ?>
  </tbody>
</table>

After that, you need to add following parameters to lang.en_US.ini file 

contact_list_ID = "Contact ID"
contact_list_first_name = "First Name"
contact_list_last_name = "Last Name"
contact_list_tel = "Phone number"
contact_list_address = "Address"
contact_list_email = "Email"

And then, let's look your result
File: create_module_part4_result.png

And now, if you have many record, you want to apportion pages. How do you do?
We also have available library to do this.
First, you have to determine start position, number of records which retrieve data from database and calculate number of pages.
Insert following code into you list action 

$perPage = 3;
$start = ($pageIndex - 1) * $perPage;
$contacts = $gateway->getContacts($start, $perPage);
$numContacts = $gateway->count();

Because of, we just retrieve limited data, so we have to edit sql statement like this
[Pdo_Mysql] 

class Tomato_Modules_Category_Models_Dao_Pdo_Mysql_Category
	extends Tomato_Core_Model_Dao
	implements Tomato_Modules_Category_Models_Interface_Category
{
  ...
  public function getContacts($start = null, $count = null)
  {
    $select = $this->_conn
            ->select()
            ->from($this->_prefix.'contact')
            ->order('contact_id DESC');
    if (is_int($start) && is_int($count)) {
      $select->limit($count, $start);
    }
    $rs = $select->query()->fetchAll(); 
    return new Tomato_Core_Model_RecordSet($rs, $this);
  }
}

[mysql] 

class Tomato_Modules_Category_Models_Dao_Pdo_Mysql_Category
	extends Tomato_Core_Model_Dao
	implements Tomato_Modules_Category_Models_Interface_Category
{
  ...
  public function getContacts($start = null, $count = null)
  {
     $sql = sprintf("SELECT * FROM ".$this->_prefix."contact 
				order('contact_id DESC')");
     if (is_int($start) && is_int($count)) {
		$sql .= "limit ($start, $count)";
     }
     $rs = mysql_query($sql);
     mysql_free_result($rs);
     return $rs;
  }
}

[postgreSQL] 

class Tomato_Modules_Category_Models_Dao_Pdo_Mysql_Category
	extends Tomato_Core_Model_Dao
	implements Tomato_Modules_Category_Models_Interface_Category
{
  ...
  public function getContacts($start = null, $count = null)
  {
     $sql = 'SELECT * FROM '.$this->_prefix.'contact';
     $sql .= ' ORDER BY contact_id DESC';
     if (is_int($start) && is_int($count)) {    
        $sql .= sprintf(' LIMIT %s OFFSET %s', $count, $start);
     }
     $rs = pg_query($sql);
     $rows = array();
     while ($row = pg_fetch_object($rs)) {
        $rows[] = $row;
     }  
     pg_free_result($rs);
     return new Tomato_Core_Model_RecordSet($rows, $this);
  }
}

[sqlserver] 

class Tomato_Modules_Category_Models_Dao_Pdo_Mysql_Category
	extends Tomato_Core_Model_Dao
	implements Tomato_Modules_Category_Models_Interface_Category
{
  ...
  public function getContacts($start = null, $count = null)
  {
      $sql = 'SELECT * FROM '.$this->_prefix.'contact ORDER BY contact_id DESC';
      if (is_int($start) && is_int($count)) {
          $sql = $this->_conn->limit($sql, $count, $start);
      }
      $stmt = $this->_conn->prepare($sql);
      $stmt->execute();
      $rows = $stmt->fetchAll();
      $stmt->closeCursor();
      return new Tomato_Core_Model_RecordSet($rows, $this);
  }
}


To count number of records, we add a function count()
[pdo_mysql] 

class Tomato_Modules_Category_Models_Dao_Pdo_Mysql_Category
	extends Tomato_Core_Model_Dao
	implements Tomato_Modules_Category_Models_Interface_Category
{
  ...
  public function count()
  {
    $select = $this->_conn
          ->select()
          ->from(array('c' => $this->_prefix.'contact'), array('num_contacts' => 'COUNT(*)'))
          ->limit(1);
    $rs = $select->query()->fetch();
 
    return $rs->num_contacts;
  }
}

[mysql] 

class Tomato_Modules_Category_Models_Dao_Pdo_Mysql_Category
	extends Tomato_Core_Model_Dao
	implements Tomato_Modules_Category_Models_Interface_Category
{
  ...
  public function count()
  {
     $sql = sprintf("Select count(*) as num_contacts from ".$this->_prefix."contact limit(1)");
     $rs = mysql_query($sql);
     mysql_free_result($rs);
     return $rs->num_contacts;
  }
}

[postgreSQL] 

class Tomato_Modules_Category_Models_Dao_Pdo_Mysql_Category
	extends Tomato_Core_Model_Dao
	implements Tomato_Modules_Category_Models_Interface_Category
{
  ...
  public function count()
  {
     $sql = 'SELECT COUNT(*) AS num_contacts FROM '.$this->_prefix.'contact';  
     $sql .= ' LIMIT 1';
     $rs = pg_query($sql);
     $row = pg_fetch_object($rs);
     pg_free_result($rs);
     return $row->num_contacts;
  }
}

[SQLServer] 

class Tomato_Modules_Category_Models_Dao_Pdo_Mysql_Category
	extends Tomato_Core_Model_Dao
	implements Tomato_Modules_Category_Models_Interface_Category
{
  ...
  public function count()
  {
     $sql = 'SELECT TOP 1 COUNT(*) AS num_contacts FROM '.$this->_prefix.'contact';
     $stmt = $this->_conn->prepare($sql);
     $stmt->execute();
     $rs = $stmt->fetch();
     $stmt->closeCursor();
     return $rs->num_contacts;
  }
}

Now, data was apportioned into pages.
So, how to display apportion pages on our page and display route of link to page.
To display apportion pages, we insert following code into list controller 

class Contact_IndexController extends Zend_Controller_Action
{
   ...
   public function listAction()
   {
      ...
      $paginator = new Zend_Paginator(new Tomato_Core_Utility_PaginatorAdapter($contacts, $numContacts));
      $paginator->setCurrentPageNumber($pageIndex);
      $paginator->setItemCountPerPage($perPage);
      $this->view->assign('paginator', $paginator);
      $this->view->assign('paginatorOptions', array(
          'path' => $this->view->url(array(), 'contact_index_list'),
          'itemLink' => 'page-%d',
      ));
   }
}

We had available Tomato_Core_Utility_PaginatorAdapter library to apportion pages. Things you have to do are insert your parameter.
Note: path parameter is route which define in contact.ini file 

routes.contact_index_list_pager.type = "Zend_Controller_Router_Route_Regex"
routes.contact_index_list_pager.route = "admin/contact/list/page-(\d+)"
routes.contact_index_list_pager.reverse = "admin/contact/list/page-%d"
routes.contact_index_list_pager.defaults.module = "contact"
routes.contact_index_list_pager.defaults.controller = "Index"
routes.contact_index_list_pager.defaults.action = "list"
routes.contact_index_list_pager.map.1 = "page_index"

What about view? Let's just insert following code into list.phtml file 

<div>
   <?php echo $this->paginator()->slide($this->paginator, $this->paginatorOptions); ?>
</div>

Now, let's refresh your page
File:create_module_part4_paging.png

Retrieved from "http://docs.tomatocms.com/index.php/Develop_new_module_-_Part_4_-_Connect_database"
Personal tools