[adsense id=”0514458240″ width=”468″ height=”60″]
You should use either mysqli or PDO to connect to the database when using PHP 5.5 or higher. The mysql set of functions have been deprecated from this version (). Alternatively you can use PDO instead of mysqli. An identical example using PDO is found by clicking the link.
The biggest advantage of using PDO is that by changing the DATASOURCE from mysql to postgresql this will work in equally as well without changing any lines of code. CARE must be taken that you use standard SQL and not proprietary statements to make this work across multiple databases.
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 testimage.jpg.
<?php
$dbh = mysqli_connect("localhost", "user");
mysqli_select_db($dbh, "test");
$data = file_get_contents("testimage.jpg");
// This is important to avoid a ' to accidentally close a string
$data = mysqli_real_escape_string($dbh, $data);
mysqli_query($dbh, "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 = mysqli_connect("localhost", "rcashell")) {
die("ERROR: " . mysqli_error());
}
mysqli_select_db($dbh, "test");
if(!$res = mysqli_query($dbh, "SELECT data FROM testblob WHERE id = 1")) {
die("ERROR: " . mysqli_error($dbh));
}
$row = mysqli_fetch_assoc($res);
$data = $row['data'];
file_put_contents("testblob_i.jpg", $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 13:14 testblob_i.jpg
[googleplus]