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

Hery Purnama – 081.223344.506 –
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT

Author: Inhouse Trainer freelance

Hery Purnama 081.223344.506 - Freelance Trainer , Excel VBA Macro, Android, Phonegap, ITIL, COBIT, Google Map API, Sketchup3D , MS Project, Sencha ExtJS, Oracle , MySQL DBA, MS. Access VBA, Primavera P6, MS Project ,Jquery, Yii, CodeIgniter. laravel, CATIA 3D, Unity 5 , PMBOK, PMO, TOGAF, PRINCE2 - Call : 081.223344.506 | PinBB : 7DC633AA| Email / YM : | FB : inhousetrainer | TW : inhousetrainer

Leave a Reply

Your email address will not be published. Required fields are marked *