C3rd

Inserting and Retrieving File into MySQL Database

Posted: 5 Feb 2013, 3:44am - Tuesday

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!

Back to list

Inserting and Retrieving File into MySQL Database

Search

Pages

Categories