Add-On Table with MySQL
Informations
Author:OlivierLicense: FPDF
Description
This class allows to output a table whose content comes from a MySQL query. The table header is automatically printed on top of each page.The method to add a column is:
AddCol([mixed field [, mixed width [, string caption [, string align]]]])field: field of the SQL query (either name or index). If -1, the column will represent
the field in the same position in the query (default value).width: width of the column. It can be a number giving the absolute value, or a string
of the form x% to request a percentage of the table width. If -1, the width equals the table
width divided by the number of columns (default value).caption: caption of the column. If empty, the capitalized name of the field will
be used (default value).align: alignment of the column content. Possible values are L, C and
R (default is L).
The method to output the table is:
Table(mysqli link, string query [, array prop])link: the MySQLi link.query: the SQL query.prop: associative array containing the table properties. Possible keys are:width: width of the table. Useful if you specify column widths by percentage. The
default value is the page width without the margins.align: alignment of the table in the page. Possible values are L, C
and R (default is C).padding: left and right margins used inside cells. Default value is 1mm.HeaderColor: background color for the table header (array of the three RGB components).color1: background color for odd rows.color2: background color for even rows.If no column has been defined when
Table() is called, columns corresponding to all the
fields of the query are added automatically.After the output of the table, all column definitions are cleared.
Source
<?php
require('fpdf.php');
class PDF_MySQL_Table extends FPDF
{
protected $ProcessingTable=false;
protected $aCols=array();
protected $TableX;
protected $HeaderColor;
protected $RowColors;
protected $ColorIndex;
function Header()
{
// Print the table header if necessary
if($this->ProcessingTable)
$this->TableHeader();
}
function TableHeader()
{
$this->SetFont('Arial', 'B', 12);
$this->SetX($this->TableX);
$fill=!empty($this->HeaderColor);
if($fill)
$this->SetFillColor($this->HeaderColor[0], $this->HeaderColor[1], $this->HeaderColor[2]);
foreach($this->aCols as $col)
$this->Cell($col['w'], 6, $col['c'], 1, 0, 'C', $fill);
$this->Ln();
}
function Row($data)
{
$this->SetX($this->TableX);
$ci=$this->ColorIndex;
$fill=!empty($this->RowColors[$ci]);
if($fill)
$this->SetFillColor($this->RowColors[$ci][0], $this->RowColors[$ci][1], $this->RowColors[$ci][2]);
foreach($this->aCols as $col)
$this->Cell($col['w'], 5, $data[$col['f']], 1, 0, $col['a'], $fill);
$this->Ln();
$this->ColorIndex=1-$ci;
}
function CalcWidths($width, $align)
{
// Compute the widths of the columns
$TableWidth=0;
foreach($this->aCols as $i=>$col)
{
$w=$col['w'];
if($w==-1)
$w=$width/count($this->aCols);
elseif(substr($w, -1)=='%')
$w=$w/100*$width;
$this->aCols[$i]['w']=$w;
$TableWidth+=$w;
}
// Compute the abscissa of the table
if($align=='C')
$this->TableX=max(($this->w-$TableWidth)/2, 0);
elseif($align=='R')
$this->TableX=max($this->w-$this->rMargin-$TableWidth, 0);
else
$this->TableX=$this->lMargin;
}
function AddCol($field=-1, $width=-1, $caption='', $align='L')
{
// Add a column to the table
if($field==-1)
$field=count($this->aCols);
$this->aCols[]=array('f'=>$field, 'c'=>$caption, 'w'=>$width, 'a'=>$align);
}
function Table($link, $query, $prop=array())
{
// Execute query
$res=mysqli_query($link, $query) or die('Error: '.mysqli_error($link)."<br>Query: $query");
// Add all columns if none was specified
if(count($this->aCols)==0)
{
$nb=mysqli_num_fields($res);
for($i=0;$i<$nb;$i++)
$this->AddCol();
}
// Retrieve column names when not specified
foreach($this->aCols as $i=>$col)
{
if($col['c']=='')
{
if(is_string($col['f']))
$this->aCols[$i]['c']=ucfirst($col['f']);
else
$this->aCols[$i]['c']=ucfirst(mysqli_fetch_field_direct($res, $col['f'])->name);
}
}
// Handle properties
if(!isset($prop['width']))
$prop['width']=0;
if($prop['width']==0)
$prop['width']=$this->w-$this->lMargin-$this->rMargin;
if(!isset($prop['align']))
$prop['align']='C';
if(!isset($prop['padding']))
$prop['padding']=$this->cMargin;
$cMargin=$this->cMargin;
$this->cMargin=$prop['padding'];
if(!isset($prop['HeaderColor']))
$prop['HeaderColor']=array();
$this->HeaderColor=$prop['HeaderColor'];
if(!isset($prop['color1']))
$prop['color1']=array();
if(!isset($prop['color2']))
$prop['color2']=array();
$this->RowColors=array($prop['color1'], $prop['color2']);
// Compute column widths
$this->CalcWidths($prop['width'], $prop['align']);
// Print header
$this->TableHeader();
// Print rows
$this->SetFont('Arial', '', 11);
$this->ColorIndex=0;
$this->ProcessingTable=true;
while($row=mysqli_fetch_array($res))
$this->Row($row);
$this->ProcessingTable=false;
$this->cMargin=$cMargin;
$this->aCols=array();
}
}
?>Example
This example creates two tables. The first one is automatically built from all the fields of the SQL query. The second one defines 3 columns with specific attributes (caption, alignment, width) and sets some table properties (colors and padding).<?php
require('mysql_table.php');
class PDF extends PDF_MySQL_Table
{
function Header()
{
// Title
$this->SetFont('Arial', '', 18);
$this->Cell(0, 6, 'World populations', 0, 1, 'C');
$this->Ln(10);
// Ensure table header is printed
parent::Header();
}
}
// Connect to database
$link = mysqli_connect('server', 'login', 'password', 'db');
$pdf = new PDF();
$pdf->AddPage();
// First table: output all columns
$pdf->Table($link, 'select * from country order by name');
$pdf->AddPage();
// Second table: specify 3 columns
$pdf->AddCol('rank', 20, '', 'C');
$pdf->AddCol('name', 40, 'Country');
$pdf->AddCol('pop', 40, 'Pop (2001)', 'R');
$prop = array('HeaderColor'=>array(255, 150, 100),
'color1'=>array(210, 245, 255),
'color2'=>array(255, 255, 210),
'padding'=>2);
$pdf->Table($link, 'select name, format(pop, 0) as pop, rank from country order by rank limit 0, 10', $prop);
$pdf->Output();
?>