Jump to content


Join table works but number records on the paging the same table before join..

join table

  • Please log in to reply
1 reply to this topic

#1 Anto

Anto

    Member

  • Members
  • PipPip
  • 12 posts
  • LocationJambi, Indonesia

Posted 15 June 2012 - 08:49 AM

hello, i'm still new at CI and Grocery crud . right now i'm trying to make a report from grocery crud.(report as report table like crystal report )
so i unset the delete, add, and update function, it's just for viewing functional
and so i join some table, the table works as the record is exactly the same as i query on mssql. but
the records found in the paging is still the same like the table before i use join table.
here's the code i use :

the controller :
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class GuestIn extends CI_Controller {

	function __construct()
	{
		parent::__construct();

		/* Standard Libraries of codeigniter are required */
		$this->load->database();
		$this->load->helper('url');
		/* ------------------ */

		$this->load->library('grocery_CRUD');

	}


	Public function index()
	{
		$this->GuestInHouse();
	}
	

	

	Public function GuestInHouse()
	{
		$this->grocery_crud->unset_delete();
		$this->grocery_crud->unset_add();
		$this->grocery_crud->unset_edit();
$this->grocery_crud->set_table('tb_Guest');
		$this->grocery_crud->set_model('GuestIn_model');

		$this->grocery_crud->columns('roomNo','roomType','code','building','XBED','BFXD','BFST');
  

		//$this->grocery_crud->set_relation('nationality','tbl_nationality','remark');

			//$crud->display_as('salesRepEmployeeNumber','from Employeer')
			//	 ->display_as('customerName','Name')
			//	 ->display_as('contactLastName','Last Name');
			//$crud->set_subject('Customer');
			//$crud->set_relation('salesRepEmployeeNumber','employees','{lastName} {firstName}');
			
			$output = $this->grocery_crud->render();
			
			$this->_example_output($output);
	}
	

	function _example_output($output = null)
	{
		$this->load->view('grid.php',$output);	
	}
}
/* End of file welcome.php */
/* Location: ./application/controllers/welcome.php */


i use the set_model method
here's the model i use :
<?php
class GuestIn_model  extends grocery_CRUD_Model {

		function get_primary_key($table_name = null)
		{
		 if($table_name == null)
		 {
   return 'rsvNo'; // <-------- change this line here
		 }
		 else
		 {
		  $fields = $this->get_field_types($table_name);
		
		  foreach($fields as $field)
		  {
		   if($field->primary_key == 1)
		   {
				return $field->name;
		   }
		  }
		
		  return false;
		 }
		
		}

	function get_list()
	{

		if($this->table_name === null)
			return false;
		
		$select = "{$this->table_name}.*";
		$select .=",tbl_nationality.code,tb_Room.building,vwCountXbed.XBED,vwCountXbed.BFXB,vwCountXbed.BFST";
		// ADD YOUR SELECT FROM JOIN HERE <------------------------------------------------------
		// for example $select .= ", user_log.created_date, user_log.update_date";
		$select .=", ";
		if(!empty($this->relation))
			foreach($this->relation as $relation)
			{
				list($field_name , $related_table , $related_field_title) = $relation;
				$unique_join_name = $this->_unique_join_name($field_name);
				$unique_field_name = $this->_unique_field_name($field_name);
				
				if(strstr($related_field_title,'{'))
					$select .= ", CONCAT('".str_replace(array('{','}'),array("',COALESCE({$unique_join_name}.",", ''),'"),str_replace("'","\\'",$related_field_title))."') as $unique_field_name";
				else				
					$select .= ", $unique_join_name.$related_field_title as $unique_field_name";
				
				if($this->field_exists($related_field_title))
					$select .= ", {$this->table_name}.$related_field_title as '{$this->table_name}.$related_field_title'";
			}
			
		$this->db->select($select, false);
		
		// ADD YOUR JOIN HERE for example: <------------------------------------------------------
		// $this->db->join('user_log','user_log.user_id = users.id');
		$this->db->join('tb_Room','tb_Room.roomNo=tb_Guest.roomNo','inner');
		$this->db->join('tbl_nationality','tbl_nationality.remark=tb_Guest.nationality','inner');
		$this->db->join('vwCountXbed','vwCountXbed.regNo=tb_Guest.regNo','inner');
		
		$results = $this->db->get($this->table_name)->result();
		
		
		return $results;
	}
}

if we select the joined table i found 98 record ,
but the paging found record 17000 record , it's the same number record from the table i set in set_table
so i want the record found on the paging is the same as the record shown.
somehow i found some explanation about this problem on this forum, it says : the model was overrided but the table doesn't follow the overided table from model
Any help is highly appreciated
i'm using the mssql 2005 though.
i'm sorry for my bad english :D ...

#2 Anto

Anto

    Member

  • Members
  • PipPip
  • 12 posts
  • LocationJambi, Indonesia

Posted 15 June 2012 - 09:52 AM

the funtion get_list i used based on this post http://www.grocerycr...64-join-tables/
i follow the instructions and it works. but the records shows on the pagination doesn't match with the data shown in table. data in table has 98 records and data in pagination records shows 17000 records , one page contains 25 record so after page 4 the table don't have any data . here's the message in the table ( No items to display ) page 5 until page 700 only contains that message.
i'm sorry for my double post . just trying to make my question clearer
anyway nice CRUD ! :)





Also tagged with one or more of these keywords: join table

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users