CODEWITHSUNDEEP
phppostgresqldata-uribytea
Abel Callejo
Abel Callejo

Reputation: 14939

How to convert the PostgreSQL bytea value into Data URL value using PHP

PostgreSQL has this datatype called bytea. It is their version of a blob.

In displaying images, png/jpg/gif, the current trend nowadays is to use Data URLs.

My question is how to convert a bytea value into a Base64 Data URL value?

References

Upvotes: 3

Views: 4723

Answers (3)

Parker Dell
Parker Dell

Reputation: 468

Posting this answer incase anyone stumbles here using Postgres and sqlalchemy. Thank you to the other answers for helping me get this far.

I was able to use the answer provided by clemens above and place the sql into a column_property and leveraging literal_column to insert a rawsql statement.

class Attachment(BaseModel):
    __tablename__ = "attachments"
    id = Column(UUID(), primary_key=True, default=uuid.uuid4, )

    data = Column(BYTEA(), nullable=True)
    text = Column(Text, nullable=True)
    file_name = Column(String(255), nullable=True)
    content_type = Column(String(255), nullable=True)
    model = Column(String(255), nullable=False)
    model_id = Column(UUID(), nullable=False, index=True)
    meta_data = Column(JSON, nullable=True)
    deleted = Column(BOOLEAN, default=False)

    # return a data url when the attachment is an image, doesnt work in sqlite
    image_url = column_property(
        literal_column("""
            CASE
                WHEN content_type LIKE 'image%' THEN 'data:image/gif;base64,' || encode(data, 'base64')
                ELSE null
            END
            """, type_=String).label("image_url")
    )

Upvotes: 0

clemens
clemens

Reputation: 17712

You may also fetch the complete image data URL from your database, e.g.:

SELECT 'data:image/gif;base64,' || encode(image_data, 'base64') AS image_url 
FROM ...

Upvotes: 6

CodeBoy
CodeBoy

Reputation: 3300

$img_bin = hex2bin(substr($the_bytea, 2));
$url = 'data:image/gif;base64,' . base64_encode($img_bin);

Postgres bytea is a string starting with \x followed by hexadecimal characters. To get the image in binary format, need to strip off the leading \x and convert from hexadecimal characters to binary with hex2bin. Now that you have it in binary, you can base64_encode it.

Upvotes: 1

Related Questions