[adsense id=”0514458240″ width=”468″ height=”60″]
NOTE: the MySQL method for connecting to the database and performing database operations but has been deprecated as of version 5.5 ().
You should use either mysqli or PDO to connect to the database. An identical example using mysqli and PDO are found by clicking the links.
This paper provides the code needed to both write binary content to a MySQL table and to read the same data back. In our example, we will create two scripts, one that reads the contents of a PDF file and adds this into a table. The second script will read the contents of the same record in MySQL and output it to a file. We will then show that the two files are identical.
The table that we created is as follows:
CREATE TABLE testblob( id INT AUTO_INCREMENT , data MEDIUMBLOB, PRIMARY KEY ( id ) ) ENGINE = InnoDB;
The first script reads the contents of a PDF file called itiss.pdf.
<?php
$dbh = mysql_connect("localhost", "user");
mysql_select_db("test");
$data = file_get_contents("itiss.pdf");
// This is important to avoid a ' to accidentally close a string
$data = mysql_real_escape_string($data);
mysql_query("INSERT INTO testblob(data) VALUES ('$data')");
?>
At this point the binary has been added into the database. We now need a second script which will read this content and write it back out to a separate file:
<?php
if(!$dbh = mysql_connect("localhost", "user")) {
die("ERROR: " . mysql_error());
}
mysql_select_db("test");
if(!$res = mysql_query("SELECT data FROM testblob WHERE id = 1", $dbh)) {
die("ERROR: " . mysql_error());
}
$row = mysql_fetch_assoc($res);
$data = $row['data'];
file_put_contents("itiss1.pdf", $data);
?>
To see this in action:
$ mysql -D test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 30 Server version: 5.6.17 openSUSE package Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select count(1) from testblob; +----------+ | count(1) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) $ php writeblob.php $ mysql -D test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 32 Server version: 5.6.17 openSUSE package Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select count(1) from testblob; +----------+ | count(1) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) $ php readblob.php $ ls -ltr -rw------- 1 rcashell users 229943 Jan 19 12:57 testimage.jpg -rw-r--r-- 1 rcashell users 229943 Jan 19 16:15 testblob1.jpg
[googleplus]