Display "longblob" field content containing PDF file

Question

I have a question about the data longblob which are stored in the Database.

I currently have so for the other data:

echo "<tr><td>Email:</td>";
echo "<td>";

if ($exibe['Email']) {
    echo $exibe['Email'];
} else {
    echo 'N/D';
}

echo "</td></tr>";
echo "<tr><td>Alvara Numero:</td>";
echo "<td>";

if ($exibe['AlvaraNumero']) {
    echo $exibe['AlvaraNumero'];
} else {
    echo 'N/D';
}

echo "</td>   </tr>";

And now I want to show the attachment:

echo "<tr><td>Alvara Anexo:</td>";
echo "<td>";

I don’t know which is the best way to show or download to view the file.

Answer 1

The document stored in the database will have to be collected in an isolated PHP file, so that it contains the appropriate headers for the correct interpretation of the data.

A solution will be to apply a link in the code visualized in your question, which will call the file responsible for presenting the document through a parameter identifier of the desired line in your table:

Link to document

Example of creating a link to a PHP file with a parameter to pass to it:

echo '
<tr>
  <td>
    Alvara Anexo:
  </td>
  <td>
    <a href="caminho/ficheiro_mostra_pdf.php?id='.$exibe["id"].'" title="">
      Clique para ver documento PDF
    </a>
  </td>
</tr>';

^{Notes:
I’m assuming you have a spine id to identify the row in the table containing the document.
You must update/rectify the matrix entry name $exibe for the column name that actually exists in your table.}

File displaying a document

Below the source of the file with the name ficheiro_mostra_pdf.php which will collect from the database the binary data of the line it received and output to the browser with suitable header for PDF document:

NOTE: I’m using PHP mysql_* because I have seen your other questions resorting to this method for database queries, however I advise you to opt for other methods as this PHP extension is being removed.

<?php
// para debug, remover as 3 linhas quando em produção
error_reporting(E_ALL ^ E_DEPRECATED);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);


// verifica se existe identificador
if (isset($_GET["id"]) && ctype_digit($_GET["id"])) {

    // apanha identificador
    $id = $_GET["id"];

    /* define os acessos à base de dados
     */
    define("HOST", "localhost");     // Database Name
    define("DBUSER", "utilizador");  // Database user
    define("PASS", "password");      // Database password
    define("DB", "nome-base-dados"); // Database name

    // estabele ligação à base de dados
    $ligacao = mysql_connect(HOST, DBUSER, PASS) or die('Erro:' . mysql_error());

    // escolhe a base de dados
    $db = mysql_select_db(DB) or die(LANG_DB_CONNECT_FAIL);

    // consulta base de dados
    $resultado = mysql_query("SELECT meu_campo_blob FROM minha_tabela WHERE id='".$id."'");

    // verifica se recebeu algo
    if ($resultado) {

        // verifica se temos uma única linha
        if (mysql_num_rows($resultado)==1) {

            // recolhe os dados binários
            $registo = mysql_fetch_array($resultado);

            // verifica se contem dados
            if (is_array($registo) && isset($registo["meu_campo_blob"]) && $registo["meu_campo_blob"]!='') {

                $ficheiro = $registo["meu_campo_blob"];

                // cabeçalho identificador para o navegador
                header('Pragma: public');
                header('Expires: 0');
                header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
                header('Content-Type: application/pdf');
                header('Content-Transfer-Encoding: binary');
                header('Content-Length: '.strlen($ficheiro));

                // faz saída para o navegador
                print $ficheiro;

            } else {
                echo "Sem conteúdo para gerar o PDF.";
            }

        } else {
            echo "O número de resultados não é 1.";
        }

    } else {
        echo "Não foi possível seleccionar o campo da base de dados";
    }

    // fecha a ligação à base de dados
    mysql_close($ligacao);

} else {
    echo "Não foi possível apurar o identificador do registo pretendido.";
}

?>

The checks performed are basic to ensure a minimum of viability in the solution. However, you must ensure that access to this file is properly protected against undue access.

---

Headlines

Alternatively, you can add more headers to better control your document output, such as not doing cache, output encoding type and output size:

header('Pragma: public');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Content-Type: application/pdf');
header('Content-Transfer-Encoding: binary');
header('Content-Length: '.filesize($ficheiro));

Answer 2

Depending on the file format you cannot display it directly on the page, but you should put a link to the download of the same.

Displaying files directly on the page

If the file is text type (txt, xml, html, etc.) you can display it directly on the page. Use a tag <textarea>, <pre> or even in a <div> with due CSS.

Let’s take an example:

.conteudo-texto {
    display: block;
    unicode-bidi: embed;
    font-family: monospace;
    white-space: pre-wrap;
    width: 100%;
}

echo "<tr><td>Alvara Anexo:</td>";
echo "<td><div class='conteudo-texto'>" . htmlentities($exibe['AlvaraConteudo']) . '</div></td>';

Demo at Jsfiddle

You can also limit the size and place a scroll bar if necessary by adding CSS overflow: scroll;.

Demo at Jsfiddle

The same principle can be applied if the attachment is an image. Simply display it in place with maximum width and height sizes.

Displaying files in a frame, iframe or popup

Pdfs and some other binary file types are recognized by browsers and plugins, but should be displayed on a separate page.

New page

To display a PDF on a new page, simply create a link with the attribute target worthwhile _blank. The link should point to a PHP page which in turn will display the contents of the file.

Example for a PDF:

echo '<a href="visualizar_anexo.php?AlvaraNumero=' . $exibe['AlvaraNumero']) 
   . '" target="_blank">Abrir anexo</a>';

So the PHP file would have something like:

<?php
if (isset($_GET['AlvaraNumero'])) {
    try {
        // recupera dados do alvará
        $conteudo_arquivo = ...
        $tamanho_arquivo = ...
        $nome_arquivo = ...

        header("Content-length: $tamanho_arquivo");
        header("Content-type: application/pdf");
        header("Content-disposition: inline; filename=$nome_arquivo");
        echo $conteudo_arquivo; 
    } catch (PDOException $e) {
        // tratar erro
    }
}

Popup

To display in a popup, just add a Javascript event in the link.

Example:

$('a').click(function() {
    w = 600;
    h = 600;
    x = 10;
    y = 10;
    window.open(this.href, 'anexo', "resizable=no, toolbar=no, scrollbars=no, menubar=no, status=no, directories=no, width=" + w + ", height=" + h + ", left=" + x + ", top=" + y);
    return false; //inibe o clique original para não abrir a nova janela
});

Demo at Jsfiddle

Note that this is a simplified example. You should adjust the selector and parameters as needed.

Frame

The third option is to display the file in a frame or iframe. Example:

echo '<iframe src="visualizar_anexo.php?AlvaraNumero=' . $exibe['AlvaraNumero']) 
   . '"></iframe>';

Add a CSS style to iframe to leave it at a suitable size.

Forcing the download of the file

If you simply want to force the file to download, just add a link, in a similar way to display on another page.

Example:

echo '<a href="download_anexo.php?AlvaraNumero=' . $exibe['AlvaraNumero']) 
   . '">Abrir anexo</a>';

The PHP download script will be similar to the file for display, changing only the header Content-disposion.

Example:

<?php
if (isset($_GET['AlvaraNumero'])) {
    try {
        // recupera dados do alvará
        $conteudo_arquivo = ...
        $tamanho_arquivo = ...
        $nome_arquivo = ...

        header("Content-length: $tamanho_arquivo");
        header("Content-type: application/pdf");
        header("Content-disposition: download; filename=$nome_arquivo");
        echo $conteudo_arquivo; 
    } catch (PDOException $e) {
        // tratar erro
    }
}

Recovering the mime type automatically

The Mime Type is a standardized constant that tells the browser what kind of content is sent by PHP. In the examples, I used the value application/pdf fixed in the code.

However, you can use the function finfo_file to recover this value automatically if the file is written in some directory. But you probably won’t want this, so you can use the function finfo_buffer to get the file type through the contents.

Example:

$finfo = new finfo(FILEINFO_MIME_TYPE);
$mime =  $finfo->buffer($conteudo_arquivo);