This guide demonstrates how to create a PHP application that fetches data from a PostgreSQL database and generates a PDF file. We will use the TCPDF library, a powerful and open-source solution for PDF generation in PHP.
Step 1: Prerequisites
PHP Version: Ensure PHP 7.4 or higher is installed.
Database: PostgreSQL database with sample data.
Composer: Required for installing the TCPDF library.
Web Server: Apache or Nginx.
Step 2: Set Up the PostgreSQL Database
Create a PostgreSQL database and table. Use the following SQL script to create sample data:
CREATE DATABASE tutorial_db;
\c tutorial_db
CREATE TABLE students (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
course VARCHAR(100) NOT NULL
);
INSERT INTO students (name, email, course) VALUES
('John Doe', 'john@example.com', 'Computer Science'),
('Jane Smith', 'jane@example.com', 'Information Technology'),
('Alice Johnson', 'alice@example.com', 'Mathematics');
Step 3: Install the TCPDF Library
Install the TCPDF library using Composer:
composer require tecnickcom/tcpdf
Verify the vendor directory is created in your project.
Step 4: Create the PHP Script
Create a file named generate_pdf.php to fetch data from PostgreSQL and generate the PDF.
<?php
require 'vendor/autoload.php'; // Load TCPDF via Composer
use TCPDF;
// Database connection
$host = 'localhost';
$dbname = 'tutorial_db';
$username = 'postgres';
$password = 'yourpassword';
try {
$pdo = new PDO("pgsql:host=$host;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("Database connection failed: " . $e->getMessage());
}
// Fetch data from PostgreSQL
$stmt = $pdo->query("SELECT id, name, email, course FROM students");
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Create a new PDF document
$pdf = new TCPDF();
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('Your Name');
$pdf->SetTitle('Student List');
$pdf->SetSubject('List of Students');
$pdf->SetKeywords('PDF, students, PostgreSQL');
// Set default header and footer
$pdf->SetHeaderData('', 0, 'Student List', 'Generated using PHP and TCPDF');
$pdf->setFooterData();
$pdf->setPrintHeader(true);
$pdf->setPrintFooter(true);
// Set default font
$pdf->SetFont('helvetica', '', 12);
$pdf->AddPage();
// Add content to the PDF
$html = '<h1>Student List</h1>';
$html .= '<table border="1" cellspacing="0" cellpadding="5">
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Course</th>
</tr>';
foreach ($data as $row) {
$html .= '<tr>
<td>' . $row['id'] . '</td>
<td>' . $row['name'] . '</td>
<td>' . $row['email'] . '</td>
<td>' . $row['course'] . '</td>
</tr>';
}
$html .= '</table>';
// Write HTML content to PDF
$pdf->writeHTML($html, true, false, true, false, '');
// Output PDF to browser
$pdf->Output('students.pdf', 'I'); // Inline display
?>
Step 5: Create a Trigger Form
Create a simple frontend to trigger the PDF generation script.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Generate PDF</title>
</head>
<body>
<h1>Generate PDF from PostgreSQL</h1>
<form action="generate_pdf.php" method="post">
<button type="submit">Download PDF</button>
</form>
</body>
</html>
Step 6: Test the Application
Open the frontend (index.html) in your browser.
Click the "Download PDF" button.
The PDF should display the student data in tabular format.
Enhancements
Styling the Table: Add CSS styles for better table appearance in the PDF:
$html .= '<style>
table {
width: 100%;
border-collapse: collapse;
}
th, td {
border: 1px solid #000;
padding: 5px;
text-align: left;
}
th {
background-color: #f2f2f2;
}
</style>';
Dynamic Filtering: Modify the SQL query to allow filters based on user input (e.g., by course or date).
Error Handling: Ensure robust error handling for database connections and PDF generation.
Hope this is helpful, and I apologize if there are any inaccuracies in the information provided.
Post a Comment for "Generate PDF from PostgreSQL Data Using PHP and TCPDF: Complete Guide"