php saving an image to postgresql and read it back is not working

Question

This must be common asked question, But I could not find from google. We have a table and has photo column in bytea type in postgresql. We are saving upcoming image with this snippet:

$photo->attributes = $_FILES['Photo'];
$file = CUploadedFile::getInstance($photo, 'photo');
$path = Yii::app()->basePath . '/data/' . $file->name;
$file->saveAs($path); //save the file to the $path
$fp = fopen($path, 'rb');
$content = fread($fp, filesize($path));  //get the file content
fclose($fp);
$photo->photo = base64_encode($content);   //encode it
$photo->save();  //save the record to db
unlink(Yii::app()->basePath . '/data/' . $file->name);

saving seems working good.

this is where we are reading blob field from db:

base64_decode($data->photo) //this call is giving base64_decode() expects parameter 1 to be string, resource given error.

if I do:

print_r($data->photo) //I am getting: Resource id #51

Obviously $data->photo is not binary string, it is coming as a resource. Any idea how to make it work?

thanks in advance.

Could you please add the code you are using to get $data out of the database? — chrislondon
– chrislondon, Commented Jun 9, 2013 at 5:26
@chrislondon, We are using fromework's active reader feature. $data = Photo::model()->find('employee_id = :employee_id', array(':employee_id' => $id)); — Elbek
– Elbek, Commented Jun 9, 2013 at 5:29

Craig Ringer · Accepted Answer · 2013-06-09 08:27:41Z

If you're using base64 then you don't need bytea, you can use a regular text field. It'll be less efficient for disk space, but that's about it.

If you want to store the actual bytes, you don't encode it as base64, you use pg_escape_bytea to convert it to PostgreSQL's hex-string representation (for modern PostgreSQL versions) of bytea values. You use pg_bytea_decode when extracting the data.

If you're using PDO instead of the native PostgreSQL driver, look up how to work with bytea using PDO. You haven't actually shown your database code at all, only some wrapper for it, so it's very hard to tell what's going on.

Of course, all this only applies to PHP; most languages have separate data types for "textual data" and "binary data" that let the client library automatically convert binary data without you having to jump through escape/unescape hooks.

Александр М · Accepted Answer · 2015-09-07 08:26:29Z

For me work this solution:

Save file to DB:

$file = CUploadedFile::getInstanceByName('file');
if ($file!=null) {
    $fp = fopen($file->tempName,'r');
    $content = fread($fp,  $file->size);
    fclose($fp);
    $doc->doc=null;  //bytea field
    $doc->docname = $file->name;
    $doc->mime = $file->type;
    $doc->size = $file->size;
    $doc->save();   
    $msg = $doc->getErrors();
    $command = Yii::app()->db->createCommand('UPDATE '.
                $doc->tableName().' SET "doc"=:doc WHERE id='.$doc->id); 
    $command->bindParam(":doc", $content, PDO::PARAM_LOB); // <-- look this!
    $command->execute();
}

Show file from DB:

header('Content-Type: '.$doc->mime );
header('Content-Description: File Transfer');
header('Content-Disposition: attachment; filename*=UTF-8\'\'' . rawurlencode ($doc->docname ) );
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate');
header('Pragma: public');
header('Content-Length: ' . $doc->size);
$content = fread($doc->doc, $doc->size);
echo $content;
exit;

Collectives™ on Stack Overflow

php saving an image to postgresql and read it back is not working

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related