CodeIgniter SQL Server Pagination - Solved ~ TRAINER ITIL | COBIT | POWER BI | ISO 27001 CISA | COGNOS | MS PROJECT | CISM | CISSP

Wednesday, March 18, 2015

CodeIgniter SQL Server Pagination - Solved

March 18, 2015 No comments

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

TRAINER ITIL | COBIT | POWER BI | ISO 27001 CISA | COGNOS | MS PROJECT | CISM | CISSP | CRISC

Wednesday, March 18, 2015