Useful PHP DataTable Class Example To Store Objects including Recordset

Introduction

Any table like objects like array and recordset have same structure. They have rows and columns.
Usually column is a key and row is a value.

A recordset that comes from database ‘select’ operation usually stored in array or OOP object variable on MVC concept or any kind of business logic and view layer.
A web page access this variable to show its data. View layer only include database and datatable class files. So web page users doesn’t access database operational directly.

With this concept, I have build a simple DataTable class to store those kind of objects.
This DataTable has functions to add columns and rows and get their values so that we can extract information from DataTable with easy and use same methods repeatedly.
Also we can get DataTable object value on another page with SESSION functionality.

DataTable Class

class DataTable {
	private $str_title, $arrcolumns, $arrrows;
	function __construct() {
	
		$this->arrcolumns = array();
		$this->arrrows = array();
	}
	
	function set_title($str_title) {
		$this->str_title = $str_title;
	}
	
	function get_title() {
		return $this->str_title;
	}
	
	function addcolumn($columnname, $idxcolumn=null) {
		if (!in_array($columnname, $this->arrcolumns)) {
			array_push($this->arrcolumns, $columnname);
		}
		
	}
	
	function columns($idx=null) {
		if (is_null($idx) || (string) $idx=="") {
		
			return $this->arrcolumns;
		} else {
			
			return $this->arrcolumns[$idx];		
		}
	}
	
	function columnidx($columnname) {
		$array_keys = array_keys($this->arrcolumns, $columnname);
		return $array_keys[0];
	}
	
	function addrow($arrrow) {
		$this->arrrows[] = $arrrow;		
	}
	
	function rows($idx=null) {
		if (is_null($idx) || (string) $idx=="") {
			
			return $this->arrrows;
		} else {			
			return $this->arrrows[$idx];
		}
	}
	
	function data($idxrow, $idxcolumn) {
		return $this->arrrows[$idxrow][$idxcolumn];
	}	
	
	function total_rows() {
		return count($this->arrrows);
	}
}

DataTable Class has set_title($str_title), get_title() to set and get heading title, addcolumn() to add a column, addrow() to add row, columns($idx=null) to get column array or column name at specific index,
rows($idx=null) to get rows array or row array value at specific index. Also it has data($idxrow, $idxcolumn) to get cell value.

Store Array Example

Storing Person First Name and Last Name

//include datatable class
include_once "datatable.php";

$dt = new DataTable("person");
//add a column / header
$dt->addcolumn("FirstName");
$dt->addcolumn("LastName");

//adding rows
$dt->addrow(array("Agung", "Gugiaji"));
$dt->addrow(array("John", "Doe"));

//alternative way to add row
$row = array();
$row[0] = "Peter";
$row[1] = "Parker";
$dt->addrow($row);

//get columns name
for ($j=0;$j<count($dt->columns());$j++) {
	echo $dt->columns($j)." ";
}
echo "<br>";

//get rows array or cell value by row and column index
echo "<hr>";
for ($i=0;$i<count($dt->rows());$i++) {
	for ($j=0;$j<count($dt->columns());$j++) {
		echo $dt->data($i,$j)." ";
	}
	echo "<br>";
}
echo "<hr>";
//alternative way to get rows array value
$arrrows = $dt->rows();
for ($i=0;$i<count($arrrows);$i++) {
	for ($j=0;$j<count($dt->columns());$j++) {
		echo $arrrows[$i][$j]." ";
	}
	
	echo "<br>";
}

DataTable has two ways to get columns and rows value which are using data() and extract value from rows array. The result should show added FirstName & LastName.

Database Example with DB2

I am using DB2 SAMPLE database to show Employee info. Also I use paging on footer page so that record retrieval is limited by pagesize.

I differentiate the business logic and view layer so I have datatable.php, employeedb.php and viewemployee.php.

employee.php file:

class employee {
	function __construct() {
	}
	
	function employeelist() {
		$conn = db2_connect("SAMPLE", "agung", "abc123");
		$sql = "select FirstNME, LastName, WorkDept, PhoneNo from Employee";
		$query_stmt = db2_prepare($conn, $sql);
		$result = db2_execute($query_stmt);

		$dt = new DataTable();
		$dt->set_title("Employee Info");
		$dt->addcolumn("First Name");
		$dt->addcolumn("Last Name");
		$dt->addcolumn("Work Dept");
		$dt->addcolumn("Phone");

		while ($row = db2_fetch_array($query_stmt)) {
			$arr_row = array($row[0], $row[1], $row[2], $row[3]);
			$dt->addrow($arr_row);	
		}
		db2_close($conn);
		
		return $dt;
	}
}

Employee class returns list of them on DB2 SAMPLE database. I use datatable class to store the recordset. Viewemployee.php will access this datatable to show employee list.
viewemployee.php file:

include_once "datatable.php";
include_once "employee.php";
session_start();

$pagesize = 10;
$page = $_GET["page"];

if ($page == "" || $_SESSION["sess_dt"]=="") {
	$emp = new employee();
	$dt = $emp->employeelist();
	
	$_SESSION["sess_dt"] = $dt;
	$page = 1;
} else {
	$dt = $_SESSION["sess_dt"];
}

$startrow_idx = ($page-1)*$pagesize;

echo "<h3>".$dt->get_title()."</h3>";
echo "<table>";
echo "<tr>";
for ($j=0;$j<count($dt->columns());$j++) {
	echo "<th>".$dt->columns($j)."</th>";
}
echo "</tr>";

$arrrows = $dt->rows();
for ($i=$startrow_idx;$i<($startrow_idx + $pagesize);$i++) {
	if ($i>$dt->total_rows()) break;
	echo "<tr>";
	for ($j=0;$j<count($dt->columns());$j++) {
		echo "<td>".$dt->data($i,$j)."</td>";
	}
	echo "</tr>";
}
echo "</table>";

for ($i=1;$i<=ceil($dt->total_rows()/$pagesize);$i++) {
	echo "<a href='".$_SERVER["PHP_SELF"]."?page=".$i."'>".$i."</a> &nbsp;";
}
echo "<br><br>";
echo "<input type=button value='Save data to excel' id='btnxl' onclick=\"window.open('save_datatable_xl.php')\">";

This file doesn’t has any database operation. It is included on related php files. This file use datatable functionality only. We can change to another RDBMS software without affecting datatable and view file.

I use session to store datatable object due to web paging facility. So that I do not need to retrieve same recordset from database repeatedly.

I use same way with other type of objects to get record Rows and Columns value. Also I have Save Data To Excel button to export those records to Excel file

Export Data To Excel

I use PHPExcel library to create Excel via PHP. Visit this link http://phpexcel.codeplex.com/ to download the libraries.

Session is used to get data from previous process. The method to get data is also same as above.

include_once "datatable.php";
require_once "phpexcel/1.7.6/Classes/PHPExcel.php";
require_once "phpexcel/1.7.6/Classes/PHPExcel/IOFactory.php";

session_start();
if ($_SESSION["sess_dt"]=="") {
	die("Session is empty");
}
//get datatable instance via session
$dt = $_SESSION["sess_dt"];

//create phpexcel instance
$objPHPExcel = new PHPExcel();
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );
//set first sheet as default
$var = $objPHPExcel->setActiveSheetIndex(0);

//create header column
$col_ascii = 65;
$rowxl=1;
for ($j=0;$j<count($dt->columns());$j++) {
	$var->setCellValue(chr($col_ascii).$rowxl, $dt->columns($j));
	$col_ascii++;
	
}
$rowxl++;

//write rows value
for ($i=0;$i<count($dt->rows());$i++) {
	$col_ascii = 65;
	for ($j=0;$j<count($dt->columns());$j++) {
		$var->setCellValue(chr($col_ascii).($i+$rowxl), $dt->data($i,$j));
		$col_ascii++;
	}
}

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename=datatable.xls');
header('Cache-Control: max-age=0');

//create excel file and save
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

One thing to remember is to write include_once or require_once before session_start() because error ‘The script tried to execute a method or access a property of an incomplete object’ can occur.

Also check session value whether it is still available or not. If it is not available then go back to previous page or just exit with a message.

Export Data To Pdf

I am using FPDF to create PDF file via PHP. Visit: http://www.fpdf.org

include_once "datatable.php";
include_once "../fpdf17/fpdf.php";
session_start();

class PDF extends FPDF
{
	function addTable($dt)
	{
		// Header		
		foreach ($dt->columns() as $col) {
			//prints a cell
			$this->Cell(40,7,$col,1);
		}
		$this->Ln();
		
		// Data		
		foreach ($dt->rows() as $row) {			
			foreach ($row as $col) {
				//prints a cell
				$this->Cell(40,6,$col,1);
			}
			$this->Ln();
		}
	}

}


$dt = $_SESSION["sess_dt"];
if ($_SESSION["sess_dt"]=="") {
	die("Session is empty");
}

$pdf = new PDF();
// Column headings
$pdf->SetFont('Arial','',14);
$pdf->AddPage();
$pdf->addTable($dt);
$pdf->Output();

Quite simple, isn’t it? 🙂

Cheers,
Agung Gugiaji

Advertisements

One response to “Useful PHP DataTable Class Example To Store Objects including Recordset

  1. Nice and detailed tutorial! Thank you very much for it!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s