Go from MySQL database to PDF report in just three steps: follow this tutorial to (1) create a database, (2) design a dynamic PDF report, and (3) publish your PDF report to the web.
For this tutorial, we use Five, an application development environment, that gives you a built-in MySQL database and a PDF reports generator. Make sure to sign up for free to follow along.
Generating a PDF report from data stored in a MySQL database is a common development task. Traditional SQL admin tools or GUIs, however, do not support PDF report generation.
Five is a rapid application development environment that supports database creation, SQL queries, and PDF report generation (as well as many other useful features to build a web app such as forms, charts, dashboards, etc.).
Let’s explore how we can go from database to PDF reports in a few simple steps.
Here is an example of an online quotation generator developed in Five.
The quotation generator is running on an SQL database, created in Five. It generates a standardized quotation PDF, including bill-to and ship-to addresses, or quotation line items.
To use the report generator, click Quotations in the menu on the left, select an existing quotation or create a new one, and then click the Generate PDF button.
Building a web app that turns form inputs into a PDF report is very easy in Five.
Here’s a simple scenario: your business needs a new monthly inventory report (yet again). Let’s check out how quickly we can build this in Five.
First up, Five gives developers a dedicated MySQL database. This built-in MySQL can be created using Five’s Table Wizard or its visual Database Modeler. We will generate our report on top of this integrated MySQL database. Of course, a report can also be generated on any external, pre-existing relational database.
Here’s what our database looks like for this example: we have a single table that stores information about Warehouses.
To create this database, follow these steps:
If you are struggling with any of these steps, watch this tutorial:
Next, click on Visual > Reports.
You can now generate, adjust, customize, and manage MySQL PDF reports. Five lets you create reports on any data source, including its built-in MySQL database, any SQL query, or any external database connected to Five.
Inside Reports,
You are now inside a rich text editor that allows you to design PDF reports the same way you would design a Word document. On top of the text and document design features, the report generator also lets you select any data field from your database or data source.
For example, you can add a table to a report that contains each field that is stored inside the warehouse table. To do so,
You can use the Insert Fields functionality to add your database captions and records to the PDF document. Here’s how to set this up:
Let’s understand what we are doing here:
If you don’t enjoy working with the rich text editor, you can design your PDF report in HTML. To understand how this works, copy this piece of HTML into the code view of the report generator.
Open the code view by clicking on </> in the top left corner of the report generator.
<div><strong>Warehouse Report</strong></div>
<div><strong><br></strong></div>
<div><strong><br></strong></div>
<table style="width: 100%;">
<tbody>
<tr>
<td>Location</td>
<td>ID</td>
</tr>
<tr class="fiveReportHelper" contenteditable="false">
<td class="fiveReportHelper">{{#each @root.[Warehouse].[Records] as | Warehouse |}}</td>
</tr>
<tr>
<td>{{Warehouse.[Location]}}</td>
<td>{{Warehouse.[ID]}}</td>
</tr>
<tr>
<td>{{/each}}</td>
<td><br></td>
</tr>
</tbody>
</table>
<div><strong><br></strong></div>
<div><strong><br></strong></div>
Save everything by clicking the Tick marks.
IMPORTANT: Don’t forget to add your report to the menu of your application.
To preview your PDF report that now turns the data stored in your MySQL database into an easy-to-use, printable, and shareable PDF document, launch your application by clicking the ▶️ button in the top right corner of your development environment.
After a few seconds, the application will launch, and you’ll see your PDF report right in front of you!
You might be wondering why this report is empty. Well, we didn’t add any data to our database table.
But of course, we could have done so by importing a CSV, or by creating a form in our application for users to add data. You can quickly add a form to your app using Five’s Form Wizard, which can be found inside the development environment by clicking on Visual > Form Wizard.
Last, we could have spent more time on the formatting of our SQL report (and the look of the application surrounding it) by using the rich text editor or by customizing Five’s application themes.
Watch our Developer Advocate Pranoy explain the PDF report generator in more detail in our full product demo here (to jump straight to the SQL report writer start watching from 21m and 40s):
Sign up and get started with Five at no cost.