Home > JQGrid, MySQL, Pemrograman Web, PHP, Web Engineering > Membuat CRUD dengan JqGrid di PHP

Membuat CRUD dengan JqGrid di PHP

13 July 2012

Hooooaaaaaaaaaaamm…..baru hibernasi ane om, dah lama hampir 1 tahun ane gak nulis

kali ini ane pengen share pengalaman ane tentang jqgrid. sebelumnya kenalan dulu om ya sama jqgrid

“JqGrid adalah salah satu plugin jquery yang populer. Dalam setiap release terbaru selalu disertakan versi demo yang bisa diinstal di komputer lokal. ” via (http://heru.wordpress.com)

cara install di komputer local ada dsni om. klo demo onlinenya disni. ini Screenshoot nya om

Jqgrid Crud dengan PHP

Jqgrid Crud dengan PHP

Gimana udah kenal kan sekrang sama jQgrid??? Yuuuuk,,,,kita buat contoh CRUD di  jqgrid dengaan PHP. Kita akan buat jqgrid dengan fitur berikut:

1. Top navigasi

2. Custom Button

3. Grouping JqGrid

4. Column Chooser /pengaturan kolom

5. Filter toolbar untuk pencarian cepat

6. custom  Add dan Edit Form menjadi dua  kolom

7. include jquery  datetime picker dan jquery dialog box

8. custom editrules

Pertama : Buat tabel seperti di bawah ini

Kedua buat file accountpage.php

</pre>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link rel="shortcut icon" type="image/png" href="favicon.ico" />
<title></title>
<head>
 <link rel="stylesheet" type="text/css" href="core/jquery/jquery-ui-themes-1.8.6/themes/redmond/jquery.ui.all.css" />
 <link rel="stylesheet" type="text/css" href="js/jqgird/plugins/ui.multiselect.css" />
 <link rel="stylesheet" type="text/css" href="js/jqgird/css/ui.jqgrid.css" />
 <link rel="stylesheet" type="text/css" media="screen" href="core/jquery/themes/base/ui.datepicker.css" />
 <link rel="stylesheet" type="text/css" href="css/content.css">
 <script type="text/javascript" src="js/jquery-1.4.2.min.js"></script>
 <script type="text/javascript" src="js/jquery-ui.min.js"></script>
 <script type="text/javascript" src="js/jqgird/plugins/ui.multiselect.js"></script>
 <script type="text/javascript" src="js/jqgird/src/grid.base.js"></script>
 <script type="text/javascript" src="js/jqgird/src/grid.common.js"></script>
 <script type="text/javascript" src="js/jqgird/src/grid.formedit.js"></script>
 <script type="text/javascript" src="js/jqgird/src/grid.inlinedit.js"></script>
 <script type="text/javascript" src="js/jqgird/src/grid.custom.js"></script>
 <script type="text/javascript" src="js/jqgird/src/jquery.fmatter.js"></script>
 <script type="text/javascript" src="js/jqgird/plugins/jquery.searchFilter.js"></script>
 <script type="text/javascript" src="js/jqgird/src/grid.jqueryui.js"></script>
 <script type="text/javascript" src="js/jqgird/src/i18n/grid.locale-en.js"></script>
 <script src="js/jqgird/jquery.jqGrid.src.js" type="text/javascript"></script>
 <script src="js/jqgird/jquery.jqGrid.min.js" type="text/javascript"></script>
 <script src="js/jquery-ui-1.8.2.custom.min.js" type="text/javascript"></script>
 <script src="core/jquery/ui/ui.datepicker.js" type="text/javascript"></script>
 <script src="js/jquery-ui-timepicker-addon.js" type="text/javascript"></script>
 <script src="js/scripts.js" type="text/javascript"></script>
 <style type="text/css">
 .wrap {width: 100%; margin: 0 auto;}
 .ui-timepicker-div .ui-widget-header{ margin-bottom: 8px; }
 .ui-timepicker-div dl{ text-align: left; }
 .ui-timepicker-div dl dt{ height: 25px; }
 .ui-timepicker-div dl dd{ margin: -25px 0 10px 65px; }
 .ui-timepicker-div td { font-size: 70%; }
 </style>
</head>
<body>
<div id="childMenu" style="margin:5px 0 5px 10px">
 Grup Berdasarkan
 <select id="chngroup">
 <option value="name">Nama</option>
 <option value="level">Level</option>
 <option value="date_str">Tgl. Login</option>
 <option value="clear" selected="selected">Hapus Gruping</option>
 </select>
 <input type="hidden" value="<? echo $_SESSION[id];?>" name="user" id="user" />
</div>
<div id="childContenet">
 <table id="tbl_view" cellpadding="0" cellspacing="0" align="center"></table>
 <div id="pager" style="text-align:center;"></div>
 <div id="usernameDialog">
 <span id="dialog"></span>
 </div>
</div>
</body>
</html>
<pre>

Buat file scripts.js. File ini berfungsi untuk menampilkan JQgrid

</pre>
//pengecekan minimal karakter
function mycheck(value) {
if (value.length<5){
 return [false,"Password: Panjang Huruf harus lebih dari atau sama dengan 5",""];
 } else{
 return [true,"",""];
 }
}

var lastsel2;

$(document).ready(function($){
 var colModel, i, cmi, skip = 0, ths, $tr;
 var user = $("#user").val();
 var grid = $("#tbl_view");
 var mygrid = $("#tbl_view").jqGrid({
 url:'inc/accountdata.php?q=1',
 datatype: "json",
 colNames:['id','Nama','Alamat','Phone','Email','Username','Password','Level','Login Terakhir'],
 colModel:[
 {
 name:'id',
 index:'id',
 width:20,
 search:false,
 sortable: false,
 editable:true,
 hidden:true,
 },{
 name:'name',
 index:'name',
 width:120,
 sortable:true,
 editable: true,
 editrules : {required:true},
 },{
 name:'address',
 index:'address',
 width:120,
 sortable:true,
 editable: true,
 editrules : {required:true},
 },{
 name:'phone',
 index:'phone',
 width:100,
 sortable:true,
 editable: true,
 editrules:{number:true},
 },{
 name:'email',
 index:'email',
 width:150,
 sortable:true,
 editable: true,
 },{
 name:'username',
 index:'username',
 width:100,
 sortable:true,
 editable: true,
 editoptions : {
 editoptions : {minlength: 5},
 dataEvents: [{
 type: 'change',
 fn: function(e) {
 var value = $("#username").val();
 $.ajax({
 url: "inc/cek_username.php?username="+ value,
 dataType: "html",
 success: function(data) {
 if ((data == 1)) {
 var tinggi = 80;
 showUsernameDialog("Username sudah ada!",tinggi)
 }
 }
 });
 }
 }]
 },
 editrules:{required:true,minValue:3},
 },{
 name:'password',
 index:'password',
 width:100,
 edittype:'password',
 editable:true,
 sortable:false,
 editoptions : {minlength: 5},
 editrules:{custom:true,custom_func:mycheck},
 },{
 name:'level',
 index:'level',
 width:80,
 align: 'center',
 editable: true,
 edittype:"select",
 editoptions:{value:"Admin:Admin;SPV:SPV"},
 stype: "select",
 searchoptions: { value: ":All;Admin:Admin;SPV:SPV" },
 },{
 name: 'date_str',
 index: 'date_str',
 width: 120,
 align: 'center',
editable: true,
 editrules:{required:true},
 editoptions: {
 size:25,
 dataInit:function(elem){
 setTimeout(function(){ $(elem).datetimepicker({
 showSecond: true,
 dateFormat:'dd-M-yy',
 timeFormat: 'hh:mm:ss',
 stepMinute: 5,
 stepSecond: 10,
 ampm: true,
 hourGrid: 5,
 minuteGrid: 10,
 secondGrid:10,
 });
 }, 10);
 }
 },
 sorttype:'date',
 searchoptions:{
 sopt:['bw', 'cn'],
 dataInit:function(el){$(el).datepicker({dateFormat:"yy-mm-dd"});
 }
 },
 formatter:'date',
 formatoptions:{ srcformat:'Y-m-d h:i:s', newformat:'d-M-Y h:i:s' },datefmt: "Y-m-d h:i:s",
 }
 ],
 mtype:'POST',
 pager: '#pager',
 rownumbers:true,
 altRows: false,
 rowNum:10,
 rowTotal: 999999,
 height: "100%",
 autowidth: true,
 sortorder:'desc',
 rowList:[10,20,30,40,50,100],
 editurl: "inc/accountcrud.php?user="+ user,
 sortname: "id",
 toppager:true,
 cloneToTop:true,
 ignoreCase:true,
 shrinkToFit: false,
 search:true,
 viewrecords: true,
 toolbar: [false, "top"], //you can also use bottom/both to place toolbar on bottom or on both positions
 multiselect: true,
 sortorder: "desc",
 sortable:true,
 loadonce:true,
 reloadAfterSubmit: true,
 caption:"&nbsp;&nbsp;&nbsp;Data Administrator",
 grouping: true,
})

$("#tbl_view").jqGrid('gridResize','');

function aftersavefuncz() {
 $("#tbl_view").setGridParam({
 page:1,
 datatype:'json',
 multiselect: true,
 loadonce:false,
 reloadAfterSubmit:true
 }).trigger('reloadGrid');
 $("#tbl_view").setGridParam({loadonce:true});
}

//Fungsi umtuk memecah form edit jadi 2 kolom(biar tidak memanjang kebawah)
var splitFormatted = false;
function SplitFormatForm(tableName, add) {
 if (!splitFormatted) {
 splitFormatted = true;
 $("#FrmGrid_" + tableName).append('<table><tr><td valign="top"><table id="TblGrid_' + tableName + '_A" border="0" cellSpacing="0" cellPadding="0" /></td><td valign="top"><table id="TblGrid_' + tableName + '_B" border="0" cellSpacing="0" cellPadding="0" /></td></tr></table>');

var cc = $("#TblGrid_" + tableName + "> tbody").children("tr").length;
 var s = (cc / 2) - 1;

var x = $("#TblGrid_" + tableName + "> tbody").children("tr");
 var i = 0;
 x.each(function (index) {
 var e = $(this).clone();
 var oldID = e.attr("id") + "";
 var newID = oldID;
 if (oldID.substring(0, 3) === "tr_") {
 newID = "clone_" + oldID;
 $(this).css("display", "none");
 e.change(function () { $("#" + oldID + " > .DataTD > .FormElement").val($("#" + newID + " > .DataTD > .FormElement").val()); });
 e.attr("id", newID);

if (i++ < s) {
 $("#TblGrid_" + tableName + "_A").append(e);
 }
 else {
 $("#TblGrid_" + tableName + "_B").append(e);
 }
 }
 });

//This hack makes the popup work the first time too
 //$(".ui-icon-closethick").trigger('click');
 var sel_id = "'new'";
 if (!add) {
 sel_id = $('#'+tableName).jqGrid('getGridParam', 'selrow');
 }

$('#'+tableName).jqGrid('editGridRow', sel_id, { closeAfterEdit: true, width: 800,reloadAfterSubmit:true });
 //$('#'+tableName).jqGrid('editRow',sel_id,true,'','','','','');
}}

//custom form edit
$.jgrid.edit = {
 addCaption: "Tambah Data",
 editCaption: "Ubah Data",
 bSubmit: "Simpan",
 bCancel: "Batal",
 processData: "Processing...",
 width : 470,
 msg: {
 required:"Tidak Boleh Kosong!",
 number:"Masukkan Angka dengan Benar!",
 minLength:"Panjang Huruf harus lebih dari atau sama dengan ",
 maxLength:"Panjang Huruf harus kurang dari atau sama dengan",
 },
 onclickSubmit: function(eparams){
 $("#tbl_view").setGridParam({datatype:'json', multiselect: true, loadonce:false, reloadAfterSubmit:true});
 $("#tbl_view").setGridParam({loadonce:true});
 }
};

//custom form tambah
$.jgrid.add = {
 addCaption: "Tambah Data",
 editCaption: "Ubah Data",
 bSubmit: "Simpan",
 bCancel: "Batal",
 processData: "Processing...",
 width : 470,
 msg: {
 required:"Tidak Boleh Kosong!",
 number:"Masukkan Angka dengan Benar!",
 minLength:"Panjang Huruf harus lebih dari atau sama dengan ",
 maxLength:"Panjang Huruf harus kurang dari atau sama dengan",
 },
 onclickSubmit: function(eparams){
 $("#tbl_view").setGridParam({datatype:'json', multiselect: true, loadonce:false, reloadAfterSubmit:true});
 $("#tbl_view").setGridParam({loadonce:true});
 }
};

$.jgrid.nav = {
 edittext: "Ubah",
 edittitle: "Ubah Data",
 addtext:"Tambah",
 addtitle: "Tambah Data Baru",
 deltext: "Hapus",
 deltitle: "Hapus Data",
 searchtext: "Pencarian",
 searchtitle: "Pencarian Data",
 refreshtext: "",
 refreshtitle: "Muat Ulang Halaman",
 alertcap: "Warning",
 alerttext: "Please, pilih salah satu data",
 viewtext: "Lihat",
 viewtitle: "View Data Terpilh"
}

//$("#tbl_view").jqGrid('gridResize',{minWidth:1800,maxWidth:2000,minHeight:300, maxHeight:500});
$("#tbl_view").jqGrid('sortableRows');

$("#tbl_view").navGrid('#pager', {cloneToTop:true,view: true, <?php if($_SESSION[level]=='SPV'){?>del: false, add: false, edit: true,refresh:false }<?php }else{?>del: true, add: true, edit: true,refresh:false}<?php }?>,
 {
 closeOnEscape: true,
 afterShowForm: function () { SplitFormatForm("tbl_view", false); }
 }, // default settings for edit
 {
 afterShowForm: function () { SplitFormatForm("tbl_view", false); }
 }, // default settings for add
 {}, // delete
 { closeOnEscape: true, multipleSearch: true,
 closeAfterSearch: true}, // search options
 {}
 );

//fungsi untuk menambah top navigator di atas
 var topPagerDiv = $("#tbl_view_toppager")[0];
$("#edit_tbl_view", bottomPagerDiv).remove();
$("#del_tbl_view", bottomPagerDiv).remove();
$("#search_tbl_view", bottomPagerDiv).remove();
$("#refresh_tbl_view", bottomPagerDiv).remove();
$("#tbl_view_toppager_center", topPagerDiv).remove();
$(".ui-paging-info", topPagerDiv).remove();

var bottomPagerDiv = $("div#pager")[0];
$("#add_tbl_view", bottomPagerDiv).remove();

$.jgrid.search={
 caption: "Pencarian",
 Find : "Cari",
 Reset: "Reset",
 odata : ['sama dengan', 'tidak sama dengan', 'less', 'less or equal','greater','greater or equal', 'diawali dengan','does not begin with','is in','is not in','diakhiri dengan','does not end with','seperti','does not contain'],
 sopt:['eq','ne','cn','bw','ew']
 }
 $.jgrid.del ={
 caption: "Hapus Data",
 msg: "Hapus data tersebut?",
 bSubmit: "Hapus",
 bCancel: "Batal",
}
//menambah tombol refresh
$("#tbl_view").jqGrid('navButtonAdd',"#pager",{caption:"Refresh",title:"Refresh Data",buttonicon :'ui-icon-refresh',
 onClickButton:function(){
 $("#tbl_view").setGridParam({datatype:'json',multiselect: true}).trigger('reloadGrid');
 mygrid[0].clearToolbar();
 }
});
//show/hide filtertoolbar
$("#tbl_view").jqGrid('navButtonAdd',"#pager",{caption:"Toggle",title:"Toggle Search Toolbar", buttonicon :'ui-icon-pin-s',
 onClickButton:function(){
 mygrid[0].toggleToolbar()
 }
});
//manambah tombol pengaturan kolom
$("#tbl_view").jqGrid('navButtonAdd',"#pager",{caption:"Atur Kolom",title:"Remove Columns", buttonicon :'ui-icon-calculator',
 onClickButton: function() {
 grid.jqGrid('columnChooser', {
 done: function(perm) {
 if (!perm) { return false; }
 this.jqGrid('remapColumns', perm, true);
 mygrid.updateColumns();
 }
 });
 }
});

//menabah filtertoolbar dan setting pencarian default jadi "cn"/contain
$("#tbl_view").jqGrid('filterToolbar',{stringResult: true,searchOnEnter : false, defaultSearch : "cn",autosearch:true});

menambah gouping pada jqgrid
$("#chngroup").change(function(){
 var vl = $(this).val();
 if(vl) { if(vl == "clear") {
 $("#tbl_view").jqGrid('groupingRemove',true);
 } else {
 $("#tbl_view").jqGrid('groupingGroupBy',vl);
 }
 }
 });

//fungsi untuk menampilkan dialog box
 function showUsernameDialog(text,heights){
 $('#usernameDialog').dialog({
 autoOpen: false,
 width: 200,
 height: heights,
 modal: true,
 title: 'Warning'
 });
 $("#dialog").text(text);
 $('#usernameDialog').dialog('open');
 }
 $('#ui-datepicker-div').hide();
});

&nbsp;
<pre>

Setelah itu kita buat file accountdata.php. File ini berfungsi untuk menampilkan data dari database ke jqgrid

 <?php
 session_start();
 include("config.php");
 include("tanggal.php");

$page = $_REQUEST['page'];
 $limit = $_REQUEST['rows'];
 $sidx = $_REQUEST['sidx'];
 $sord = $_REQUEST['sord'];
 $search=$_REQUEST['_search'];

if(!$sidx) $sidx =1;

$totalrows = isset($_REQUEST['totalrows']) ? $_REQUEST['totalrows']: false;
 if($totalrows) {
 $limit = $totalrows;
 }

$result = mysql_query("SELECT COUNT(*) AS count FROM admin WHERE id = '".$_SESSION[id]."' ". $where);
 if($_SESSION['level']=="Admin"){
 $result = mysql_query("SELECT COUNT(*) AS count FROM admin WHERE id = '".$_SESSION[id]."' OR level = 'SPV' ". $where);
 }
 $row = mysql_fetch_array($result,MYSQL_ASSOC);
 $count = $row['count'];

if( $count >0 ) { $total_pages = ceil($count/$limit);
 } else { $total_pages = 0; }

if ($page > $total_pages) $page=$total_pages;

$start = $limit*$page - $limit; //
 if($start <0) $start = 0;

$SQL = "SELECT * FROM admin WHERE id = '".$_SESSION[id]."' ".$where." ORDER BY $sidx $sord LIMIT $start , $limit";
 if($_SESSION['level']=="Admin"){
 $SQL = "SELECT * FROM admin WHERE id = '".$_SESSION[id]."' OR level = 'SPV' ".$where." ORDER BY $sidx $sord LIMIT $start , $limit";
 }
 $result = mysql_query( $SQL ) or die("Couldn`t execute query.".mysql_error());
 $responce->page = $page;
 $responce->total = $total_pages;
 $responce->records = $count;
 $i=0;
 while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
 $responce->rows[$i]['id']=$row['id'];
 $responce->rows[$i]['cell']=array(
 $row[id],
 $row[name],
 $row[address],
 $row[phone],
 $row[email],
 $row[username],
 $row[password],
 $row[level],
 $row[a_date_str]
 );
 $i++;
 }
 // return the formated data
 echo json_encode($responce);

?>
<pre>

Selanjutnya buat file accountcrud.php. File ini yang akan menjalankan INSERT UPDATE DELETE ke database

</pre>
<?php
include("config.php");
include("tanggal.php");
$oper=$_REQUEST['oper'];
$id=$_REQUEST['id'];
$level =$_REQUEST['level'];
$name=$_REQUEST['name'];
$address=$_REQUEST['address'];
$phone=$_REQUEST['phone'];
$email=$_REQUEST['email'];
$username=$_REQUEST['username'];
$password=$_REQUEST['password'];
$password=$_REQUEST['password'];

$user = $_REQUEST[user];

switch ($oper) {
 case 'add':
 $q=mysql_query("INSERT INTO admin VALUES('',
 '$level',
 '$name',
 '$address',
 '',
 '',
 '',
 '',
 '$phone',
 '$email',
 '$username',
 '".md5($password)."',
 'Active',
 '$date_str'
 )");
 if($q){
 echo "suksess";
 }
 break;
 case 'edit':
 $qry = mysql_query("SELECT * FROM admin WHERE id='$id'");
 $data = mysql_fetch_array($qry);
 $pass = ($data['password']==$password)? $data['password'] : md5($password);
 $q=mysql_query("UPDATE admin SET name='$name',
 address='$address',
 phone = '$phone',
 email = '$email',
 username = '$username',
 password = '$pass',
 level = '$level'
 WHERE id='$id'");
 if($q){
 echo "suksess";
 }
 break;
 case 'del':
 $arrId = explode(',',($id));
 foreach($arrId as $k=>$v){
 $q=mysql_query("DELETE FROM admin WHERE id='$v'");
 }
 if($q){
 echo "suksess";
 }
 break;
}
?>
<pre>
%d bloggers like this: