In Use MySQL BLOB column with PHP to store .pdf file, I covered an example of how to store a .pdf file in the actual database table using the MySQL BLOB datatype and PHP. Now that we have .pdf’s stored in the database, how do we display them in the browser? This blog post answers that exact question. Continue reading to see a working example using PHP…
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
There are 2 separate steps involved in displaying a MySQL BLOB .pdf file in the browser using PHP:
- Provide a list of all the current available .pdfs and Project Names (optional)
- Display a project’s .pdf file in some manner in the browser when the user chooses it.
All Current Projects
For the first step, we’ll provide all the available .pdf projects from the database, in a simple list for the user to choose from, using 3 files:
base.html.phpdisplay.html.phpprojects.php
For a refresher, shown below is the structure for the ‘pdf_projects’ table:
Now a look at each file in turn
File base.html.php is the foundation template file. Data is sent – or included – in other template files, then rendered in the browser from base.html.php using 2 variables: ‘$title’ and ‘$output’.
<title><?=$title?></title>
<?=$output?>
<script src=“https://code.jquery.com/jquery-3.5.1.slim.min.js” integrity=“sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj” crossorigin=“anonymous”></script>
<script src=“https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js” integrity=“sha384-9/reFTGAW83EW2RDu2S0VKaIzap3H66lZH81PoYlFhbGU+6BZp6G7niu735Sk7lN” crossorigin=“anonymous”></script>
<script src=“https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js” integrity=“sha384-B4gt1jrGC7Jh4AgTPSdUtOBvfO8shuf57BaghqFfPlYxofvL8/KUEfYiJOMMV+rV” crossorigin=“anonymous”></script>
Next, file display.html.php is a partial template file, providing all current .pdf project’s in an HTML unordered list (ul) using a PHP foreach loop:
<h5 class=“text-center”>Current PDF Projects</h5>
</div>
<div class=“d-flex justify-content-center align-self-center” style=“margin-top: 55px;”>
<ul>
<?php foreach ($records as $row):?>
<li><a href=“display.php?id=<?=$row[‘id’]?>” target=”_blank”><?=$row[‘project_name’]?></a></li>
<?php endforeach;?>
</ul>
</div>
Finally, file projects.php is the control script that queries the database for data and passes any retrieved data to the display.html.php partial template file:
include __DIR__.”/includes/DatabaseConnection.php”;
$sql = “SELECT id, project_name
FROM project_pdf
ORDER BY project_name ASC;”;
$result = $pdo->query($sql);
foreach ($result as $row) {
$records[] = [
‘id’ => $row[‘id’],
‘project_name’ => $row[‘project_name’]
];
}
$title = ‘Display PDF File’;
ob_start();
include __DIR__.”/templates/display.html.php”;
$output = ob_get_clean();
} catch (PDOException $e) {
echo ‘Database Error ‘. $e->getMessage(). ‘ in ‘. $e->getFile().
‘: ‘. $e->getLine();
}
include __DIR__.”/templates/base.html.php”;
PHP MySQL BLOB PDF: File Structure and Functionality
In file projects.php, a ‘$records’ array is created containing all returned data from this simple MySQL SELECT query, retrieving the ‘id’ and ‘project_name’ values in ASC order:
FROM project_pdf
ORDER BY project_name ASC;
If you recall I mentioned the 2 template file variables ‘$title’ and ‘$output’. Both of them are set to values in this file and then passed to the base template file at this line:
The ‘pdf_project’ table data is then rendered in the browser using the ‘$title’ and ‘$output’ variables through these 3 lines of PHP code:
include __DIR__.“/templates/display.html.php”;
$output = ob_get_clean();
Related recommended reading: The fantastic WPShout article The Many Uses of PHP Output Buffering in WordPress, is a great write-up on PHP’s output buffering. While the blog post is specific to WordPress, output buffering is a PHP thing and not limited to only WordPress. Nevertheless, the blog post is well-written and full of information, providing a great overview of output buffering. Do give this one a read.
The official PHP online documentation, Output Buffering Control, is a solid resource as well.
Credit Source
Another resource I have to mention, in which I first learned about this process of retrieving data and using output buffering to render data in template files, is from the fantastic book, PHP & MySQL: Novice To Ninja (not an affiliate link). I can not sing enough praise for this book. Most all of – if not all – of my foundational PHP learning is based on this book and I would recommend it to anyone.
PHP MySQL BLOB PDF Display in browser
Now that we have listed all current data from the MySQL table ‘project_pdf’, in the browser, we use another PHP script to display a chosen .pdf in a new browser tab once the user chooses it.
Does the file name display.php ring a bell for any of you? Look familiar? Savvy readers may have noticed it already.
display.php is the href attribute value for the HTML anchor tag in file display.html.php as shown in this code snippet:
Each rows’ ‘id’ is included in the href value via the ? and $row['id'] value.
Below are the contents of the file display.php:
include __DIR__.”/includes/DatabaseConnection.php”;
$id = htmlspecialchars($_GET[‘id’]);
$query = “SELECT `project_name`, `pdf_doc`
FROM `project_pdf`
WHERE `id` = :id;”;
$stmt = $pdo->prepare($query);
$stmt->bindValue(‘:id’, $id, PDO::PARAM_INT);
$stmt->bindColumn(1, $project_name);
$stmt->bindColumn(2, $pdf_doc, PDO::PARAM_LOB);
if ($stmt->execute() === FALSE) {
echo ‘Could not display pdf’;
} else {
$stmt->fetch(PDO::FETCH_BOUND);
header(“Content-type: application/pdf”);
header(‘Content-disposition: inline; filename=”‘.$project_name.’.pdf”‘);
header(‘Content-Transfer-Encoding: binary’);
header(‘Accept-Ranges: bytes’);
echo $pdf_doc;
}
} else {
header(‘location: projects.php’);
}
In this script, I use the $_SERVER['REQUEST_METHOD'] superglobal array and check if the HTTP method is ‘GET’ along with ensuring the ‘id’ value is set in the request URI string.
Then it’s a simple query against the MySQL database for the correct ‘pdf_doc’ based on the WHERE clause predicate for the ‘id’.
Again, since we are introducing user-supplied input – in the form of the $_GET['id'] variable from the URI – using prepared statements makes sense. I also, sanitize the ‘$id’ variable using the PHP htmlspecialchars() function.
In these 2 lines, I use the PDO bindColumn() method to basically assign the returned query results column values to PHP variables for further use:
$stmt->bindColumn(2, $pdf_doc, PDO::PARAM_LOB);
In order to use the ‘$project_name’ and ‘$pdf_doc’ variables in the subsequent code, setting the fetch mode to PDO::FETCH_BOUND in the $stmt->fetch(PDO::FETCH_BOUND) call works in conjunction with the bindColumn() method calls.
Here is the description of $stmt->fetch(PDO::FETCH_BOUND) from the PHP website documentation:
“PDO::FETCH_BOUND: returns TRUE and assigns the values of the columns in your result set to the PHP variables to which they were bound with the PDOStatement::bindColumn() method”
Therefore, we now have 2 variables (‘$project_name’ and ‘$pdf_doc’) we can use in the calls to the header() function used for displaying the actual pdf in the browser:
header(‘Content-disposition: inline; filename=”‘.$project_name.‘.pdf”‘);
header(‘Content-Transfer-Encoding: binary’);
header(‘Accept-Ranges: bytes’);
echo $pdf_doc;
Additional Information: Feel free to visit the below links for more information on several concepts covered in this section:
With all the code out of the way, let’s have a trial run and see that it all works by clicking on a project from the list, and seeing the .pdf displayed in a new browser tab.
When clicking on the ‘First Project’ link, its stored .pdf is displayed in a new browser window:
Additional PHP/MySQL Reading
Be sure and visit these other blog posts I have written on PHP and MySQL:
- PHP PDO lastInsertId() method with examples in MySQL
- Beginning Perspective on PHP Arrays
- Sorting associative arrays in PHP with array_multisort() – New learning
- Dynamic HTML drop-down with PHP and MySQL
I’ll be the first to admit this is a working example (obviously). However, I won’t necessarily say it is the only way to display a MySQL BLOB pdf file in the browser. Or the best way, as I am still learning PHP. As always, I welcome tips, suggestions, best practices, and any corrections in the comments below.
If you see any corrections I should make in the code, please let me know in the comments.
Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.
The post PHP MySQL BLOB PDF: Display in Browser appeared first on Digital Owl’s Prose.
