Table php to pdf

Export MySql database table to pdf using php

This tutorial focus on how we can use PHP to generate MySQL table to PDF. Along with simple PHP code we are also using FPDF library. You need to make sure that you Download Library first.

Make sure to modify the database details according to your settings. Also modify the path of fpdf.php file.

Table of Contents

Let us jump on to some of the major sections in the code so that it will be easier for your to do the relevant modification as per your requirement.

This is the view of my MySQL table data

mysql> select * from mydatabase_bank; +----+--------------+------------+-----------------------+---------------------+ | ID | Name | Date | Transaction Data | Failed Transactions | +----+--------------+------------+-----------------------+---------------------+ | 1 | John Cellar | 2022-01-01 | Pending_Transaction | No Failure Reported | | 2 | Mike luis | 2022-01-02 | Pending_Transaction | Failure Reported | | 3 | Aaron butler | 2022-01-02 | Transaction_Completed | Failure Reported | +----+--------------+------------+-----------------------+---------------------+ 3 rows in set (0.00 sec)

Let us break the code in major chunks

Connecting your MySQL database and selecting table

Once you extract the fpdf library put it in a folder, my folder name is fpdf_data and that is the header of the PHP file

Once the above step is done go to line number approximately 77 to configure your database credentials

$objConnect = mysqli_connect(«localhost»,»USERNAME»,»PASS») or die(«Error:Please check your database username & password»); $objDB = mysqli_select_db($objConnect,»customer»); $strSQL = «SELECT Name,Date,`Transaction Data`,`Failed Transactions` FROM mydatabase_bank»; $objQuery = mysqli_query($objConnect,$strSQL); $resultData = array(); for ($i=0;$i

$strSQL = “SELECT Name,Date, Transaction Data , Failed Transactions FROM mydatabase_bank”;

In the SELECt statement above we have the column names from which data needs to be displayed in the final PDF.
Note: This should be the same names that we pass to Cells in the BasicTable function discussed below.

Formatting the output

In line 43 we are describing a function named BasicTable

function BasicTable($header,$data) < $this->SetFillColor(255,0,0); $this->SetDrawColor(128,0,0); $w=array(30,15,20,10,10,10,10,10,15,15,15,15,15); //Header for($i=0;$iCell(40,10,$header[$i],1,0,'C',true); $this->Ln(); //Data foreach ($data as $eachResult) < //width $this->Cell(40,12,$eachResult["Name"],1); $this->Cell(40,12,$eachResult["Date"],1); $this->Cell(40,12,$eachResult["Transaction Data"],1); $this->Cell(40,12,$eachResult["Failed Transactions"],1); $this->Ln(); > >

this->Cell is defined like the following, More details can be found in this public documentation http://fpdf.org/en/doc/cell.htm

this->Cell (Width, Height, Text_To_Display, Border)

How to add Logo to the PDF

In line number 102 we are making a image call like below, in which mylogo.jpg is the logo that you are going to add to the header of the PDF.

More details and examples of adding logo and links can be found in this public documentation http://www.fpdf.org/en/doc/image.htm

How to add font family and size to PDF

The above function can be used to choose any font family and size of your choice. More details can be found on this public documentation http://www.fpdf.org/en/doc/addfont.htm

Add header / footer with some extra data / string to the PDF

Apart from data pulled from MySQL we can also use the below function to add some header footer static data.

function Header() < //Logo $name="Testing PDF Creation"; $this->SetFont('Arial','B',15); //Move to the right $this->Cell(80); //Title $this->Cell(20,40,"Data Generated For $name on ".date('d-m-Y'),0,0,'C'); $this->SetFont('Arial','B',9); $this->Cell(10,60,"Test Place 1",0,0,'C'); $this->Cell(-10,70,"Test Place 2",0,0,'C'); //Line break $this->Ln(20); >

//Page footer function Footer()

Here is the complete code

You can always go to this online reference manual http://www.fpdf.org/en/doc/ to take a broader look at all the functions available in fpdf .

SetFont('Arial','B',15); //Move to the right $this->Cell(80); //Title $this->Cell(20,40,"Data Generated For $name on ".date('d-m-Y'),0,0,'C'); $this->SetFont('Arial','B',9); $this->Cell(10,60,"Test Place 1",0,0,'C'); $this->Cell(-10,70,"Test Place 2",0,0,'C'); //Line break $this->Ln(20); > //Page footer function Footer() < >//Simple table function BasicTable($header,$data) < $this->SetFillColor(255,0,0); $this->SetDrawColor(128,0,0); $w=array(30,15,20,10,10,10,10,10,15,15,15,15,15); //Header for($i=0;$iCell(40,10,$header[$i],1,0,'C',true); $this->Ln(); //Data foreach ($data as $eachResult) < //width $this->Cell(40,12,$eachResult["Name"],1); $this->Cell(40,12,$eachResult["Date"],1); $this->Cell(40,12,$eachResult["Transaction Data"],1); $this->Cell(40,12,$eachResult["Failed Trasactions"],1); $this->Ln(); > > //Better table > $pdf=new PDF(); $header=array('Name','Date','Transaction_Data','Failed_Trasactions'); //Data loading //*** Load MySQL Data ***// $objConnect = mysqli_connect("localhost","USERNAME","PASS") or die("Error:Please check your database username & password"); $objDB = mysqli_select_db($objConnect,"customer"); $strSQL = "SELECT Name,Date,`Transaction Data`,`Failed Trasactions` FROM mydatabase_bank"; $objQuery = mysqli_query($objConnect,$strSQL); $resultData = array(); for ($i=0;$i //************************// $pdf->SetFont('Arial','',6); //*** Table 1 ***// $pdf->AddPage(); $pdf->Image('mylogo.jpg',80,8,33); $pdf->Ln(35); print_r($header); echo "
"; print_r($resultData); $pdf->BasicTable($header,$resultData); $t=time(); $r="_mistonline.in"; $name=$d."_".$t."_".$r.".pdf"; echo "
Data generated succesfully. Download it here DOWNLOAD"; $pdf->Output($name,"F");?>

This demo is for education purpose and it shows how the code works. It also displays array details along with variables that are being passed in to PDF .
Demo

If you are experiencing error related to FPDF error: Alpha channel not supported error message, Check this tutorial.

Источник

PDF Table by taking data from MySQL database

We will take records from our student database and then crate a PDF document by using the data. Records will be displayed in a tabular format.

Displaying Data from MySQL table in PDF document by using Cell with alternate background color

In above tutorial it is explained how to draw tables, we will use the same concepts to display data with column headers to show the records.

Connect to database,
Run SQL to collect records
Display in data inside a table and generate PDF document.

You can download the ZIP file containing all the above steps. Inside ZIP folder these files are used.

How to connect and collect the records from table

Read more about SELECT query here. You can read more on database connection using PHP PDO. The full code to generate PDF document with data from tables is here.

AddPage(); $width_cell=array(20,50,40,40,40); $pdf->SetFont('Arial','B',16); //Background color of header// $pdf->SetFillColor(193,229,252); // Header starts /// //First header column // $pdf->Cell($width_cell[0],10,'ID',1,0,'C',true); //Second header column// $pdf->Cell($width_cell[1],10,'NAME',1,0,'C',true); //Third header column// $pdf->Cell($width_cell[2],10,'CLASS',1,0,'C',true); //Fourth header column// $pdf->Cell($width_cell[3],10,'MARK',1,0,'C',true); //Third header column// $pdf->Cell($width_cell[4],10,'GENDER',1,1,'C',true); //// header ends /////// $pdf->SetFont('Arial','',14); //Background color of header// $pdf->SetFillColor(235,236,236); //to give alternate background fill color to rows// $fill=false; /// each record is one row /// foreach ($dbo->query($sql) as $row) < $pdf->Cell($width_cell[0],10,$row['id'],1,0,'C',$fill); $pdf->Cell($width_cell[1],10,$row['name'],1,0,'L',$fill); $pdf->Cell($width_cell[2],10,$row['class'],1,0,'C',$fill); $pdf->Cell($width_cell[3],10,$row['mark'],1,0,'C',$fill); $pdf->Cell($width_cell[4],10,$row['gender'],1,1,'C',$fill); //to give alternate background fill color to rows// $fill = !$fill; > /// end of records /// $pdf->Output(); ?> 

Download and Install script

Download and Install fpdf class from https://www.fpdf.org/
Keep a copy of fpdf.php file in the same directory
Keep the font directory inside in the same directory.

Use the SQL_dump.txt file to create student table in your MySQL database

Open config.php file to enter your MySQL login details.

Open index.php file to see the records in your browser ( Not PDF ).

Open index-pdf.php file to generate PDF document.

Open index1-pdf.php file to generate PDF document with link to breakup of marks.

Connecting database and executing Query

To manage data we have to connect to MySQL database and execute query to get our date. Here there are two ways to use PHP drivers to connect to MySQL and execute the functions for getting records.

You can download both the scripts inside the same Zip file. Inside MySQLI folder you can get same scripts with MySQLi connection. ( change the config.php file here also and place fpdf.php with font directory inside this folder)

Questions

1. How do you connect to a MySQL database using PHP to retrieve data for a PDF table?
2. What are the steps involved in fetching data from a MySQL database in PHP for generating a PDF table?
3. How do you use the FPDF library in PHP to create a table structure in a PDF document?
4. Can you customize the column widths and row heights of the PDF table using FPDF?
5. How do you populate the PDF table with data retrieved from a MySQL database using PHP?
6. What techniques can be used to handle large datasets and pagination when generating a PDF table from a MySQL database?
7. Can you apply different styles, such as font, color, and alignment, to the PDF table cells using FPDF?
8. How do you handle special characters or formatting in the data retrieved from the MySQL database while generating the PDF table?
9. Are there any performance considerations or optimizations to keep in mind when generating a PDF table from a MySQL database in PHP?
10. What are some best practices for generating visually appealing and well-structured PDF tables from MySQL data using FPDF in PHP?

plus2net.com

Источник

Генерация PDF из данных MySQL с использованием FPDF

В этой статье мы считаем данные из таблицы MySQL с помощью PHP. Затем используем библиотеку FPDF для генерации PDF.

Генерация PDF из табличных данных MySQL с использованием библиотеки FPDF и PHP

Есть таблица БД MySQL. Мы считаем эти данные в массив и передадим их в функцию FPDF для генерации кода.

runQuery("SELECT * FROM toy"); $header = $db_handle->runQuery("SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='blog_samples' AND `TABLE_NAME`='toy'"); require('fpdf/fpdf.php'); $pdf = new FPDF(); $pdf->AddPage(); $pdf->SetFont('Arial','B',12); foreach($header as $heading) < foreach($heading as $column_heading) $pdf->Cell(90,12,$column_heading,1); > foreach($result as $row) < $pdf->SetFont('Arial','',12); $pdf->Ln(); foreach($row as $column) $pdf->Cell(90,12,$column,1); > $pdf->Output(); ?>

DBController.php

Этот файл содержит функции для установления соединения с базой данных и считывания информации из нее. Функция runQuery() считывает данные из таблицы и возвращает результаты, пригодные для создания отчета в формате PDF.

conn = $this->connectDB(); if(!empty($this->conn)) < $this->selectDB(); > > function connectDB() < $conn = mysqli_connect($this->host,$this->user,$this->password,$this->database); return $conn; > function selectDB() < mysqli_select_db($this->conn, $this->database); > function runQuery($query) < $result = mysqli_query($this->conn, $query); while($row=mysqli_fetch_assoc($result)) < $resultset[] = $row; >if(!empty($resultset)) return $resultset; > function numRows($query) < $result = mysqli_query($this->conn, $query); $rowcount = mysqli_num_rows($result); return $rowcount; > > ?>

SQL-скрипт

Выполните приведенный ниже SQL-код для создания таблицы toy.

CREATE TABLE IF NOT EXISTS `toy` ( `Name` varchar(55) NOT NULL, `Type` varchar(55) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `toy` -- INSERT INTO `toy` (`Name`, `Type`) VALUES ('Ben 10 Watch', 'Battery Toys'), ('Angry Birds Gun', 'Mechanical Toys'), ('Remote Car', 'Remote Toys'), ('Uno Cards', 'Card Game'), ('Keyboard', 'Musical Toys'), ('Jigsaws', 'Board Game');

Генерация PDF-отчета из данных MySQL с использованием FPDF

На скриншоте, приведенном ниже, показан результат работы программы: выведенный список значений из базы данных с кнопкой «Generate PDF». При ее нажатии вызывается библиотека FPDF, которая используется для создания отчета в формате PDF.

Вадим Дворников автор-переводчик статьи « Generate PDF from MySQL Data using FPDF »

Пожалуйста, оставьте свои комментарии по текущей теме материала. Мы крайне благодарны вам за ваши комментарии, лайки, подписки, отклики, дизлайки!

Источник