Some order of operations improvements and optimizations.
Assuming the database is correctly storing the entire contents of the LOB data.
Output buffering using ob_start, ob_clean, and ob_end_flush will provide more control over the desired response content from your script. This will help to alleviate erroneous spaces or emitted warnings from being included in the binary output.
Additionally this allows for you to control which header data to send in the response.
There's no need to use while($row = $result->fetch_assoc()) since the response from the database should contain the entirety of the single LONGBLOB data row.
Using mysqli_stmt::bind_result and mysqli_stmt::fetch will reduce some overhead caused by fetching into an associative array, since its only needed to retrieve the content. mysqli_stmt::fetch will return NULL if there are no results/data or false on error.
I also suggest using prepared statements, to prevent SQL injections and filter_var to ensure the user supplied input is of the expected data type and is valid.
Using content-disposition: inline will ask the browser to attempt to load it, if the browser can understand the content-type, otherwise it will download it.
Lastly you do not need to end your code with ?>, which can cause unintended behavior, instead just use exit;. It's best to exclude the closing tag in your PHP script files unless you are transitioning from PHP to plain text or markup in the same file.
I tested the below against my MySQL database table that also uses LONGBLOB to store PDF files and is functioning as desired.
<?php /*line 1*/
ob_start(); //start output buffering immediately
$conn = new mysqli('localhost','user','','db');
if (mysqli_connect_errno()) {
exit;
}
$tpurchase_id = filter_var($_GET['tpurchase_id'], FILTER_VALIDATE_INT);
$stmt = $conn->prepare('SELECT tp.content
FROM temp_purchase AS tp
WHERE tp.tpurchase_id = ?
AND tp.content > ""'); //ensure content is not empty
if ($stmt && false !== $tpurchase_id) {
$stmt->bind_param('i', $tpurchase_id);
$stmt->execute();
$stmt->bind_result($content);
if ($stmt->fetch()) {
//a record was found, change to a PDF file
ob_clean(); //clear the buffer
header('content-type: application/pdf');
header('content-disposition: inline; filename="Test.pdf"');
echo $content;
ob_end_flush(); //output only the buffered content to the client
}
$stmt->close();
unset($content, $stmt);
}
$conn->close(); //always close the connection
while (ob_get_level() > 0) {
ob_end_clean(); //remove everything else from the buffer
}
exit;
This will result in only the headers and content response being sent to the client, otherwise if a result from the database is not found, a blank plain/text response is sent.
The above script can then be used as the source for your inline object.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8"/>
</head>
<body>
<object data="/path/to/above_script.php?tpurchase_id=123" type="application/pdf" style="height:200px;width:60%"></object>
</body>
</html>
Aside from the above, there other points that can cause issues, which we are currently not aware of.
- Headers added or modified by the webserver (apache, nginx, IIS, etc).
- Upload form or PHP processing script modifying or not sending complete
LOB data to the database.
- Database truncating or altering the
LOB data.
To use the above PHP script for displaying inline object(s). There's no need for output buffering. However you would need to swap out base64_decode in favor of using base64_encode. Decode takes a base64 encoded string and converts it to the original format. Where you actually want to take the binary data from the database and convert it to a base64 encoded string for the browser to later decode. If the file content has already been base64_encode'd by the upload processing script, neither base64_encode or base64_decode is needed.
Tested the below and is functioning as desired.
<?php /*line 1*/
$conn = new mysqli('localhost','user','','db');
if (mysqli_connect_errno()) {
exit;
}
$tpurchase_id = filter_var($_GET['tpurchase_id'], FILTER_VALIDATE_INT);
$stmt = $conn->prepare('SELECT tp.content
FROM temp_purchase AS tp
WHERE tp.tpurchase_id = ?
AND tp.content > ""'); //ensure content is not empty
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8"/>
</head>
<body>
<?php
if ($stmt && false !== $tpurchase_id) {
$stmt->bind_param('i', $tpurchase_id);
$stmt->execute();
$stmt->bind_result($content);
if ($stmt->fetch()) { ?>
<object data="data:application/pdf;base64,<?php echo base64_encode($content); ?>" type="application/pdf" style="height:200px;width:60%"></object>
<?php }
$stmt->close();
unset($content, $stmt);
}
$conn->close();
?>
</body>
</html>
For the purposes of retrieving multiple documents, you can optionally change if ($stmt->fetch()) in favor of using while($stmt->fetch())
Upload processor Suggestion
Assuming you're using the code in your question "Auto submit is not posting data to database" for the file uploading, I highly recommend that you rewrite the upload processor using current standards/best-practices, which will also make your upload processor compatible with this answer.
Using addslashes or other escaping techniques can cause issues with the resulting LOB data stored in the database. Which I surmise is the cause for the complications you are experiencing now.
You should also take into consideration the max packet size that is used by your PHP and database environment, that limits the data size that can be sent or received by your application, which can lead to truncated LOB data. Because of the packet size limits, it is recommended that you use send_long_data to prevent issues with your application transmitting the LOB data.
upload-form.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8"/>
</head>
<body>
<form id="target" method="post" enctype="multipart/form-data" name="frmImage" class="frmImageUpload" action="./post.php">
<input type="file" name="userfile" id="userfile" class="userfile"/>
</form>
</body>
</html>
post.php
<?php
$conn = new mysqli('localhost','user','','db');
if (mysqli_connect_errno()) {
exit;
}
if (!session_id()) {
session_start();
}
//never trust data from GLOBALS
$user_id = filter_var($_SESSION['user_id'], FILTER_VALIDATE_INT);
if (false === $user_id ||
!isset($_FILES) ||
!array_key_exists('userfile', $_FILES) ||
UPLOAD_ERR_OK !== $_FILES['userfile']['error'] ||
$_FILES['userfile']['size'] <= 0 ||
!is_uploaded_file($_FILES['userfile']['tmp_name'])
) {
//invalid user or file upload
exit;
}
//params = { 0: user_id, 1: content }
$stmt = $conn->prepare('INSERT INTO temp (user_id, content) VALUES (?, ?)');
if ($stmt) {
//bind default value as NULL
$null = null;
$stmt->bind_param('ib', $user_id, $null);
//max packet size limits can lead to partial file data being inserted
$fp = new SplFileObject($_FILES['userfile']['tmp_name'], 'rb', false);
while (!$fp->eof()) {
//use send_long_data to send the file data in chunks
//be sure the first argument matches the param index for the LOB data column
$stmt->send_long_data(1, $fp->fread(2048));
}
unset($fp);
$stmt->execute();
$stmt->close();
}
$conn->close();
As a personal recommendation; over the years I have come to discover that storing LOB data within the database has caused some serious issues. While it does increase the portability and ease of file management within applications. It has greatly hindered data recovery and backup, by significantly increasing the amount of I/O time needed to recover the database and hard drive RAID integrity. Also when used with other data, significantly increases query and maintenance times with the database. Forcing us to migrate from SELECT * to explicitly avoid the LOB column data or skip the tables for optimization or re-indexing. Lastly it also prevented client-side caching without creating specific RESTful URLs to serve the files. Overall it became much more trouble than was worth the effort to store the LOB data. I suggest using your web server to store the physical file(s), and use the database to store the relative path to the physical file, where PHP manages the absolute paths to the physical file(s) for viewing/manipulation. For example when creating thumbnails that can be cached and served from a publicly accessible location.
