Converting a MySQL Table to a PDF File

I have done some research into converting a MySQL table to a PDF file and I think I will eventually get this to work.
But if someone has already figured this out, I would be pleased to hear from you.

Mike

Hi @mdsmith1 Did you already implement this? One idea is to use the pdf exporter query type, as explored here

The pdf exporter is somewhat limited though. This blog post explores other ways to generate PDFs from Retool, such as a download button or using an external api :slightly_smiling_face:

Download button example:

Tess, I think this is probably all I need but I can't get it to work.

I am sending screen capture. I have asked it to print fields Field1, Field2, Fierld3 and Field4 and it just prints out these words as a header.

How does this work?

Mike

The PDF Content needs to be formatted as markdown, which is why I mentioned it can be very limited. There isn't much flexibility with styling and it can be tedious to map out the data you need properly.

Headers from table could be formatted like this:

const headers = [Object.keys({{ table1.data }}[0])];
const breaks = [headers[0].map(() => "---")];
const tableEntries = headers.concat(breaks);
return tableEntries.map((row) => "|" + row.join("|") + "|").join("\n");

Tess:

Yes this is complicated. I can get the .pdf file to download but it is not scrollable and does not print properly.
I have attached 3 screen shots to help you see what I am doing wrong.
Thanks
Mike

Hi there! What do you mean that it does not print properly?

For the utils.downloadPage, the pdf will only show what is visible on the Retool page. For example, if you download a table component, it will only show the results that are visible on the table component (the first page in a paginated table or the visible rows in a scrollable table).

For the pdf exporter, if you have a wider table that overflows off of the page, you may find this option to be too limited as far as formatting.

The blog's suggestion of using an external API may be best for working with tables!

Tess:
I was never able to get the ReTool PDF Feature to work properly but I found a good solution using PHP. It generates a CVS file which can be clicked on and immediately emailed to the client.
There are some width problems. Also I had to build the headers with code.
I can send it in if you think people would be interested.
Mike

1 Like

Oh wow, @mdsmith1 good to know! We'd love to see it! :sunglasses:

Tess:

Here is the code for converting a MySQL File "Letter03" to a CSV File. The file is called Export toCVS05.php.

I have shown a screen capture of the output file.

In my example I would route to http://doncasteroffice.com/Invoices01/ExportToCVS05.php

Note I had to build the MySQL Letter03 source file with code.

The end product is really nice. As shown in the screen capture, you have access to the full features of Excel, so if you would like to email the .CVS file to your customer you can do that using the Excel Screen. Or you may print it. You can also widen the columns or whatever.

Mike

 <?php
 $servername = "doncasteroffice.com";

 $username = "XXXX";
 $password = "XXXX";
 $dbname = "ccprocess";
 $db_con = mysqli_connect($servername, $username, $password, $dbname);
 
 $result = $db_con->query('SELECT * FROM Letter03');
 if (!$result) die('Could not fetch records');
 $num_fields = mysqli_num_fields($result);
 $headers = array();
 while ($fieldinfo = mysqli_fetch_field($result)) {
     $headers[] = $fieldinfo->name;
 }
 $fp = fopen('php://output', 'w');
 if ($fp && $result) {
     header('Content-Type: text/csv');
     header('Content-Disposition: attachment; filename="export.csv"');
     header('Pragma: no-cache');
     header('Expires: 0');
     fputcsv($fp, $headers);
     while ($row = $result->fetch_array(MYSQLI_NUM)) {
         fputcsv($fp, array_values($row));
     }
     die;
 }
 ?>

1 Like

Hi @mdsmith1 Thanks so much for taking the time to share all of these details!