CODEWITHSUNDEEP
phppostgresqlyiiblob
Elbek
Elbek

Reputation: 3494

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

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.

Upvotes: 0

Views: 4993

Answers (2)

Александр М
Александр М

Reputation: 21

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;

Upvotes: 0

Craig Ringer
Craig Ringer

Reputation: 325051

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.

Upvotes: 4

Related Questions