CakePHP, MySQL and Blob

Originally posted on May 26th, 2006, here is how I got files stored inside database with blob data type. However I advice you to store file on hard drive and some meta-data in the data base, then when a content is required, retrieve the file correct file based upon the meta-data stored in the database.

First of all the table where to store data. Let’s assume something similar to this

create table actors(
   id int(8) unsigned not null auto_increment,
   ...
   photo mediumblob default null,
   created datetime default null,
   modified datetime default null,
   primary key(id)
)engine=MyISAM,character set=utf8;

In order to store blob data, you will need as a view something similar to the following

<form method="post" action="/actors/add" enctype="multipart/form-data">
   <fieldset>
      ...
      <p>
         Send us a photo: <?=$html->file("Actor/photo")?>
      </p>
   </fieldset>
   <?=$html->submit("Send!")?>
</form>

notice the form’s enctype attribute.

Then the controller

function add(){
    if(empty($this->params["data"])){
        $this->render();
    }else{
      if($this->Actor->validates($this->data)){
         $photo = $this->params["data"]["Actor"]["photo"];
         if($photo["size"]>0){
            $fileData = fread(fopen($photo["tmp_name"],"r"),$photo["size"]);
            $this->params["data"]["Actor"]["photo"] = $fileData;
         }else{
             $this->params["data"]["Actor"]["photo"] = null;
         }
         if($this->Actor->save($this->params["data"])){
            $this->flash("added with code " . $this->Actor->getLastInsertID(),"/");
         }
      }else{
          $this->validateErrors($this->Actor);
      }
   }
}

Code can surely be tuned and made better. Note that in MySQL if you use the blob type, you will be able to store up to 64Kb of data. For more information and how to store more informations inside the blob field refer to data type storage requirements. Also read: blob & text type.

About these ads

6 thoughts on “CakePHP, MySQL and Blob

  1. I’ve never tried. As I said in the beginning of the post, I prefer store files on hard drive and some meta-data on db.
    However I think it should be enough, from a controller method for example, something like the following
    function showphoto($id){
    this->Actor->find($id,…);
    $this->layout=”binary”;
    $this->set($data,$this->Actor["Actor"]["photo"];
    }
    then the binary layout should be something like an empty file with only a line, like <?=content_for_layout?>. I don’t remember exactly how it is and I don’t have a source by my hands now, consult the defaut layout.
    Finally the view should be something like

    <?php
    //set the right http headers: content-type, file name, file lenght, …
    echo $data;
    ?>

    I’ve not tried it, so it could be wrong.

  2. RAVINDER SINGH says:

    PLEASE TELL , IF IT IS POSSIBLE TO RETRIEVE A PART OF BLOB DATA.
    ANY FUNCTION IN PHP THAT CAN DO IT.

    I AM STORING A TEXT FILE THROUGH FCKEDITOR INTO A BLOB FIELD IN MYSQL.
    I WANT TO RETRIEVE ONLY 2 OR 3 LINES FROM THIS TEXT FILE.

    ANY ADVISE.
    THANX
    rs

  3. lakshmi says:

    Hi
    I’m currrently trying to get a xml file stored in blob.
    this is the piece of code i’m using
    // after getting blob

    blob b= rs.getBlob(“xmlfile”);

    PrintWriter fwriter = new PrintWriter (new FileOutputStream(fileName));
    InputStream in = blob.getBinaryStream();
    InputStreamReader reader = new InputStreamReader(in);
    int length = -1;
    char[] buf = new char[bBufLen];
    while ((length = reader.read(buf)) != -1) {
    out.write(buf, 0, length);
    }

    but the xml file i’m getting is having all junk chars in middle.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s