Table with MySQL

This class allows to output a table whose content comes from a MySQL query

Informations

Author: Olivier
License: 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).

To output the table:

function Table(string query [, array prop])

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 { var $ProcessingTable=false; var $aCols=array(); var $TableX; var $HeaderColor; var $RowColors; var $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($query, $prop=array()) { //Issue query $res=mysql_query($query) or die('Error: '.mysql_error()."<BR>Query: $query"); //Add all columns if none was specified if(count($this->aCols)==0) { $nb=mysql_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(mysql_field_name($res, $col['f'])); } } //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=mysql_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 output parent::Header(); } } //Connect to database mysql_connect('server', 'login', 'password'); mysql_select_db('db'); $pdf=new PDF(); $pdf->AddPage(); //First table: put all columns automatically $pdf->Table('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('select name, format(pop, 0) as pop, rank from country order by rank limit 0, 10', $prop); $pdf->Output(); ?>
View the result here.

Download

ZIP | TGZ

Quelle

http://www.fpdf.org/en/script/script14.php, zuletzt abgerufen 02.10.2016 13:03