For this moment now I would like to discuss data grids show by browser which add by capability of data field sorting, can be an ascending or descending, beside that also add ability of data paginating, so as we want to expose a numerous of data in a browser, it don’t need scrolling anymore and replace by data paginating which divide the data into several page.
Step one is create a table tag in scope of html tag in php file which going to be expose on a browser, as an example we use 'tbl_siswa' as table on mysql database. And safe this file as 'sortable.php' and fill this file with this syntax like shown below:
<html>
<head>
<title>Editable Data Grid</title>
<script type="text/javascript" language="javascript">
<!—- Fill it with javascript -->
</script>
</head>
<body>
<form id="myForm" name="myForm" method="post">
<div class="area">
<br>
<table width="80%" cellpadding="0" cellspacing="0" border="0">
<tr><td align="right"><? //Fill it with paginating code ?></td></tr>
<tr><td align="right"> </td></tr>
</table>
<table width="80%" cellpadding="0" cellspacing="0" border="1" >
<tr>
<th width="0%"> </th>
<th width="24%" onclick="<!--Function sort-->">Nomor Induk</th>
<th width="24%" onclick="<!--Function sort-->">Nama Siswa</th>
<th width="50%" onclick="<!--Function sort-->">Alamat Lengkap</th>
</tr>
<? //Diisi kode looping data ?>
<tr>
<td> </td>
<td><? //Diisi kode php echo ?></td>
<td><? //Diisi kode php echo ?></td>
<td><? //Diisi kode php echo ?></td>
</tr>
<? //Diisi kode looping data ?>
</table>
</div>
</form>
</body>
</html>
Next thing to do is write code to query data in 'tbl_siswa' table, first we must create several variable to initialize and use it for sorting and paginating process, as follow is php code written in template file and place it in beginning of file before code like shown above, and the php code is like shown below:
<?
$fieldId = 1;
if (isset($_REQUEST['fieldId'])) {$fieldId = $_REQUEST['fieldId'];}
$lastSort = 'asc';
if (isset($_REQUEST['sort'])) {$lastSort = $_REQUEST['sort'];}
$link = mysql_connect('localhost', 'root', 'admin');
if (!$link) {die('Could not connect: ' . mysql_error());}
$link1 = mysql_select_db('latih');
if (!$link1) {die('Could not select database');}
if ($fieldId == '1') {
$field = 'no_induk';
} else if ($fieldId == '2') {
$field = 'nama';
} else if ($fieldId == '3') {
$field = 'alamat';
} else {
$field = 'no_induk';
}
$query0 = 'SELECT id, no_induk, nama, alamat FROM tbl_siswa ORDER BY '.$field.' '.$lastSort;
$resultRow = mysql_query($query0);
$num_rows = mysql_num_rows($resultRow);
$first_pages = 1; $data_content = 5;
$last_pages = ceil($num_rows / $data_content);
$increment = $first_pages;
if (isset($_REQUEST['increValue'])) {$increment = $_REQUEST['increValue'];}
// Is used limit offset mysql technique
$query1 = 'SELECT id, no_induk, nama, alamat FROM tbl_siswa ORDER BY '.$field.' '.$lastSort.' LIMIT '.$data_content.' OFFSET '.$increment;
$result = mysql_query($query0);
if (!$result) {die('Query failed: ' . mysql_error());}
// If used Array top and bottom edge technique and use it as
// temporary buffer, and split it as top and bottom edge
$idx = 0;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$showData[$idx]['no_induk'] = $line['no_induk'];
$showData[$idx]['nama'] = $line['nama'];
$showData[$idx]['alamat'] = $line['alamat'];
$idx++;
}
$firstEdge = ($data_content * $increment) - $data_content;
$lastEdge = ($data_content * $increment) - 1;
?>
After connection code follow by query code written in php, don’t forget to close the connection and place it in a bottom of that template file like shown below:
<?
mysql_free_result($result);
mysql_close($link);
?>
Don’t forget to write JavaScript code to activate sorting and paginating trigger, then place it all the JavaScript function into template file already setup before, but please remember to place it on place already mark with red color written, and if all the html template, JavaScript and php code combine will look like shown below:
<?
$fieldId = 1;
if (isset($_REQUEST['fieldId'])) {$fieldId = $_REQUEST['fieldId'];}
$lastSort = 'asc';
if (isset($_REQUEST['sort'])) {$lastSort = $_REQUEST['sort'];}
$link = mysql_connect('localhost', 'root', 'admin');
if (!$link) {die('Could not connect: ' . mysql_error());}
$link1 = mysql_select_db('latih');
if (!$link1) {die('Could not select database');}
if ($fieldId == '1') {
$field = 'no_induk';
} else if ($fieldId == '2') {
$field = 'nama';
} else if ($fieldId == '3') {
$field = 'alamat';
} else {
$field = 'no_induk';
}
$query0 = 'SELECT id, no_induk, nama, alamat FROM tbl_siswa ORDER BY '.$field.' '.$lastSort;
$resultRow = mysql_query($query0);
$num_rows = mysql_num_rows($resultRow);
$first_pages = 1;
$data_content = 5;
$last_pages = ceil($num_rows / $data_content);
$increment = $first_pages;
if (isset($_REQUEST['increValue'])) {$increment = $_REQUEST['increValue'];}
// Start of limit offset mysql technique
$query1 = 'SELECT id, no_induk, nama, alamat FROM tbl_siswa ORDER BY '.$field.' '.$lastSort.' LIMIT '.$data_content.' OFFSET '.$increment;
$result = mysql_query($query1);
if (!$result) {die('Query failed: ' . mysql_error());}
// End of limit offset mysql technique
// Start of array top and bottom edge technique
$result = mysql_query($query0);
if (!$result) {die('Query failed: ' . mysql_error());}
$idx = 0;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$showData[$idx]['no_induk'] = $line['no_induk'];
$showData[$idx]['nama'] = $line['nama'];
$showData[$idx]['alamat'] = $line['alamat'];
$idx++;
}
$firstEdge = ($data_content * $increment) - $data_content;
$lastEdge = ($data_content * $increment) - 1;
// End of array top and bottom edge technique and use it as
// temporary buffer, and split it as top and bottom edge
?>
<html>
<head>
<title>Editable Data Grid</title>
<script type="text/javascript" language="javascript">
function sortableField(par1,par2,par3) {
if (par2 == "asc") {
var lastSort = "desc";
} else if (par2 == "desc") {
var lastSort = "asc";
} else {
var lastSort = "asc";
}
document.myForm.action = "sortable.php?fieldId="+par1+"&sort="+lastSort+"&increValue="+par3;
document.myForm.submit();
}
function prev(par1,par2,par3) {
if (parseInt(par3) != 1) {
par3 = par3 - 1;
}
document.myForm.action = "sortable.php?fieldId="+par1+"&sort="+par2+"&increValue="+par3;
document.myForm.submit();
}
function next(par1,par2,par3) {
var lastPage = parseInt('');
if (parseInt(par3) != lastPage) {
par3 = par3 + 1;
}
document.myForm.action = "sortable.php?fieldId="+par1+"&sort="+par2+"&increValue="+par3;
document.myForm.submit();
}
</script>
</head>
<body>
<form id="myForm" name="myForm" method="post">
<div class="area"><br>
<table width="80%" cellpadding="0" cellspacing="0" border="0">
<tr>
<td align="right">
<?
if ($increment == $first_pages) {
echo ' prev ';
} else { ?>
<a href="#" onclick="prev(<? echo $fieldId; ?>, '<? echo $lastSort; ?>',<? echo $increment; ?>);"> prev</a>
<?}
echo $increment." of ".$last_pages;
if ($increment == $last_pages) {
echo ' next ';
} else { ?>
<a href="#" onclick="next(<? echo $fieldId; ?>, '<? echo $lastSort; ?>',<? echo $increment; ?>);"> next</a>
<? } ?>
</td>
</tr>
<tr><td align="right"> </td></tr>
</table>
<table width="80%" cellpadding="0" cellspacing="0" border="1" >
<tr>
<th width="0%">> </th>
<th width="24%" onclick="sortableField(1, '<? echo $lastSort; ?>',<? echo $increment; ?>);">Nomor Induk</th>
<th width="24%" onclick="sortableField(2, '<? echo $lastSort; ?>',<? echo $increment; ?>);">Nama Siswa</th>
<th width="50%" onclick="sortableField(3, '<? echo $lastSort; ?>',<? echo $increment; ?>);">Alamat Lengkap</th>
</tr>
<!-- If used limit offset mysql technique -->
<? while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {?>
<tr>
<td> </td>
<td><? echo $line['no_induk']; ?></td>
<td><? echo $line['nama']; ?></td>
<td><? echo $line['alamat']; ?></td>
</tr>
<? } ?>
<!-- If used array top and bottom edge technique -->
<? for ($i = 0; $i < count($showData); $i++) {
if ($i >= $firstEdge && $i <= $lastEdge) {?>
<tr>
<td> </td>
<td><? echo $showData[$i]['no_induk']; ?></td>
<td><? echo $showData[$i]['nama']; ?></td>
<td><? echo $showData[$i]['alamat']; ?></td>
</tr>
<? }
} ?>
</table>
</div>
</form>
</body>
</html>
<?
mysql_free_result($result);
mysql_close($link);
?>
From sample code above we can see two kind paginating technique written on purpose, each of technique have some advantage and lacked, if we use offset limit technique, then advantage we’ll got light weight of data loading, because paginating mechanism already handed by database server, but have a weakness on data that show on a browser always had a same limit weather on first page till end of page, for instance if we have 23 data record with limit 10, according to paging algorithm will produce 3 page with each page show 10 record except last page only show 3 record, but this couldn’t happen if used mysql limit offset technique, last page will always show 10 record, to fulfill term of limit 10 by take several record to page before last page than show it again on last page. This thing gave the effect of this program experience some error on it (probably true or false, correct me if I’m wrong..! ^__^ ).
Whereas if used array with top and bottom edge technique, then the result we going to see on browser will accord with expected algorithm, but it sacrifice of heavy weight data loading if it reach to thousand or event million of data. This cause by all record load from database on first step then filter it in php code. From this two technique within advantage and lacked, decision to used which technique is handover to developer. This image below is shown how looks like if these codes above see on a browser:
From figure above we can see paginating trigger can execute through clicked link “prev” or “next”, when browser in condition showing first page than link “prev” will disabled, and when condition showing last page then link “next” will disabled. Whereas for “ascending” and “descending” trigger from each fields, just click table header from each field then field already clicked will automatically sort. Unnecessary “asc” or “desc” properties for this sorting process cause that properties already set switch automatically every header clicked between “asc” and “desc”.
For your record this two technique name I made myself, I’m not searching yet in google for this issue weather this name exist or not (^__^V hehe.. peace). Perhaps only this knowledge I can share to all reader for this moment, if in this article contain some mistake, please don’t mind to correct me if I done some wrong or give me some advise and comment. To improve knowledge and share it to other.
Read More...
