Inserting and Retrieving File into MySQL Database

Info.Tech, PHP, SQL: Structured Query Language, Web Development Add comments

Today,  I encounter a task to save the file into the database and its been a while that I have been saving files into the database method. I usually save just the path and filename. So this is just recall… 🙂

create table pdf_files (
	id bigint(20) unsigned not null auto_increment primary key,
	filename varchar(64),
	file_path varchar(64),
	binfile blob,
	created datetime
);

There’s a lot of method out there, but here’s my way of inserting the file to the database.

$path = "public/pdf/";
$file = "test.pdf";

$fileHandle = fopen($path.$file, "r");
$fileContent = fread($fileHandle, filesize($path.$file));
$fileContent = addslashes($fileContent);
fclose($fileHandle);
mysql_query("INSERT INTO pdf_files (filename,file_path,binfile,created) VALUES ('$file','$path','$fileContent',NOW())");

You can also use LOAD_FILE(”/path/filename.xxx’) in MySQL to save the file in the database.. 🙂

Again, there’s a lot of way… and this is my way how to retrieve the file…

$res = mysql_query(sprintf("SELECT filename,file_path,binfile FROM pdf_files WHERE id = %d", $xid));
if (mysql_num_rows($res) > 0) {
    $row = mysql_fetch_array($res);
    $bin_data = $row['binfile'];
    $filename_target = $row['file_path'].md5($row['filename'].'_'.time()).'.pdf';
    file_put_contents($filename_target, $bin_data);
	echo '<script>
            location.href="/'.$filename_target.'";
          </script>';
} else {
	echo 'File not found!';
	die();
}

And another way, force-download…

$res = mysql_query(sprintf("SELECT filename,file_path,binfile FROM pdf_files WHERE id = %d", $xid));
if (mysql_num_rows($res) > 0) {
    $row = mysql_fetch_array($res);
    $bin_data = $row['binfile'];
    $filename_target = $row['file_path'].md5($row['filename'].'_'.time()).'.pdf';
    file_put_contents($filename_target, $bin_data);
	// let the user download the file...
	header('Content-Description: File Transfer');
    header('Content-Type: application/force-download');
    header('Content-Disposition: attachment; filename='.basename($filename_target));
    header('Content-Transfer-Encoding: binary');
    header('Expires: 0');
    header('Cache-Control: must-revalidate');
    header('Pragma: public');
    header('Content-Length: ' . filesize($filename_target));
    ob_clean();
    flush();
    readfile($filename_target);
    exit;
} else {
	echo 'File not found!';
	die();
}

Hope this will help you… Cheers!




Comments are closed.

WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Log in