jump to navigation

Three Lines of PHP-code to Fill Up HTML Tables with Mysql Database Results (by using PEAR’s HTML_Table_Matrix Modul) August 1, 2012

Posted by Tournas Dimitrios in PHP.
trackback

Form me on GitHubWhile a developer could  write the code manually , for displaying data into HTML tables  , his productivity decreases exponentially as the number of rows/columns of that table increases . Of-course , manually created code is error prone , difficult to debug and  re-factoring can be daunting (if we need similar functionality into another project ) . Fortunately the PHP-community has released many libraries that can be used as blueprints for common tasks . For example ,  Zend-Framework’s loosely coupled structure has the advantage for  allowing us to use these modules as separate  blueprints . The PEAR repository is also a good choice where we can find “blueprints” for our development needs .

Today’s article will use a module from  the PEAR repository , the HTML_Table_Matrix module .
This module is part of the HTML package from PEAR‘s  repository  (although this package is not maintained anymore , it can still be used ) . I have build a custom Class for auto-filling a HTML table with results from a Mysql table . Creating a table is simple as :

  • Including the custom Class (require_once(“HtmlTable.Class.php”) )
  • Instantiating a new HtmlTable Class (our custom Class ) and pass two arrays into it’s constructor .
    The first array defines the credentials of the database connection and the table-name (from which we will display it’s content ) .
    The second array defines  the column-names of the table (from which the content will be extracted and display into the HTML table)  .

A practical code snippet :

<!--?php-->

// Include the "HtmlTable.Class.php" file first
require_once("HtmlTable.Class.php") ;

/*
This array contains all required parameters .
While the order of these parameters is not important , an exception-error will be thrown if they are misspelled (also case-sensitive) .

*/
$config = array(
				"db_Host" => "localhost",
				"db_User" => "root" ,
				"db_Passwd" => "" ,
				"db_Name" => "test" ,
				"tbl_Name" => "customers" ,
				"tbl_ColumnOrder" => "cust_id"
				) ;
$columnNames = array( "cust_id" , "first_name" , "last_name" , "country") ;
  try {
$custom_Table = new HtmlTable($config , $columnNames) ;
unset($custom_Table) ;
    } catch (Exception $e) {
        echo 'Caught exception: ', $e->getMessage(),"";
    }

The only requirement is that PEAR’s HTML package is into our “lib” folder (this folder should also be part of PHP’s path ) . If you don’t feel comfortable with installing PEAR modules on your hosting server , I have bundled all necessarily modules into a folder . Just upload this folder into your server’s lib folder (pay attention that this folder is set into PHP’s include path ) . The full code is uploaded into a GITHUB repository . Clone the whole repository  , or download a zipped file .
The result of previous code will produce the following HTML table :

cust_id first_name last_name country
1 Joe Smith US
2 Masao Yasunori Japan
3 Hans Schwartz Germany
4 Alex Smith England
5 Tom Greenfield US
6 Jane Addington England
7 Alex Jones Canada

Here follows the code for the Custom Class : 

<?php
//File HtmlTable.Class.php
require_once("PEAR.php") ;
require_once("HTML/Table/Matrix.php");

class HtmlTable {
// Initialize Variables
private $columnMeta = array() ;
private$requestedColumnNames = array() ;
private $data = array() ;
private $db_Host ;
private $db_Name ;
private $db_User ;
private $db_Passwd ;
private $mysqli ;
private $tbl_Name ;
private $tbl_ColumnOrder ;
private $tbl_Border = 2 ;
private $tbl_CellPadding = 3 ;
private $tbl_CellSpacing = 3 ;
private $tbl_Limit = 10 ;
private $orderBy = "ASC" ;
private $available_options = array (
			 "db_Host" ,"db_Name" , "db_User" , "db_Passwd" ,
			 "tbl_Name" , "tbl_ColumnOrder" , "tbl_Border" , "tbl_CellPadding" ,
			"tbl_CellSpacing" , "tbl_Limit" , "orderBy" ) ;
/**
* Constructor
* @param array $config This array contains db-connection credentials and optional configuration options for the html-table (border , cellpadding , cellspacing  ... )
*@param array $columnNames This array defines the columns of the SQL-table that will be displayed
*/
   public function  __construct($config , $columnNames)
   {
	foreach ($config as $k => $v) {
	if (in_array("$k", $this->available_options)) {
	$this->{$k} = $v ;
		}else{
throw new Exception(
"The parameter -- <strong>$k</strong> -- is unknow .

 <strong>Available options are : </strong><br>
 <ul>
  <li>db_Host</li>
  <li>db_Name</li>
  <li>db_User</li>
  <li>db_Passwd</li>
  <li>tbl_Name</li>
  <li>tbl_ColumnOrder</li>
  <li>tbl_Border</li>
  <li>tbl_CellPadding</li>
  <li>tbl_CellSpacing</li>
   <li>tbl_Limit</li>
  <li>orderBy</li>
</ul>") ;
	   exit();
			}
		}
	foreach ($columnNames as $v) {
	array_push($this->requestedColumnNames , $v ) ;
			}
	 $this->db_Connect() ;
	 $this->returnTable() ;
   }// End of public function __construct
/**
* db_Connect
* Establishes a db-connection
*/
   private function db_Connect()
   {
 // Connect to db, run query, populate variables
	@$this->mysqli = new mysqli(
					$this->db_Host ,
					$this->db_User ,
					$this->db_Passwd ,
					$this->db_Name ) ;
	if (mysqli_connect_errno()) {
   throw new Exception('Could not Connect to database') ;
	   exit();
		}
   }// End of private function db_Connect

/**
* returnTable
* @return html-table
*/
   private function returnTable()
   {
   $qarguments = implode(",", $this->requestedColumnNames) ;
      $query = "SELECT $qarguments
                FROM  {$this->tbl_Name}
                ORDER BY {$this->tbl_ColumnOrder}  {$this->orderBy}
				LIMIT {$this->tbl_Limit} " ;
	if ($result = $this->mysqli->query($query)) {
   $rows    = $this->mysqli->affected_rows ;
   $columns = $this->mysqli->field_count ;
	    for($i=0 ; $i<$columns ; $i++) {
        $columnMeta = $result->fetch_field_direct($i) ;
        $columnNames[]  = "<strong>{$columnMeta->name}</strong>" ;
			}//End of for-loop
			$data = array() ;
	$data = array_merge($data, $columnNames);
		while($row = $result->fetch_row())  {
        $data = array_merge($data , $row) ;
			}//End of while-loop

	/**
     * @var HTML_Table_Matrix
     * Create the table and set properties - $rows + 1 needed for column headers
     */
	$table = new HTML_Table_Matrix(array(
					'border' => $this->tbl_Border ,
					'cellpadding' => $this->tbl_CellPadding ,
					'cellspacing' => $this->tbl_CellSpacing ,
					));
    $table->setData($data);
    $table->setTableSize($rows + 1 , $columns );

    /**
     * @var HTML_Table_Matrix_Filler
     * Create the filler, and render the table
     */
    $filler = HTML_Table_Matrix_Filler::factory('LRTB', $table);
    $table->accept($filler) ;
    print_r( $table->toHtml()) ;
			}// End of querying the database and rendering table
		}//End of public function returnTable
	}//End of Class

Happy coding 🙂

Advertisements

Comments»

No comments yet — be the first.

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

%d bloggers like this: