FPDF lib to create PDF for data with multiple pages

Question

This code works perfectly while creating a PDF for data that only fits on the first page. However, on getting on the second page everything disorganizes and and can not create proper PDFs. Even the data on the first page disorganizes.

Anyone help me sort this such that any data from page two leads to proper pdfs. Thank you.

<?php
ob_start();
require('fpdf/fpdf.php');

//database connection

//Select the Products you want to show in your PDF file
$result= mysqli_query($conn, "SELECT * FROM customer");

$number_of_products = mysqli_num_rows($result);

//Initialize the  columns and the total
$column_id = "";
$column_name = "";
$column_trans_id = "";

//For each row, add the field to the corresponding column
while($row = mysqli_fetch_array($result))
{   
    $id = $row["id"];
    $name = $row["name"];
    $trans_id = substr($row["trans_id"],0,20);


    $column_id = $column_id.$id."\n";
    $column_name = $column_name.$name."\n";
    $column_trans_id = $column_trans_id.$trans_id."\n";
}
mysqli_close();

//Create a new PDF file
$pdf=new FPDF();
$pdf->AddPage();

//Fields Name position
$Y_Fields_Name_position = 20;
//Table position, under Fields Name
$Y_Table_Position = 26;

//First create each Field Name
//Gray color filling each Field Name box
$pdf->SetFillColor(232,232,232);
//Bold Font for Field Name
$pdf->SetFont('Arial','B',8);
$pdf->SetY($Y_Fields_Name_position);
$pdf->SetX(0);
$pdf->Cell(10,6,'ID',1,0,'L',1);
$pdf->SetX(10);
$pdf->Cell(30,6,'NAME',1,0,'L',1);
$pdf->SetX(40);
$pdf->Cell(20,6,'TRASACTION ID',1,0,'L',1);
$pdf->SetX(60);

$pdf->Ln();

//Now show the  columns
$pdf->SetFont('Arial','',8);
$pdf->SetY($Y_Table_Position);
$pdf->SetX(0);
$pdf->MultiCell(10,6,$column_id,1);
$pdf->SetY($Y_Table_Position);
$pdf->SetX(10);
$pdf->MultiCell(30,6,$column_name,1);
$pdf->SetY($Y_Table_Position);
$pdf->SetX(40);
$pdf->MultiCell(20,6,$column_trans_id,1);
$pdf->SetY($Y_Table_Position);
$pdf->SetX(60);

//Create lines (boxes) for each ROW (Product)
//If you don't use the following code, you don't create the lines separating each row
$i = 0;
$pdf->SetY($Y_Table_Position);
while ($i < $number_of_products)
{
    $pdf->SetX(0);
    $pdf->MultiCell(60,6,'',1);
    $i = $i +1;
}
ob_end_clean();
$pdf->Output();
ob_end_flush();
?>

Answer 1

The problem with your code is that you are putting all the result rows from the database into multi-line strings, and creating only one cell per column, which is bad. The cells may extend to more than one page. When adding a cell, it will start on the page the previous cell extended to. While it is possible to go back to the page where the table started, this is not the preferred way of outputting tables in FPDF.

A more common approach is to add a set of cells for each result rows you got from the database. This saves you the second iteration for painting the cell borders, and a lot of re-positioning.

<?php
require('fpdf/fpdf.php');

// Connect to database...

// Define your columns like so:
$columns = array(array("name" => "id",             "width" => 10),
                 array("name" => "name",           "width" => 30),
                 array("name" => "transaction_id", "width" => 20));

$pdf = new FPDF();
$pdf->AddPage();

// Table header
$pdf->SetFillColor(232, 232, 232);
$pdf->SetFont('Arial', 'B', 8);
foreach ($columns as $column)
{
    $pdf->Cell($column['width'], 6, strtoupper($column['name']), 1, 0, 'L', 1);
}
$pdf->Ln();

// Table rows
$pdf->SetFont('Arial', '', 8);
$result = mysqli_query($conn, "SELECT id, name, transaction_id FROM customers");
while ($row = mysqli_fetch_assoc($result))
{
    foreach ($columns as $column)
    {
        $pdf->Cell($column['width'], 6, $row[$column['name']], 1);
    }
    $pdf->Ln();
}

// Clean up
mysqli_free_result($result);
mysqli_close($conn);

$pdf->Output();