How to insert a pdf file in a MySQL database and then read it in php?

Question

I have a PHP code that allows me to send a pdf file by email with SendGrid and to insert this file as a BLOB in a MySQL database.

Everything works fine but the file inserted in the database is always a [BLOB - 20,0 kio] file, I can't figure out if it is inserted correctly and how to retrieve it from the database...

Thanks for your help

<?php

 
 $filename2 = 'test.pdf';

 $file_encoded = base64_encode(file_get_contents("C:/wamp64/www/final/API_label/PDF/$filename2"));
 $email->addAttachment($file_encoded, "application/pdf", "$filename2", "attachment");

 $sendgrid = new \SendGrid('SG.Ebi-CnMATfeehrmw89O5CuSNfPk');
 try {
     // $response = $sendgrid->send($email);
     // print $response->statusCode() . "\n";
     // print_r($response->headers());
     // print $response->body() . "\n";
 } catch (Exception $e) {
     // echo 'Caught exception: ' .  $e->getMessage() . "\n";/
 }

      //Insertion of the values in the database
      try {
         // Connect to db
         $db = new db('mysql:dbname=jotform; host=localhost', 'root', '');
         $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

         // Set SQL
         $sql = 'INSERT INTO DHL (submission_id, formID, identite, email, adresse, telephone, label, commercial_invoice) 
         VALUES (:submission_id, :formID, :NOM, :EMAIL, :ADRESSE, :TELEPHONE, :file_encoded, :COMMERCIAL_INVOICE)';

         // Prepare query
         $query = $db->prepare($sql);
         $query->bindParam(':file_encoded', $file_encoded, PDO::PARAM_LOB);

         // Execute query
         $query->execute(array(':submission_id' => $submission_id, ':formID' => $formID, ':NOM' => $NOM, ':EMAIL' => $EMAIL, ':ADRESSE' => $ADRESSE, ':TELEPHONE' => $TELEPHONE, 
         ':file_encoded' => $file_encoded, ':COMMERCIAL_INVOICE' => $COMMERCIAL_INVOICE));
     } catch (PDOException $e) {
         echo 'Error: ' . $e->getMessage();
     }
 

}

?>

Code to dowload:

    //PDO PART
include '../include/classe_PDO.php';

 try {
  // Connect to db
  $db = new db('mysql:dbname=jotform; host=localhost', 'root', '');
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // Set SQL
  $sql = "SELECT * FROM `dhl` WHERE `submission_id` = '5094071540419221255'";

  foreach  ($db->query($sql) as $row) {
    
          $filedata = $row['label']; //get base64 data from query result
          $decoded = base64_decode($filedata); //decode base64 to binary
          
          //set suitable HTTP response headers
          header('Content-Description: File Transfer'); 
          header('Content-Type: application/octet-stream'); 
          header('Content-Disposition: attachment; filename="label.pdf"');
          header('Expires: 0'); 
          header('Cache-Control: must-revalidate'); 
          header('Pragma: public'); 
          //output the binary file data in the body of the response
          echo $decoded; 
          exit;
}



} catch (PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}         


?>

Answer 1

You need to

1. Store the data in a TEXT column in MySQL, not BLOB, because a base64 string is text, not binary data.

2. Query that field from your database to get the base64 data

3. Decode the base64 data to get the original binary data.

4. Send that data for download, setting the appropriate headers for your browser to understand that it's a file download rather than a web page.

You seem to have the SQL part sorted now, judging by the update to your questions, so here I'll just show a simpler way to download the data, without needing to write a file to the server disk first (which you'd then need to clean up later).

$filedata = $row['label']; //get base64 data from query result
$decoded = base64_decode($filedata); //decode base64 to binary

//set suitable HTTP response headers
header('Content-Description: File Transfer'); 
header('Content-Type: application/octet-stream'); 
header('Content-Disposition: attachment; filename="test.pdf"');
header('Expires: 0'); 
header('Cache-Control: must-revalidate'); 
header('Pragma: public'); 
//output the binary file data in the body of the response
echo $decoded; 
exit;