How to store and get data from postgres bytea field with Laravel ? I want to update binary data and download them. File types is jpg, excel, txt and so on. Can I do?
currently my code to store.
public function store_db( $file, $file_name, $user_id ) {
$file_path = $file->getRealPath();
$new_attachment = Attachment::create([
'name' => $file_name,
'mime' => $file->getClientMimeType(),
'size' => $file->getClientSize(),
'uploaded_data' => pg_escape_bytea(file_get_contents($file_path)),
'created_by' => $user_id,
'updated_by'=> $user_id,
'created_at' => Carbon::now(),
'updated_at' => Carbon::now()
]);
return $new_attachment->id;
next to get and download data (jpg, excel, and so on)
public function get_attachment( $id ) {
$file = $this->attachmentRepository->findWithoutFail($id);
$dbh = DB::connection()->getPdo();
$stmt = $dbh->prepare("SELECT name, mime, trim(trailing from encode(uploaded_data,'escape')) AS encode_data FROM attachments WHERE attachments.id = :atid");
$stmt->bindParam(':atid', $id);
$stmt->execute();
$result = $stmt->fetch($dbh::FETCH_ASSOC);
$name = $result['name'];
$mime = $result['mime'];
$headers = array(
"Content-Type: {$mime}",
);
$fileData = $result['encode_data'];
$ext = substr($name, strrpos($name, '.') + 1);
file_put_contents($ext , pg_unescape_bytea($fileData));
return response()->download($ext, $name, $headers);