Search code examples
imagebinary-databytea

fetch data from postgres bytea type column in cakphp 3+

PostgreSQL database and has some jpeg files saved to a table in the database with type bytea. After reading the documentation I get that in Cakephp it is treated as binary data. The first problem I have is that when I do a find(‘all’) to the table in where the pictures are, I get the text and numbers data correctly, but the bytea data is retrieved as ‘null’. How can I get the data of the picture and return it as an image to the user? I couldn’t find an example anywhere. this is normal quary to get bytea column from postgres select encode(column_name::bytea, 'base64') AS column_name from table1 I need this query in cakephp 3.X

select encode(column_name::bytea, 'base64') AS column_name from table1

Solution

  • the easiest way to get binary data from database.

    $binaryData = stream_get_contents($row[0]['employee_photo_path']);
$base64Data = base64_encode($binaryData);
$userPic = base64_decode($base64Data);