A
A
Angelxalfa2015-05-11 00:23:34
PHP
Angelxalfa, 2015-05-11 00:23:34

Output pdf from MySQL?

Hello! On one project, it became necessary to store PDF files in a database.
I save in the field longBLOB a trace. code:

include '../../connect.php';
    $table = "".$prefix."test01";
    $city = "city";
    if ($handle = opendir(''.$_SERVER['DOCUMENT_ROOT'].'/wp-content/themes/domen/cities/'.$citie.'/bills/bill_files')) {
    echo "Файлы в папке:<br>";
    $log ='';
  $file_name = null;
  while (false !== ($file = readdir($handle))) { 
        if(preg_match("|.pdf$|",$file)){
       if($file_name !== $file){
        echo "$file<br>";
        $pieces = explode(".", $file);
        $id = $pieces[0];
        echo $id;
        $upload=file_get_contents(''.$_SERVER['DOCUMENT_ROOT'].'/wp-content/themes/domen/cities/'.$citie.'/bills/bill_files/'.$file.'');
            $ins=sprintf("UPDATE $table SET bill ='%s' WHERE id='$id'",mysql_real_escape_string($upload));
            mysql_query($ins);
            if (mysql_errno()) {echo mysql_errno() . '\n' . mysql_error() . '\n';}
          if (!$ins){$log .= "Ошибка копирования данных из файла ".$file."$date<br>". PHP_EOL;}
          else {$log .= "Данные из файла ".$file." скопированны в базу $date<br>". PHP_EOL;}				
          $file_name = $file;
       }
    }
    }
  echo $log;
}

I bring it back like this:
header("Content-Type: application/pdf");
include '../../connect.php';
$table = "".$prefix."test01";
$id = "0001";
$result="SELECT bill FROM $table WHERE id='".$id."'";
$result = mysql_query($result);
$row = mysql_fetch_array($result);
echo $row['bill'];

When using Chrome, the output is the original PDF. But when you save it and try to open it, the file is corrupted.
If you add a line to download the file immediately
header("Content-Disposition:attachment;filename='pdf.pdf'");

the result is a pdf.pdf file with a size of 0 bytes.
Tell me how to display pdf from the database correctly, thanks in advance!
The problem ended up being this line when uploading the file:
$ins=sprintf("UPDATE $table SET bill ='%s' WHERE id='$id'",mysql_real_escape_string($upload));

Removed escaping and changed to
$ins = "UPDATE $table SET bill ='".$upload."' WHERE id='".$id."'";

Everything worked correctly.
Many thanks for all the advice! I agree that storing in the database is not the best solution, but the site is now on a virtual hosting and I don’t have access to the nginx settings, but I need to do it “yesterday”! :-) Therefore, I have to look for a quick solution. In the near future I will transfer the site to a dedicated server and there I will try to implement it through nginx (thanks to Dmitry Filimonov and @valerium).

Answer the question

In order to leave comments, you need to log in

1 answer(s)
D
Dmitry Filimonov, 2015-05-11
@Angelxalfa

As it is customary on Russian resources not to answer a question, but give advice :) But this time the advice is important and even usable.
Storing a file in a database is bad. It may seem that this is convenient (for example, only a database backup will save everyone).
But really:
You can store blobs, of course, but sooner or later you will make the database a bottleneck. If you store blobs, then they are very, very small, but even this is debatable and depends on the task.
So give up on the idea.
And here the DB? Store the file in a place that is not served outside. And access to the file regulate at the request. That is, you have some kind of url for the file, it contains its id, for example. You carry out authorization: if you can take the file, ask the database for a link where it is located on the disk (and this place is not visible outside), and give the file in the same way as you are doing now. BUT, if large files are expected, you need to read the file in parts, otherwise you can end the operative. Frameworks usually have tools for this, by the way.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question