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;
            $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){
 $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';

 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() {
        //load helper jika tidak di set di config
       // $this->load->helper('url','form','html');

function index($no_page = 1){
     $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);     

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

        <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>

        <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; ?>
          Halaman <?php echo $no_page; ?>
        <table class="easyui-datagrid" title="PT Wavin"  >
            <thead id="headtable">
                    <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>
            <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>
                <?php } ?>



Hope can help you guys , thanks

By Freelance Inhouse Trainer
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


Post a Comment

Silahkan isikan comment box untuk komentar Anda..