Tuesday, March 17, 2015

CodeIgniter SQL Server Pagination - Solved

After few days try and error, finally i found the solution for Pagination in CodeIgniter using SQL Server

My Model <?php

class Mwavin extends CI_Model {

   
        /*
     * sample
     */
   
    function semuadata($no_page){
       
        $perpage = 5; // value $perpage sama dengan yg di $config['per_page']
        if($no_page == 1){
            $first = 1;
            $last  = $perpage;
        }else{
            $first = ($no_page - 1) * $perpage + 1;
            $last  = $first + ($perpage -1);
        }
       
        return $this->db->query("WITH CTE AS (
                                        SELECT  a.*, b.namadept,
                                                ROW_NUMBER() OVER (ORDER BY a.idpegawai desc) as RowNumber
                                        FROM tbpegawai a left join tbdept b on a.iddept = b.iddept
                                    )
                                SELECT * FROM CTE WHERE RowNumber BETWEEN $first AND $last")->result_array(); 
    }



function paging($url, $rows = 10, $uri = 3){
 $this->load->library('pagination');
  
 $config['per_page']   = 5;
 $config['base_url']   = site_url($url);
 $config['total_rows']   = $rows;
 $config['use_page_numbers'] = TRUE;
 $config['uri_segment']   = $uri;
 $config['num_links']   = 3;
 $config['next_link']   = 'Berikutnya';
 $config['prev_link']   = 'Sebelumnya';


 $this->pagination->initialize($config);
 return $this->pagination->create_links();
}



function totaldata(){
 $query = $this->db->query("SELECT count(*) as row FROM tbpegawai left join tbdept on tbpegawai.iddept = tbdept.iddept")->row_array();
 return $query['row'];
}
   
My Controller<?php
if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Wavin extends CI_Controller {

  

    public function __construct() {
        parent::__construct();
        //load helper jika tidak di set di config
       // $this->load->helper('url','form','html');
    }

function index($no_page = 1){
     $this->load->model('mwavin');
     $data['page'] = $this->mwavin->paging("wavin/index",$this->mwavin->totaldata(), 3);
     $data['baris'] = $this->mwavin->semuadata($no_page);
     $data['no_page'] = $no_page;
     $this->load->view('home', $data);     
}
   

   
My VIEW
i'm using additional Jquery plugin JeasyUi for my template
<!DOCTYPE html>

<html>
    <head>
        <title>Sample Sql Server Codeigniter</title>
      
<link rel="stylesheet" type="text/css" href="<?php echo base_url(); ?>jeasyui/themes/default/easyui.css">
<link rel="stylesheet" type="text/css" href="<?php echo base_url(); ?>jeasyui/themes/icon.css">
<script type="text/javascript" src="<?php echo base_url(); ?>jeasyui/jquery.min.js"></script>
<script type="text/javascript" src="<?php echo base_url(); ?>jeasyui/jquery.easyui.min.js"></script>

    </head>
    <body>
      
        <div class="easyui-panel" title="DATA PEGAWAI" style="width: 1300px;height: 700px;padding:10px" >
        <div> <h1>DATA PEGAWAI WAVIN</h1></div>
           
            <a href="<?php echo base_url(); ?>">HOME </a>|
            <?php echo anchor('wavin/fntambahpeg','TAMBAH'); ?> | LOGOUT
            <div >
              
         <?php echo $page; ?>
        </div>
        <div>
          Halaman <?php echo $no_page; ?>
        </div>
        <table class="easyui-datagrid" title="PT Wavin"  >
            <thead id="headtable">
                <tr>
                    <th data-options="field:'idpegawai'">ID PEGAWAI </th>
                    <th data-options="field:'nama'" sortable="true">NAMA </th>
                    <th data-options="field:'kota'">KOTA </th>
                    <th data-options="field:'tgllahir'">TGL LAHIR </th>
                    <th data-options="field:'email'">EMAIL </th>
                    <th data-options="field:'gaji'">GAJI </th>
                    <th data-options="field:'foto'">FOTO </th>
                    <th data-options="field:'status'">STATUS </th>
                    <th data-options="field:'detail'">DETAIL PEGAWAI </th>
                    <th data-options="field:'namadept'">NAMA DEPARTEMEN </th>
                    <th data-options="field:'jabatan'">JABATAN </th>
                    <th data-options="field:' '" > Admin </th>
                  
                </tr>
            </thead>
          
            <tbody id="datatable">
                <?php foreach($baris as $row){ ?>
                <tr style="vertical-align: top">
                    <td > <?php echo $row['idpegawai']; ?> </td>
                    <td > <?php echo $row['nama']; ?> </td>
                    <td> <?php echo $row['kota']; ?> </td>
                    <td > <?php echo $row['tgllahir']; ?> </td>
                    <td > <?php echo $row['email']; ?> </td>
                    <td > <?php echo $row['gaji']; ?> </td>
                    <td > <?php echo $row['foto']; ?> </td>
                    <td > <?php echo $row['status']; ?> </td>
                    <td > <?php echo $row['detail']; ?> </td>
                    <td > <?php echo $row['namadept']; ?> </td>
                    <td > <?php echo $row['jabatan']; ?> </td>
                    <td> Edit | Del | <?php echo anchor('wavin/detail/'.$row['idpegawai'],'Detail',array('class'=>'easyui-linkbutton')); ?> </td>
                </tr>
                <?php } ?>
            </tbody>
        </table>

        </div>
 
      

    </body>
</html>


Hope can help you guys , thanks


By Freelance Inhouse Trainer
http://freelance-it-trainer.blogspot.com
Hery Purnama 081.223344.506 , IT trainer , Excel / access VBA Macro, MS Project, Primavera,
PHP Ajax Jquery, Google Map API, Google Sketchup3D, Ubuntu, Sencha ExtJS, YII, Code Igniter,
Oracle, SQL Server
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)