Login   Register  
PHP Classes
elePHPant
Icontem

File: Documentation_and_Demo/easyTables/php/generateTable.class.php

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Classes of enri_pin  >  Easy Tables  >  Documentation_and_Demo/easyTables/php/generateTable.class.php  >  Download  
File: Documentation_and_Demo/easyTables/php/generateTable.class.php
Role: Example script
Content type: text/plain
Description: Example script
Class: Easy Tables
Show, edit and export database table records
Author: By
Last change:
Date: 9 months ago
Size: 12,279 bytes
 

Contents

Class file image Download
<?php
	
	/*******************************************************************************
	* generateTable                                                                *
	*                                                                              *
	* Version: 1.0                                                                 *
	* Date:    2014-01-26                                                          *
	* Author:  enri_pin                                                            *
	* Email:   enri_pin@yahoo.com                                                  *
	*******************************************************************************/
	
	class generateTable{

		function getTable(){
			/* 
				Parameters:
					orderField: Table field that will be used in order by clause
					sense: ASC or DESC used with "order by" clause
					nResults: Number of rows that will be shown in the result table
					search: String to be searched in the database table
					searchField: Table field in where the search will be done
					actual: Page actual
					prev: Previous page from where you come
					configFile: File source of information
			*/
			
			ini_set('memory_limit', '1000M');
			set_time_limit(0);
			
			include_once("DBHelper.class.php");
			$dbhelper=new DBHelper($_REQUEST['configFile']);
			$char=$dbhelper->character();
			$esc=$dbhelper->escape();
			$dontAllowed=$dbhelper->nameDontAllowedChars();
			$orderField=((isset($_REQUEST['orderField']))?str_replace($dontAllowed,'',$_REQUEST['orderField']):"");
			$sense=((isset($_REQUEST['sense']))?$_REQUEST['sense']:"");
			$nResults=$_REQUEST['nResults'];
			
			$params=array();
			//Creating search condition if exists for the query
			$condition="";
			if(($_REQUEST['search']!="")&&($_REQUEST['searchField']!="0")){
				$searchField=$char.str_replace($dontAllowed,'',$_REQUEST['searchField']).$char;
				$condition=$searchField." like :searchValue";
				$params=array(
					":searchValue"=>array("%".$_REQUEST['search']."%",PDO::PARAM_STR)
				);
			}
			$query="select count(*) as total from (".$dbhelper->query.") as table_count ".(($condition!="")?"where ".$condition:"");

			$nRows=$dbhelper->getQuery($query,$params);
			$nPages=ceil($nRows[0][0]/$nResults);
			
			//======================Calculating the number of page actual============================
			$init=0;
			if(isset($_REQUEST['actual'])){
				if($_REQUEST['actual']>$nPages){
					$init=$nPages;
					$actual=$nPages;
				}else{
					$init=$_REQUEST['actual'];
					$actual=$_REQUEST['actual'];
				}
				switch($init){
					case "First":
						$init=0;
						$actual=1;
					break;
					case "Last":
						$init=(($nPages-1)*$nResults);
						$actual=$nPages;
					break;
					case ">>":
						$init=($_REQUEST['prev'])*$nResults;
						$actual=$_REQUEST['prev']+1;
					break;
					case "<<":
						$init=($_REQUEST['prev']-2)*$nResults;
						$actual=$_REQUEST['prev']-1;
					break;
					default:
						$init=($init-1)*$nResults;
					break;
				}
			}else	$actual=1;

			$fields=$dbhelper->getColumns($dbhelper->query);
			for($l=0;$l<count($fields);$l++) $fields[$l]=$char.$fields[$l].$char;
			
			//==========================Setting limits for the sql query==============================
			$limits="";
			switch($dbhelper->db){
				case "mysql":
				case "sqlite":
					$limits=" limit :ninit, :nresult";
					$params[':nresult']=array((int)$nResults,PDO::PARAM_INT);
				break;
				case "postgres":
					$limits=" limit :nresult offset :ninit";
					$params[':nresult']=array((int)$nResults,PDO::PARAM_INT);
				break;
				case "sqlserver":
					$limits=" a.row_1ab2>:ninit and a.row_1ab2<=:nresult ";
					$params[':nresult']=array((int)($init+$nResults),PDO::PARAM_INT);
				break;
			}
			$params[':ninit']=array((int)$init,PDO::PARAM_INT);
			
			//========Creating the sql instruction to be executed and shown in the result table=========
			if($dbhelper->db!="sqlserver"){
				$query="select * from (".$dbhelper->query.") as result1";
				if($condition!="") $query.=" where ".$condition;
				if(($orderField!="")&&($sense!="")){
					$query.=" order by ".$char.$orderField.$char." ".$sense;
				}
				$query.=$limits;
			}else{
				$ord="";
				if(($orderField!="")&&($sense!="")){
					$query.=" order by ".$char.$orderField.$char." ".$sense;
				}else
					$ord=$fields[0];
				$query="select ".implode(',',$fields)." from(select *, ROW_NUMBER() over(order by ".$ord.") as row_1ab2 from (".$dbhelper->query.") b";
				$query.=(($condition!="")?" where ".$condition:"").") a where ".$limits;
			}
			
			$result=$dbhelper->getQuery($query, $params);
			
			$showCheck=$_REQUEST['showCheck'];
			
			//=================Generating the html table to be shown in the web page===============
			echo "<table class='resultTable'>\n<tr class='header'>".(($showCheck!=0)?"<th class='control'>".(($showCheck!=3)?"<input type='checkbox'>":"")."</th>":"");
			$orderNumber=-1;
			$colSize="";
			$colName="";
			
			//Working with the table headers
			if(count($dbhelper->colHeadColors)>0)	$colH=$dbhelper->colHeadColors;
			if(count($dbhelper->colHeadFontColors)>0)	$colHF=$dbhelper->colHeadFontColors;
			for($i=0;$i<count($fields);$i++){
				$fields[$i]=trim($fields[$i],$char);
				$colSize=((isset($dbhelper->colSizes[$fields[$i]]))?" width='".$dbhelper->colSizes[$fields[$i]]."'":"");
				$colName=((isset($dbhelper->colNames[$fields[$i]]))?$dbhelper->colNames[$fields[$i]]:$fields[$i]);
				if($fields[$i]==$orderField){
					//Here you can change the down up icons. Modify <i class='icon-chevron-".(($sense=="asc")?"up":"down")." icon-white'>"
					echo "<th".((isset($dbhelper->colAlign[$fields[$i]]))?" align='".$dbhelper->colAlign[$fields[$i]]."'":"")." class='ord'".$colSize." name='".$fields[$i]."' ".((isset($colH[$fields[$i]]))?"style='background:".$colH[$fields[$i]].";".((isset($colHF[$fields[$i]]))?'color:'.$colHF[$fields[$i]].';':'')."'":"").">".$colName." <i class='icon-chevron-".(($sense=="asc")?"up":"down")." icon-white'></i></th>";
					$orderNumber=$i;
				}else{
					echo "<th".((isset($dbhelper->colAlign[$fields[$i]]))?" align='".$dbhelper->colAlign[$fields[$i]]."'":"")." class='ord'".$colSize." name='".$fields[$i]."' ".((isset($colH[$fields[$i]]))?"style='background:".$colH[$fields[$i]].";".((isset($colHF[$fields[$i]]))?'color:'.$colHF[$fields[$i]].';':'')."'":"").">".$colName."</th>";
				}
			}
			echo "<th class='control2'></th></tr>\n";
			$background="";
			echo "<tr class='0'></tr>";
			
			//Working with the table body
			if($result!=false){//If the table has rows
				for($i=0;$i<count($result);$i++){
					//Setting the color of each row according the user input or by default 
					if(($i%2)==0){
						$bgorder=((isset($dbhelper->colOrderColors['Even']))?$dbhelper->colOrderColors['Even']:"#888");
						$cOrder=((isset($dbhelper->colOrderFontColors['Even']))?$dbhelper->colOrderFontColors['Even']:"#fff");
						if(count($dbhelper->colColorsEven)>0)	$colC=$dbhelper->colColorsEven;
						elseif(count($dbhelper->colColorsOdd)>0)	$colC=$dbhelper->colColorsOdd;
						if(count($dbhelper->colFontColors)>0) $colFC=$dbhelper->colFontColors;
					}else{
						$bgorder=((isset($dbhelper->colOrderColors['Odd']))?$dbhelper->colOrderColors['Odd']:"#666");
						$cOrder=((isset($dbhelper->colOrderFontColors['Odd']))?$dbhelper->colOrderFontColors['Odd']:"#fff");
						if(count($dbhelper->colColorsOdd)>0)	$colC=$dbhelper->colColorsOdd;
						elseif(count($dbhelper->colColorsEven)>0)	$colC=$dbhelper->colColorsEven;
						if(count($dbhelper->colFontColors)>0) $colFC=$dbhelper->colFontColors;
					}
					echo "<tr class='".($i+1)."'>";
					if($showCheck!=0) echo "<td class='control'>".(($showCheck!=3)?"<input type='checkbox' class='".($i+1)."'>":"")."</td>";
					//Generating the html of the table cells
					for($j=0;$j<count($fields);$j++){
						if(isset($result[$i][$j])){
							if($j!=$orderNumber){
								echo "<td";
								if((isset($colC[$fields[$j]]))||(isset($colFC[$fields[$j]]))){
									echo " style='";
									if(isset($colC[$fields[$j]])){
										echo "background:".$colC[$fields[$j]].";".((!isset($colFC[$fields[$j]]))?"'":"");
									}
									if(isset($colFC[$fields[$j]])){
										echo "color:".$colFC[$fields[$j]]."'";
									}
								}
								if(isset($dbhelper->friendlyOutput[$fields[$j]][$result[$i][$j]]))
									echo " value='".$result[$i][$j]."'".((isset($dbhelper->colAlign[$fields[$j]]))?" align='".$dbhelper->colAlign[$fields[$j]]."'":"").">".$dbhelper->friendlyOutput[$fields[$j]][$result[$i][$j]]."</td>";
								else
									echo ((isset($dbhelper->colAlign[$fields[$j]]))?" align='".$dbhelper->colAlign[$fields[$j]]."'":"").">".$result[$i][$j]."</td>";
							}else{
								if(isset($dbhelper->friendlyOutput[$fields[$j]][$result[$i][$j]]))
									echo "<td".((isset($dbhelper->colAlign[$fields[$j]]))?" align='".$dbhelper->colAlign[$fields[$j]]."'":"")." style='background:".((isset($colC[$fields[$j]]))?$colC[$fields[$j]]:$bgorder).";color:".((isset($colFC[$fields[$j]]))?$colFC[$fields[$j]]:$cOrder)."' value='".$result[$i][$j]."'>".$dbhelper->friendlyOutput[$fields[$j]][$result[$i][$j]]."</td>";
								else
									echo "<td".((isset($dbhelper->colAlign[$fields[$j]]))?" align='".$dbhelper->colAlign[$fields[$j]]."'":"")." style='background:".((isset($colC[$fields[$j]]))?$colC[$fields[$j]]:$bgorder).";color:".((isset($colFC[$fields[$j]]))?$colFC[$fields[$j]]:$cOrder)."'>".$result[$i][$j]."</td>";
							}
						}
					}
					/*
						To change the icons for edit and show you can just change the html code inside the span tags.
						Then if it's necessary you can change the .control2 width in the css/easyTable.css file
					*/
					echo "<td class='control2'><span title='see' class='".($i+1)."'>".(isset($dbhelper->changeIcons['show'])?$dbhelper->icons['show']:"<i class='icon-eye-open'></i>")."</span>".(($showCheck==2)?" <span title='edit' class='".($i+1)."'><i class='icon-pencil'></i></span>":"")."</td>";
					echo "</tr>\n";
				}
			}else{
				echo "<tr><td colspan='".(count($fields)+2)."' class='no_result'>There are no results</td></tr>";
			}
			echo "</table>";
			
			//=======================Generating the pager for the table============================
			echo "<table class='pager'><tr><td class='exp left'>Export to <select name='exportOptions'></select> <a class='get button icon arrowdown'>Get</a></td><td class='right'>";
			echo "<span class='total'>Total: ".$nRows[0][0]." results.</span> ";
			if($nRows[0][0]==0) $actual=0;
			echo "Page ".$actual." of ".$nPages."  ";
			if($nPages>5){
				if($actual<=3){
					if($actual!=1)
						echo '<input type="button" value="<<" class="pag">  ';
					for($i=1;$i<=5;$i++){
						if($i!=$actual)
							echo "<input type='button' value='".$i."' class='pag'> ";
						else
							echo "<input type='button' value='".$i."' class='actual'>  ";
					}
					echo "<input type='button' value='>>' class='pag'>  ";
					echo "<input type='button' value='Last' class='pag'>";
				}elseif($actual<=$nPages-2){
					echo "<input type='button' value='First' class='pag'>  ";
					echo '<input type="button" value="<<" class="pag">  ';
					for($i=$actual-2;$i<=$actual+2;$i++){
						if($i!=$actual)
							echo "<input type='button' value='".$i."' class='pag'>  ";
						else
							echo "<input type='button' value='".$i."' class='actual'>  ";
					}
					echo "<input type='button' value='>>' class='pag'>  ";
					echo "<input type='button' value='Last' class='pag'>";
				}else{
					echo "<input type='button' value='First' class='pag'>  ";
					echo '<input type="button" value="<<" class="pag"> ';
					for($i=$nPages-4;$i<=$nPages;$i++){
						if($i!=$actual)
							echo "<input type='button' value='".$i."' class='pag'>  ";
						else
							echo "<input type='button' value='".$i."' class='actual'>  ";
					}
					if($actual!=$nPages){
						echo "<input type='button' value='>>' class='pag'>  ";
					}
				}
			}else{
				for($i=1;$i<=$nPages;$i++){
					if($i!=$actual) 
						echo "<input type='button' value='".$i."' class='pag'>  ";
					else
						echo "<input type='button' value='".$i."' class='actual'>  ";
				}
			}
			echo "</td></tr></table>";
		}
	}
	
	$table=new generateTable();
	$table->getTable();
?>