View list sederhana PHP & MSSQL

Pada artikel kali ini saya akan coba memaparkan viewlist sederhana yang terinspirasi dari posting di salah satu millist indonesia yang menanyakan pengaplikasian limit pada query MSSQL Server dengan CodeIgniter. Berhubung saya baru mau belajar juga menggunakan codeIgniter jadi saya hanya coding dengan metode struktural dengan konsep MVC (walau masih belum bisa dibilang MVC). Dari pengalaman beberapa waktu lalu saya juga sempat mengalami kendala untuk paging yang menampilkan data dari MSSQLServer2000. Maka sembari membuka-buka kenangan lama, saya coba merevisi query tersebut sekalian mencoba membuat viewlist dengan fungsi paging lengkap dengan customisasi jumlah record yang akan ditampilkan, sorting berdasarkan field secara asc maupun descending.

Kode yang akan saya paparkan akan dibagi menjadi 3 file ;

1. Data Akses layer class -> dba.php
2. Index  -> index.php
3. View -> view.php

Table yang digunakan adalah “mscontact” berikut query-nya :

CREATE TABLE [dbo].[mscontact](
[idcontact] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nama] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[alamat] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[notelp] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[status] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[email] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[url] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

dba.php

<?php
class dba {
var $jml_record;
var $offset_record;
function dba($hostname,$username,$password,$dbname) {
$conn = mssql_connect($hostname, $username, $password) or DIE(“can’t connect to server”);
if($conn) $db = mssql_select_db($dbname,$conn) or DIE(“the database doesn’t exist”);
$this->num_row = 2;
$this->offset_row = 0;
}
function exeq($query)  {
$data = mssql_fetch_array(mssql_query($query));
return $data;
}
function exequery($query)  {
$query = mssql_query($query);
return $query;
}
function fetcharray($query) {
$query = mssql_query($query);
while($fetch = mssql_fetch_array($query)){
$data[] = $fetch;
}
return $data;
}
function countdata($table,$where=”")  {
$where = $where===”" ? “”:” “.$where;
$data = $this->exeq(“select count(*) from $table $where”);
return $data[0];
}
function fetch_limit_stock($jmlrec, $offrec, $totalrecord, $table, $orderbyfield, $where=”", $ordermethod) {
/**
$totalrecord = jumlah total record yg didapat dari count record
$jmlrec = jumlah record yg akan ditampilkan
$table = nama table
$offrec = record awal limit
$orderbyfield = harus ada isinya
$where = kondisi query
$ordermethod = “arah sorting asc / desc”
/**/
$neg_ordermethod = $ordermethod===”asc”?”desc”:”asc”;
$where = $where===”" ? “” : $where;
$this->jml_record = $jmlrec ; //default nilai jumlah baris, dapat diset melalui nilai parsing
$this->offset_record = $totalrecord – $offrec; //record awal yg akan ditampilkan, variabel ini ditampung pd class dba
$limitq = “select TOP “.$this->jml_record.” tmptable.* from(
select TOP “.$this->offset_record.” tmptab.* from(
select TOP “.$totalrecord.” a.* from $table as a with(nolock) $where order by $orderbyfield $neg_ordermethod)
as tmptab order by tmptab.$orderbyfield $neg_ordermethod)
as tmptable order by tmptable.$orderbyfield $ordermethod;”;
$query = $limitq;
$data = $this->fetcharray($query); //fungsi fetcharray mssql
return $data;
}
function getNum_field($table) { //mendapatkan jumlah field pd table
$exeq = $this->exequery(“select * from “.$table.” with(nolock)”);
$data = mssql_num_fields($exeq);
return $data;
}
function getField_name($table,$n=”") { //untuk mengambil field
$exeq = $this->exequery(“select * from “.$table.” with(nolock)”);
if(($n==”")||($n==0)) $n = $this->getNum_field(“select * from “.$table.” with(nolock)”);
for($i=0;$i<$n;$i++)  {
$data[] = mssql_field_name($exeq,$i);
}
return $data;
}
}
?>

index.php

<?
require_once “dba.php”;
$hostname = “localhost,1879″;
$username = “sa”;
$password = “root”;
$dbname = “testpaging”;
$db = new dba($hostname,$username,$password,$dbname);
$jmlrec = isset($_POST['jumlah_baris']) ? $_POST['jumlah_baris']:5; //5 adalah nilai default
$actpage = isset($_POST['actpage'])?”-2″:$_POST['actpage'];
$table = “mscontact”;
$orderbyfield = isset($_POST['orderby'])?$_POST['orderby']:”idcontact”;
$ordermethod = isset($_POST['order']) ? $_POST['order']:asc; //asc adalah nilai default
$ordermethod = $_POST['clickheader']===”true”?
$ordermethod===”asc”?”desc”:”asc” :$ordermethod;
$totalrecord = $db->countdata(trim($table),trim($where));
$actpage = isset($_POST['actpage']) ? $_POST['actpage']:-2;
$jmlpage = (int)($totalrecord/$jmlrec)+1;
$reqpage = isset($_POST['reqpage'])?$_POST['reqpage']>$jmlpage?$jmlpage:$_POST['reqpage']:1;
$firstpage = 1; $lastpage=$jmlpage;
$nextpage = $reqpage+1>$lastpage?$lastpage:$reqpage+1;
$prevpage = $reqpage-1===0?$firstpage:$reqpage-1;
$offrec =     (int)(($reqpage-1)*$jmlrec);
$data = $totalrecord>0?$db->fetch_limit_stock($jmlrec, $offrec, $totalrecord, $table, $orderbyfield, “”, $ordermethod):
array(“data”=>array(“tidak ada data”,”tidak ada data”,”tidak ada data”,”tidak ada data”,”tidak ada data”,”tidak ada data”));
$numField = 6;
$datafield = $db->getField_Name($table,$numField);
include “view.php”;
?>

dan terakhir file view.php

<style>
th {
cursor:pointer;
background-color:#FF0000;
color:#FFFFFF;
}
</style>
<form name=”testform” method=”POST” action=”index.php”>
<input type=”hidden” id=”orderby” name=”orderby” value=”<?=$orderbyfield?>” /> <!–untuk simpan nilai order by field yg mana !–>
<input type=”hidden” id=”clickheader” name=”clickheader” value=”<?=$clickheader?>” /> <!– untuk handle order klik request dari header field !–>
<hr/>
Tampilkan
<select name=”jumlah_baris” onchange=”document.getElementById(‘clickheader’).value=’false’;document.testform.submit();”>
<option value=”5″ <?=$jmlrec===”5″ ? “selected”:”"?>>5</option>
<option value=”10″ <?=$jmlrec===”10″ ? “selected”:”"?>>10</option>
<option value=”20″ <?=$jmlrec===”20″ ? “selected”:”"?>>20</option>
</select>
| order :
<select name=”order” onchange=”document.getElementById(‘clickheader’).value=’false’;document.testform.submit();”>
<option value=”asc” <?=$ordermethod===”asc” ? “selected”:”"?>>asc</option>
<option value=”desc” <?=$ordermethod===”desc” ? “selected”:”"?>>desc</option>
</select> by <b><?=$orderbyfield?></b>
| ke hal
<select id=”reqpage” name=”reqpage”  onchange=”document.testform.submit();”>
<? for($i=1;$i<=$jmlpage;$i++) : ?>
<option value=”<?=$i?>” <?=$reqpage==$i?”selected”:”"?>><?=$i?></option>
<? endfor ?>
</select> <hr/>
panel paging
<input type=”button” onclick=”document.getElementById(‘reqpage’).value=’<?=$firstpage?>’;document.getElementById(‘clickheader’).value=’false’;document.testform.submit()” name=”first” value=”first” />
<input type=”button” onclick=”document.getElementById(‘reqpage’).value=’<?=$prevpage?>’;document.getElementById(‘clickheader’).value=’false’;document.testform.submit()” name=”prev” value=”prev” />
<input type=”button” onclick=”document.getElementById(‘reqpage’).value=’<?=$nextpage?>’;document.getElementById(‘clickheader’).value=’false’;document.testform.submit()” name=”next” value=”next” />
<input type=”button” onclick=”document.getElementById(‘reqpage’).value=’<?=$lastpage?>’;document.getElementById(‘clickheader’).value=’false’;document.testform.submit()” name=”last” value=”last” />
* klik pada header field untuk sort berdasarkan field
<table border=’1′>
<tr>
<th onclick=”document.getElementById(‘clickheader’).value=’true’;document.getElementById(‘orderby’).value=’idcontact’;document.testform.submit()”>id</th>
<th onclick=”document.getElementById(‘clickheader’).value=’true’;document.getElementById(‘orderby’).value=’nama’;document.testform.submit()”>nama</th>
<th onclick=”document.getElementById(‘clickheader’).value=’true’;document.getElementById(‘orderby’).value=’alamat’;document.testform.submit()”>alamat</th>
<th onclick=”document.getElementById(‘clickheader’).value=’true’;document.getElementById(‘orderby’).value=’notelp’;document.testform.submit()”>notelp</th>
<th onclick=”document.getElementById(‘clickheader’).value=’true’;document.getElementById(‘orderby’).value=’status’;document.testform.submit()”>status</th>
<th onclick=”document.getElementById(‘clickheader’).value=’true’;document.getElementById(‘orderby’).value=’email’;document.testform.submit()”>email</th>
<tr>
<?php foreach ($data as $val): ?>
<tr>
<td><?=$val[0]?></td>
<td><?=$val[1]?></td>
<td><?=$val[2]?></td>
<td><?=$val[3]?></td>
<td><?=$val[4]?></td>
<td><?=$val[5]?></td>
</tr>
<?php endforeach?>
</table>
panel paging
<input type=”button” onclick=”document.getElementById(‘reqpage’).value=’<?=$firstpage?>’;document.getElementById(‘clickheader’).value=’false’;document.testform.submit()” name=”first” value=”first” />
<input type=”button” onclick=”document.getElementById(‘reqpage’).value=’<?=$prevpage?>’;document.getElementById(‘clickheader’).value=’false’;document.testform.submit()” name=”prev” value=”prev” />
<input type=”button” onclick=”document.getElementById(‘reqpage’).value=’<?=$nextpage?>’;document.getElementById(‘clickheader’).value=’false’;document.testform.submit()” name=”next” value=”next” />
<input type=”button” onclick=”document.getElementById(‘reqpage’).value=’<?=$lastpage?>’;document.getElementById(‘clickheader’).value=’false’;document.testform.submit()” name=”last” value=”last” />
</form>
<hr/>
order oleh : <b><?=$orderbyfield?></b>, secara : <b><?=$ordermethod?></b>, halaman : <b><?=$reqpage?></b>

Presentation layer membutuhkan Javascript-enabled untuk dapat berfungsi sepenuhnya

yah silahkan mencoba.

Hasil Tampilan layar :