How do we write PHP code to store a file as blob to SQL database?

[bakertaylor28]

I've tried the following code, which executes without error, but yet I see nothing in phpMyAdmin to reflect the inserted data:

<?php
// Require files for server-side includes for fpdf library, functions script, and database connection info.
require $_SERVER['DOCUMENT_ROOT']."/scripts/fpdf/fpdf.php";
require $_SERVER['DOCUMENT_ROOT']."/scripts/functions.php";
require $_SERVER['DOCUMENT_ROOT']."/scripts/connect.php";
//dbinfo is a function defined in functions.php which returns the database connection varriables 
// for $host, $user, $pass, and $dbname. 
dbinfo();
//Connect to database.
$con = mysqli_connect($host, $user, $pass, $dbname);
//check for database connection errors.
if ( mysqli_connect_errno()) {
die('Failed to connect to SQL:' . mysqli_connect_error());
}
//Set PHP vars to write to database.
$postusername = 'testusername';
$postip = getIP();
$postdate = '02/27/21';
$dbfilename = 'test.pdf';
$mime = 'pdf';
$pdffile = fopen($_SERVER['DOCUMENT_ROOT']."/pdf/file.pdf", "rb");
$postcomments = 'insert comments here';
//Prepare and Execute SQL statement.
$stmt = $con->prepare('INSERT INTO pdf (username, ip, date, filename, mime,file, comments) VALUES (?, ?, ?, ?, ?, ?, ?)');
$stmt->bind_param("sssssbs", $postusername, $postip, $postdate, $dbfilename, $mime, $pdffile, $comments);
$stmt->execute();
//Close Database connection.
$stmt->close();
$con->close();
// Give Feedback to User and Exit this script.
echo 'write to database sucessful';
exit;
?>
 

is this code correct, or should this be done differently?

[Niheel]

Try using

$file = file_get_contents($_SERVER['DOCUMENT_ROOT']."/pdf/file.pdf")

Use the $file value in the insert SQL statment.

instead of

$pdffile = fopen($_SERVER['DOCUMENT_ROOT']."/pdf/file.pdf", "rb");

also if you go the fopen route, you are missing the fread & fclose

$filename = "/usr/local/something.pdf";
$handle = fopen($filename, "rb");
$contents = fread($handle, filesize($filename));
fclose($handle);

[bakertaylor28]

yes but then how do you pass the file_get_contents( ) to the SQL statement? or would file_get_contents automatically pass to SQL?

It would seem we would effectively need something along the premises of:

$var = file_get_contents($_SERVER['DOCUMENT_ROOT']."/pdf/file.pdf")
.....
INSERT INTO table foo, foo, blob, foo VALUES ( $foo, $foo, $var, $foo ...); 
 

[Niheel]

Yes, that's how you would do it.

Assign it to the $var variable and use $var in the SQL statement. Just as you've done.

I've modified the example, thanks for pointing that out.