Need Programming Assignment Help?

Get Help with Programming Assignment in Java, C/C++, Dot Net, PHP, Database and UML Diagrams

MySQL Report generation in PHP

MySQL Report generation in PHP Programming

MySQL Report generation is a PHP class that Generates customizable HTML reports with data from the result set of any MySQL SELECT query.

<?php 
define('FPDF_FONTPATH','yourdirectorypath/FPDF/font/'); 
require('yourdirectorypath/FPDF/fpdf.php'); 
class PDF extends FPDF {

var $tablewidths; 
var $headerset; 
var $footerset;

function _beginpage($orientation) { 
 $this->page++; 
 if(!$this->pages[$this->page]) // solved the problem of overwriting a page, if it already exists 
 $this->pages[$this->page]=''; 
 $this->state=2; 
 $this->x=$this->lMargin; 
 $this->y=$this->tMargin; 
 $this->lasth=0; 
 $this->FontFamily=''; 
 //Page orientation 
 if(!$orientation) 
 $orientation=$this->DefOrientation; 
 else 
 { 
 $orientation=strtoupper($orientation{0}); 
 if($orientation!=$this->DefOrientation) 
 $this->OrientationChanges[$this->page]=true; 
 } 
 if($orientation!=$this->CurOrientation) 
 { 
 //Change orientation 
 if($orientation=='P') 
 { 
 $this->wPt=$this->fwPt; 
 $this->hPt=$this->fhPt; 
 $this->w=$this->fw; 
 $this->h=$this->fh; 
 } 
 else 
 { 
 $this->wPt=$this->fhPt; 
 $this->hPt=$this->fwPt; 
 $this->w=$this->fh; 
 $this->h=$this->fw; 
 } 
 $this->PageBreakTrigger=$this->h-$this->bMargin; 
 $this->CurOrientation=$orientation; 
 } 
}

function Header() 
{ 
 global $maxY;

// Check if header for this page already exists 
 if(!$this->headerset[$this->page]) {

foreach($this->tablewidths as $width) { 
 $fullwidth += $width; 
 } 
 $this->SetY(($this->tMargin) - ($this->FontSizePt/$this->k)*2); 
 $this->cellFontSize = $this->FontSizePt ; 
 $this->SetFont('Arial','',( ( $this->titleFontSize) ? $this->titleFontSize : $this- 
>FontSizePt )); 
 $this->Cell(0,$this->FontSizePt,$this->titleText,0,1,'C'); 
 $l = ($this->lMargin); 
 $this->SetFont('Arial','',$this->cellFontSize); 
 foreach($this->colTitles as $col => $txt) { 
 $this->SetXY($l,($this->tMargin)); 
 $this->MultiCell($this->tablewidths[$col], $this->FontSizePt,$txt); 
 $l += $this->tablewidths[$col] ; 
 $maxY = ($maxY < $this->getY()) ? $this->getY() : $maxY ; 
 } 
 $this->SetXY($this->lMargin,$this->tMargin); 
 $this->setFillColor(200,200,200); 
 $l = ($this->lMargin); 
 foreach($this->colTitles as $col => $txt) { 
 $this->SetXY($l,$this->tMargin); 
 $this->cell($this->tablewidths[$col],$maxY-($this->tMargin),'',1,0,'L',1); 
 $this->SetXY($l,$this->tMargin); 
 $this->MultiCell($this->tablewidths[$col],$this->FontSizePt,$txt,0,'C'); 
 $l += $this->tablewidths[$col]; 
 } 
 $this->setFillColor(255,255,255); 
 // set headerset 
 $this->headerset[$this->page] = 1; 
 }

$this->SetY($maxY); 
}

function Footer() { 
 // Check if footer for this page already exists 
 if(!$this->footerset[$this->page]) { 
 $this->SetY(-15); 
 //Page number 
 $this->Cell(0,10,'Page '.$this->PageNo().'/{nb}',0,0,'C'); 
 // set footerset 
 $this->footerset[$this->page] = 1; 
 } 
}

function morepagestable($lineheight=8) { 
 // some things to set and 'remember' 
 $l = $this->lMargin; 
 $startheight = $h = $this->GetY(); 
 $startpage = $currpage = $this->page;

// calculate the whole width 
 foreach($this->tablewidths as $width) { 
 $fullwidth += $width; 
 }

// Now let's start to write the table 
 $row = 0; 
 while($data=mysql_fetch_row($this->results)) { 
 $this->page = $currpage; 
 // write the horizontal borders 
 $this->Line($l,$h,$fullwidth+$l,$h); 
 // write the content and remember the height of the highest col 
 foreach($data as $col => $txt) {

$this->page = $currpage; 
 $this->SetXY($l,$h); 
 $this->MultiCell($this->tablewidths[$col],$lineheight,$txt,0,$this->colAlign[$col]);

$l += $this->tablewidths[$col];

if($tmpheight[$row.'-'.$this->page] < $this->GetY()) { 
 $tmpheight[$row.'-'.$this->page] = $this->GetY(); 
 } 
 if($this->page > $maxpage) 
 $maxpage = $this->page; 
 unset($data[$col]); 
 } 
 // get the height we were in the last used page 
 $h = $tmpheight[$row.'-'.$maxpage]; 
 // set the "pointer" to the left margin 
 $l = $this->lMargin; 
 // set the $currpage to the last page 
 $currpage = $maxpage; 
 unset($datas[$row]); 
 $row++ ; 
 } 
 // draw the borders 
 // we start adding a horizontal line on the last page 
 $this->page = $maxpage; 
 $this->Line($l,$h,$fullwidth+$l,$h); 
 // now we start at the top of the document and walk down 
 for($i = $startpage; $i <= $maxpage; $i++) { 
 $this->page = $i; 
 $l = $this->lMargin; 
 $t = ($i == $startpage) ? $startheight : $this->tMargin; 
 $lh = ($i == $maxpage) ? $h : $this->h-$this->bMargin; 
 $this->Line($l,$t,$l,$lh); 
 foreach($this->tablewidths as $width) { 
 $l += $width; 
 $this->Line($l,$t,$l,$lh); 
 } 
 } 
 // set it to the last page, if not it'll cause some problems 
 $this->page = $maxpage; 
}

// Leave this as it is unless you are sure what changes you are making. 
// $host is generally localhost unless you are trying to interact with Database 
// on another server. 
function connect($host='localhost',$username='',$passwd='',$db='') 
{ 
 $this->conn = mysql_connect($host,$username,$passwd) or die( mysql_error() ); 
 mysql_select_db($db,$this->conn) or die( mysql_error() ); 
 return true; 
}

function query($query){ 
 $this->results = mysql_query($query,$this->conn); 
 $this->numFields = mysql_num_fields($this->results); 
}

function mysql_report($query,$dump=false,$attr=array()){

foreach($attr as $key=>$val){ 
 $this->$key = $val ; 
 }

$this->query($query);

// if column widths not set 
 if(!isset($this->tablewidths)){

// starting col width 
 $this->sColWidth = (($this->w-$this->lMargin-$this->rMargin))/$this->numFields;

// loop through results header and set initial col widths/ titles/ alignment 
 // if a col title is less than the starting col width / reduce that column size 
 for($i=0;$i<$this->numFields;$i++){ 
 $stringWidth = $this->getstringwidth(mysql_field_name($this->results,$i)) + 6 ; 
 if( ($stringWidth) < $this->sColWidth){ 
 $colFits[$i] = $stringWidth ; 
 // set any column titles less than the start width to the column title width 
 } 
 $this->colTitles[$i] = mysql_field_name($this->results,$i) ; 
 switch (mysql_field_type($this->results,$i)){ 
 case 'int': 
 $this->colAlign[$i] = 'R'; 
 break; 
 default: 
 $this->colAlign[$i] = 'L'; 
 } 
 }

// loop through the data, any column whose contents is bigger that the col size is 
 // resized 
 while($row=mysql_fetch_row($this->results)){ 
 foreach($colFits as $key=>$val){ 
 $stringWidth = $this->getstringwidth($row[$key]) + 6 ; 
 if( ($stringWidth) > $this->sColWidth ){ 
 // any col where row is bigger than the start width is now discarded 
 unset($colFits[$key]); 
 }else{ 
 // if text is not bigger than the current column width setting enlarge the 
column 
 if( ($stringWidth) > $val ){ 
 $colFits[$key] = ($stringWidth) ; 
 } 
 } 
 } 
 }

foreach($colFits as $key=>$val){ 
 // set fitted columns to smallest size 
 $this->tablewidths[$key] = $val; 
 // to work out how much (if any) space has been freed up 
 $totAlreadyFitted += $val; 
 }

$surplus = (sizeof($colFits)*$this->sColWidth) - ($totAlreadyFitted); 
 for($i=0;$i<$this->numFields;$i++){ 
 if(!in_array($i,array_keys($colFits))){ 
 $this->tablewidths[$i] = $this->sColWidth + ($surplus/(($this->numFields)-sizeof 
($colFits))); 
 } 
 }

ksort($this->tablewidths);

if($dump){ 
 Header('Content-type: text/plain'); 
 for($i=0;$i<$this->numFields;$i++){ 
 if(strlen(mysql_field_name($this->results,$i))>$flength){ 
 $flength = strlen(mysql_field_name($this->results,$i)); 
 } 
 } 
 switch($this->k){ 
 case 72/25.4: 
 $unit = 'millimeters'; 
 break; 
 case 72/2.54: 
 $unit = 'centimeters'; 
 break; 
 case 72: 
 $unit = 'inches'; 
 break; 
 default: 
 $unit = 'points'; 
 } 
 print "All measurements in $unit\n\n"; 
 for($i=0;$i<$this->numFields;$i++){ 
 printf("%-{$flength}s : %-10s : %10f\n", 
 mysql_field_name($this->results,$i), 
 mysql_field_type($this->results,$i), 
 $this->tablewidths[$i] ); 
 } 
 print "\n\n"; 
 print "\$pdf->tablewidths=\n\tarray(\n\t\t"; 
 for($i=0;$i<$this->numFields;$i++){ 
 ($i<($this->numFields-1)) ? 
 print $this->tablewidths[$i].", /* ".mysql_field_name($this->results,$i)." */ 
\n\t\t": 
 print $this->tablewidths[$i]." /* ".mysql_field_name($this->results,$i)." */\n\t\t"; 
 } 
 print "\n\t);\n"; 
 exit; 
 }

} else { // end of if tablewidths not defined

for($i=0;$i<$this->numFields;$i++){ 
 $this->colTitles[$i] = mysql_field_name($this->results,$i) ; 
 switch (mysql_field_type($this->results,$i)){ 
 case 'int': 
 $this->colAlign[$i] = 'R'; 
 break; 
 default: 
 $this->colAlign[$i] = 'L'; 
 } 
 } 
 }

mysql_data_seek($this->results,0); 
 $this->Open(); 
 $this->setY($this->tMargin); 
 $this->AddPage(); 
 $this->morepagestable($this->FontSizePt); 
 $this->Output(); 
}

}

$pdf = new PDF('L','pt','A3'); 
$pdf->SetFont('Arial','',9); 
$pdf->AliasNbPages(); 
// The first Parameter is localhost again unless you are retrieving data from a different server. 
// The second parameter is your MySQL User ID. 
// The third parameter is your password for MySQL. In many cases these would be the same as your OS 
ID and Password. 
// The fourth parameter is the Database you'd like to run the report on. 
$pdf->connect('localhost','UserID','PassWord','Database'); 
// This is the title of the Report generated. 
$attr=array('titleFontSize'=>24,'titleText'=>'MySQL Report generation using FPDF'); 
// This is your query. It should be a 'SELECT' query. 
// Reports are run over 'SELECT' querires generally. 
$pdf->mysql_report("SELECT * FROM Table",false,$attr); 
?>