18

How to store .pdf files into MySQL as BLOBs from PHP?

halfer
  • 19,824
  • 17
  • 99
  • 186
xcodemaddy
  • 201
  • 1
  • 2
  • 5

3 Answers3

14

EDITED TO ADD: The following code is outdated and won't work in PHP 7. See the note towards the bottom of the answer for more details.

Assuming a table structure of an integer ID and a blob DATA column, and assuming MySQL functions are being used to interface with the database, you could probably do something like this: 

$result = mysql_query 'INSERT INTO table (
    data
) VALUES (
    \'' . mysql_real_escape_string (file_get_contents ('/path/to/the/file/to/store.pdf')) . '\'
);';

A word of warning though, storing blobs in databases is generally not considered to be the best idea as it can cause table bloat and has a number of other problems associated with it. A better approach would be to move the file somewhere in the filesystem where it can be retrieved, and store the path to the file in the database instead of the file itself.

Also, using mysql_* function calls is discouraged as those methods are effectively deprecated and aren't really built with versions of MySQL newer than 4.x in mind. You should switch to mysqli or PDO instead.

UPDATE: mysql_* functions are deprecated in PHP 5.x and are REMOVED COMPLETELY IN PHP 7! You now have no choice but to switch to a more modern Database Abstraction (MySQLI, PDO). I've decided to leave the original answer above intact for historical reasons but don't actually use it

Here's how to do it with mysqli in procedural mode:

$result = mysqli_query ($db, 'INSERT INTO table (
    data
) VALUES (
    \'' . mysqli_real_escape_string (file_get_contents ('/path/to/the/file/to/store.pdf'), $db) . '\'
);');

The ideal way of doing it is with MySQLI/PDO prepared statements.

GordonM
  • 31,179
  • 15
  • 87
  • 129
  • My php code is below: $fileSize = $_FILES['userfile']['size']; $fileType = $_FILES['userfile']['type']; $fp = fopen($tmpName, 'r'); $content = fread($fp, filesize($tmpName)); $content = addslashes($content);$esc= mysql_real_escape_string(file_get_contents($fp));$query = "INSERT INTO table(id,data,Dealid) VALUES (49, \".$esc.\",0)"; – xcodemaddy Jan 27 '11 at 09:24
  • [`mysqli_query()`](http://php.net/manual/en/mysqli.query.php) that requires a db connection for it and as the first parameter. I fell on this Q&A from someone using it as a possible duplicate. It should be updated in order to provide the proper syntax; and this for future visitors/newbies who don't know how it all works. – Funk Forty Niner Apr 01 '17 at 14:46
  • [`mysqli_real_escape_string()`](http://php.net/manual/en/mysqli.real-escape-string.php) also needs a db connection. Can you ping me once you've done that Gordon? – Funk Forty Niner Apr 01 '17 at 14:48
  • There should also be brackets for this `mysqli_query 'INSERT` as in `mysqli_query($con, 'INSERT... . '\');');` – Funk Forty Niner Apr 01 '17 at 16:02
  • @Fred-ii- Am double-checking the query, but the documentation for mysqli_real_escape_string says it doesn't need a link parameter (though if you do provide one it must be the first argument) http://php.net/manual/en/mysqli.real-escape-string.php – GordonM Apr 02 '17 at 21:30
  • @GordonM *"but the documentation for mysqli_real_escape_string says it doesn't need a link parameter"* - I think you may be talking about `mysql_real_escape_string()`. The `i` equivalent does need a connection passed for it, otherwise it will not work; last I knew. – Funk Forty Niner Apr 02 '17 at 21:32
  • @Fred-ii- yeah, you're right. I was looking at the right documentation, but the wrong bit (the OO calling conventions, which don't need the link) – GordonM Apr 02 '17 at 21:36
  • @GordonM Because the OOP method `string mysqli::` uses the mysqli construct http://php.net/manual/en/mysqli.construct.php – Funk Forty Niner Apr 02 '17 at 21:39
  • @Fred-ii- Yes, I'm aware, but it was late, I was tired and didn't notice my eyes wondering over the wrong part of the docs ;) – GordonM Apr 03 '17 at 06:34
  • 1
    @GordonM Ah I see. I revisited the answer just now; I wonder why it was downvoted. I've given you a + to compensate. – Funk Forty Niner Apr 03 '17 at 12:45
  • @Fred-ii- I accidentally pressed the downvote button while I was viewing the total votes of the answer I was on a mobile phone, so the wrong button was pressed, been looking for this post to fix that, but I can't change that unless answer could be edit.. GordonM Apologies, If you could just do an edit I will be able to change that – Masivuye Cokile Apr 04 '17 at 08:42
1

In regards to Gordon M's answer above, the 1st and 2nd parameter in mysqli_real_escape_string () call should be swapped for the newer php versions, according to: http://php.net/manual/en/mysqli.real-escape-string.php

4b0
  • 21,981
  • 30
  • 95
  • 142
momoren
  • 11
  • 4
-4
//Pour inserer :
            $pdf = addslashes(file_get_contents($_FILES['inputname']['tmp_name']));
            $filetype = addslashes($_FILES['inputname']['type']);//pour le test 
            $namepdf = addslashes($_FILES['inputname']['name']);            
            if (substr($filetype, 0, 11) == 'application'){
            $mysqli->query("insert into tablepdf(pdf_nom,pdf)value('$namepdf','$pdf')");
            }
//Pour afficher :
            $row = $mysqli->query("SELECT * FROM tablepdf where id=(select max(id) from tablepdf)");
            foreach($row as $result){
                 $file=$result['pdf'];
            }
            header('Content-type: application/pdf');
            echo file_get_contents('data:application/pdf;base64,'.base64_encode($file));
AbdoM
  • 1