Blob to store data in mysql database

To store data within a blob in a database can be a good thing at times because then you can just copy the database from one place to another and use the access rights on the database to restrict access to the “files” within the database.

There could be a few reasons why you want to store the data within a blob in the database, but here how the basics would work.

To start with you have to create a database and a table to store the data/file within the blob, of course if you have already created the database and/or the tables then alter as you think, but here is the basics.

  CREATE DATABASE phptestplace;
  CREATE TABLE storingData (id INT NOT NULL auto_incremenet, lblob BLOB, PRIMARY KEY (id));

And then within a php file you can access the database and a file.

  $link = mysql_connect('localhost', 'username', 'userpassword');
  if (!$link) {
      die('Could not connect: ' . mysql_error());
// alter to your database name
  mysql_select_db("phptestplace", $link);

and now access the file and read in file

  $filename = "filetoload.txt";
  $handle = fopen($filename, "r");
  $contents = fread($handle, filesize($filename));
// to insert into the database you need to add in the slashes for characters like / \ etc.
  $contents = addslashes($contents);

to insert into the blob you just, change the table and table name to what may have called it.

  $sqlquery = "insert into storingData(largeblob) values ('$contents')";
  mysql_query($sqlquery) or die("ERROR");*/

to get the data back (I am calling back the last inserted value into the table)

// get the data into a result variable
  $return = mysql_query ("select lblob from storingData where id = (select max(id) from storingData)") or die("LLL");
// get the contents of the blob from the return variable (it returns a array of data) and the list takes out the data from a array each part at time.
  list($newcontents) = mysql_fetch_array($return);

and then store the data from the database pull into a file, I have called it newfile.txt, but it is up to you.

  $fp = fopen('NEWFILE.txt', 'w');
  fwrite($fp, $newcontents);

Of course can do it via a web page, using a HTML FORM enctype=”multipart/form-data” within the form tag otherwise it may not work.

Leave a Reply

Your email address will not be published. Required fields are marked *