Location: PHPKode > scripts > tGrid > tgrid/tgrid.php
<?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 "&nbsp;".$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 "&nbsp;".$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 "&nbsp;".$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 "&nbsp;".$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 "&nbsp;".$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 "&nbsp;".$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);
	}
}

?>
Return current item: tGrid