<?php
/* ***************************************************
Class TableGrid v 1.0
(c) budi laxono (28-05-2008)
class to create datagrid instantly by query
supporting Mssql Server, MySQL, PostgreSQL
**************************************************** */
class TableGrid {
private $databaseType;
public $siteaddress;
function TableGrid($dbtype, $srvname, $dbname, $uname, $pass, $port) {
$this->databaseType = $dbtype;
switch($dbtype) {
case "mssql" :
$conn = mssql_connect($srvname, $uname, $pass);
if($conn) { mssql_select_db($dbname,$conn); }
break;
case "mysql" :
if(!$port || $port == ""){ $srvcon = $srvname; } else { $srvcon = $srvname.":".$port; }
$conn = mysql_connect($srvcon, $uname, $pass);
if($conn) { mysql_select_db($dbname,$conn); }
break;
case "pgsql" :
if(!$port || $port == ""){ $port = "5432"; }
$conn_string = "host=$srvname port=$port dbname=$dbname user=$uname password=$pass";
$conn = pg_connect($conn_string);
break;
}
$siteaddress = $_SERVER['PHP_SELF'];
}
function get_field_num($result) {
switch($this->databaseType) {
case "mssql" :
return mssql_num_fields($result);
break;
case "mysql" :
return mysql_num_fields($result);
break;
case "pgsql" :
$fields = pg_num_fields($result);
return $fields;
break;
}
}
function get_row_num($result) {
switch($this->databaseType) {
case "mssql" :
return mssql_num_rows($result);
break;
case "mysql" :
return mysql_num_rows($result);
break;
case "pgsql" :
$rows = pg_num_rows($result);
return $rows;
break;
}
}
function get_field_names($result) {
$field_nums = $this->get_field_num($result);
$arr = array();
switch($this->databaseType) {
case "mssql" :
for($i=0;$i<$field_nums;$i++) {
$arr[$i] = mssql_field_name($result, $i);
}
break;
case "mysql" :
for($i=0;$i<$field_nums;$i++) {
$arr[$i] = mysql_field_name($result, $i);
}
break;
case "pgsql" :
for($i=0;$i<$field_nums;$i++) {
$arr[$i] = pg_field_name($result, $i);
}
break;
}
return $arr;
}
function get_all_result($query) {
switch($this->databaseType) {
case "mssql" :
return mssql_query($query);
break;
case "mysql" :
return mysql_query($query);
break;
case "pgsql" :
return pg_query($query);
break;
}
}
function create_table($query) {
echo "<link href='mycss.css' rel='stylesheet' type='text/css'>";
$result = $this->get_all_result($query);
$field_nums = $this->get_field_num($result);
$field_names = $this->get_field_names($result);
echo "<table border=1>";
// DATA HEADER
echo "<tr>";
for($i=0;$i<$field_nums;$i++) {
echo "<th nowrap class='headTable'>";
echo $field_names[$i];
echo "</th>";
}
echo "</tr>";
// DATA ROW
switch($this->databaseType) {
case "mssql" :
while($row = mssql_fetch_array($result)) {
echo "<tr>";
for($i=0;$i<$field_nums;$i++) {
echo "<td nowrap class='td-data'>";
echo " ".$row[$field_names[$i]];
echo "</td>";
}
echo "</tr>";
}
break;
case "mysql" :
while($row = mysql_fetch_array($result)) {
echo "<tr>";
for($i=0;$i<$field_nums;$i++) {
echo "<td nowrap class='td-data'>";
echo " ".$row[$field_names[$i]];
echo "</td>";
}
echo "</tr>";
}
break;
case "pgsql" :
while($row = pg_fetch_array($result)) {
echo "<tr>";
for($i=0;$i<$field_nums;$i++) {
echo "<td nowrap class='td-data'>";
echo " ".$row[$field_names[$i]];
echo "</td>";
}
echo "</tr>";
}
break;
}
echo "</table>";
}
function create_paging_table($query, $pk, $data_per_page) {
echo "<link href='mycss.css' rel='stylesheet' type='text/css'>";
$result = $this->get_all_result($query);
$field_nums = $this->get_field_num($result);
$row_nums = $this->get_row_num($result);
$field_names = $this->get_field_names($result);
include "paging.php";
$paging = new PagedResults();
$paging->TotalResults = $row_nums;
$paging->ResultsPerPage = $data_per_page;
$InfoArray = $paging->InfoArray();
$limit = $InfoArray["SQL_LIMIT2"];
$offset = $InfoArray["SQL_LIMIT1"];
//printf($InfoArray);
switch($this->databaseType) {
case "mssql" :
$query = strtolower($query);
$page_query = str_replace("select ", "select top $limit ", $query);
list($f_from, $r_from) = explode("from", $page_query);
if(stripos($page_query, "where")){
list($f_query, $r_query) = explode("where", $page_query);
$page_query = "$f_query where $pk not in ( select top $offset $pk from $r_from ) and $r_query";
} else {
if(stripos($page_query, "group")){
list($f_query, $r_query) = explode("group", $page_query);
$page_query = "$f_query where $pk not in ( select top $offset $pk from $r_from ) group $r_query";
}elseif(stripos($page_query, "having")){
list($f_query, $r_query) = explode("having", $page_query);
$page_query = "$f_query where $pk not in ( select top $offset $pk from $r_from ) having $r_query";
}elseif(stripos($page_query, "order")){
list($f_query, $r_query) = explode("order", $page_query);
$page_query = "$f_query where $pk not in ( select top $offset $pk from $r_from ) order $r_query";
} else {
$page_query = "$page_query where $pk not in ( select top $offset $pk from $r_from )";
}
}
//echo $page_query."<br>";
$page_result = mssql_query($page_query);
break;
case "mysql" :
$page_query = $query." limit $offset,$limit";
$page_result = mysql_query($page_query);
break;
case "pgsql" :
$page_query = $query." limit $limit offset $offset";
$page_result = pg_query($page_query);
break;
}
echo "<div align='center'><table border=1 width=100%>";
// DATA HEADER
echo "<tr>";
for($i=0;$i<$field_nums;$i++) {
echo "<th nowrap class='headTable'>";
echo $field_names[$i];
echo "</th>";
}
echo "</tr>";
$no = $offset;
// DATA ROW
switch($this->databaseType) {
case "mssql" :
while($row = mssql_fetch_array($page_result)) {
$class = $no%2==0?"class='tr-odd'":"";
$no++;
echo "<tr $class>";
for($i=0;$i<$field_nums;$i++) {
echo "<td nowrap class='td-data'>";
echo " ".$row[$field_names[$i]];
echo "</td>";
}
echo "</tr>";
}
break;
case "mysql" :
while($row = mysql_fetch_array($page_result)) {
$class = $no%2==0?"class='tr-odd'":"";
$no++;
echo "<tr $class>";
for($i=0;$i<$field_nums;$i++) {
echo "<td nowrap class='td-data'>";
echo " ".$row[$field_names[$i]];
echo "</td>";
}
echo "</tr>";
}
break;
case "pgsql" :
while($row = pg_fetch_array($page_result)) {
$class = $no%2==0?"class='tr-odd'":"";
$no++;
echo "<tr $class>";
for($i=0;$i<$field_nums;$i++) {
echo "<td nowrap class='td-data'>";
echo " ".$row[$field_names[$i]];
echo "</td>";
}
echo "</tr>";
}
break;
}
echo "</table></div></div>";
//echo $this->siteaddress;
echo "<div id='gridPaginationContainer' style='text-align:center;'>Total ".$row_nums." records, ";
$paging->createLinks($this->siteaddress,'d=d');
echo "</div>";
}
function create_data_search($query, $data_search, $pk, $data_per_page) {
//echo var_dump($_GET,1);
echo "<link href='mycss.css' rel='stylesheet' type='text/css'>";
if(isset($_GET[$data_search])) {
$this->siteaddress = $_SERVER['PHP_SELF']."?$data_search=".$_GET[$data_search];
}
if(stripos($data_search, "_")) {
$arrds = explode("_", $data_search);
$strds = "";
foreach($arrds as $value) {
$strds .= "$value ";
}
} else { $strds = $data_search; }
echo "<form name='srcfrm1' method='get' action='".$this->siteaddress."'>";
echo "<table> <tr>";
echo "<td class='headTable'><strong>Data Search</strong></td></tr>";
echo "<tr><td class='headTable'><strong>".ucfirst(strtolower($strds))." : </strong>";
if($_GET[$data_search] != '') { echo "<input type='text' name='$data_search' value='".$_GET[$data_search]."' ></td>"; } else { echo "<input type='text' name='$data_search'></td>"; }
echo "</tr> <tr>";
echo "<td> <input type='submit' value='submit'> </td>";
echo "</tr> </table>";
echo "</form>";
$query = strtolower($query);
if($_GET[$data_search] != '') {
$src = $_GET[$data_search];
switch($this->databaseType) {
case "mssql" :
$query = strtolower($query);
//$page_query = str_replace("select ", "select top $limit ", $query);
list($f_from, $r_from) = explode("from", $page_query);
if(stripos($page_query, "where")){
list($f_query, $r_query) = explode("where", $page_query);
$page_query = "$f_query where $data_search like '$src%' and $r_query";
} else {
if(stripos($page_query, "group")){
list($f_query, $r_query) = explode("group", $page_query);
$page_query = "$f_query where $data_search like '$src%' group $r_query";
}elseif(stripos($page_query, "having")){
list($f_query, $r_query) = explode("having", $page_query);
$page_query = "$f_query where $data_search like '$src%' having $r_query";
}elseif(stripos($page_query, "order")){
list($f_query, $r_query) = explode("order", $page_query);
$page_query = "$f_query where $data_search like '$src%' order $r_query";
} else {
$page_query = "$query where $data_search like '$src%' ";
}
}
break;
case "mysql" :
list($f_from, $r_from) = explode("from", $page_query);
if(stripos($page_query, "where")){
list($f_query, $r_query) = explode("where", $page_query);
$page_query = "$f_query where $data_search like '$src%' and $r_query";
} else {
if(stripos($page_query, "group")){
list($f_query, $r_query) = explode("group", $page_query);
$page_query = "$f_query where $data_search like '$src%' group $r_query";
}elseif(stripos($page_query, "having")){
list($f_query, $r_query) = explode("having", $page_query);
$page_query = "$f_query where $data_search like '$src%' having $r_query";
}elseif(stripos($page_query, "order")){
list($f_query, $r_query) = explode("order", $page_query);
$page_query = "$f_query where $data_search like '$src%' order $r_query";
} else {
$page_query = "$query where $data_search like '$src%' ";
}
}
break;
case "pgsql" :
list($f_from, $r_from) = explode("from", $page_query);
if(stripos($page_query, "where")){
list($f_query, $r_query) = explode("where", $page_query);
$page_query = "$f_query where $data_search like '$src%' and $r_query";
} else {
if(stripos($page_query, "group")){
list($f_query, $r_query) = explode("group", $page_query);
$page_query = "$f_query where $data_search like '$src%' group $r_query";
}elseif(stripos($page_query, "having")){
list($f_query, $r_query) = explode("having", $page_query);
$page_query = "$f_query where $data_search like '$src%' having $r_query";
}elseif(stripos($page_query, "order")){
list($f_query, $r_query) = explode("order", $page_query);
$page_query = "$f_query where $data_search like '$src%' order $r_query";
} else {
$page_query = "$query where $data_search like '$src%'";
}
}
break;
}
} else { $page_query = $query; }
//echo $page_query."<br>";
$this->create_paging_table($page_query, $pk, $data_per_page);
}
}
?>