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.