Click here to Skip to main content
14,325,352 members
Rate this:
Please Sign up or sign in to vote.
See more:
how can I load a picture in a img tag from bytea field, using php? I´m using postgresql database

What I have tried:

// Connect to the database
$con_qupos = pg_connect('host= dbname=dbqupos user=postgres password=12345') or die(pg_last_error());

// Get the bytea data
$res = pg_query('select foto from "schSuperCristian".articulo_foto where codigo_referencia='."'8711600305960'");
$raw = pg_fetch_result($res, 'foto');

// Convert to binary and send to the browser
header('Content-type: image/jpeg');
echo pg_unescape_bytea($raw);
Updated 1-Mar-19 11:33am
Rate this:
Please Sign up or sign in to vote.

Solution 1

You can:
- Either create a PHP query page URL that will perform this PHP code, and use this URL in the src attribute of a standard HTML image tag: the browser will perform a separate query to that image at the PHP address (Make sure it is cachable)
- Or if the image is small (not more than about 8KB) use an image tag whose src attribute uses an URL using the "data:" scheme, in which you can encode the image content in Base64)
- Or embed the data URL for the image in a CSS (there are examples of such data URL for small icons embedded in the CSS stylesheet used by Wikimedia sites, such as custom bullets, icons for external links; note that the data URL also specifies its media type: it is not restricted to just JPEG or PNG, can be SVG as well; Base64 is probably the best encoding scheme for most bitmap images; make sure you compress these images to remove unnecessary internal tags, or extra whitespace in SVG, before you encode it to Base64

There's a limit on the maximum size of "data:*" URLs accepted by browers, so it's probably not usable for embedding user profile images in JPEG/PNG formats that are larger than about 80x80 pixels; such compression should be made before storing the images in a Blob of your Postgres database; if images are larger, then you should better store them under a unique name based or your userid or on SHA1(content) in a filesystem (and use the first few bytes of the SHA1 as a directory folder to avoid having overclowded directories which may have long access time), and your your database to store a small text field containing the external filename. Here your example seems to be about storing a photo of a user in JPEG format, and such photos are frequently still too large to fit (once encoded in Base64 which expands their size by a factor of 4/3) in the limit of the "data:image/jpeg:*" url).

Note also that not all browsers support data URLs for images (especially old smartphones): you can mix the two approaches: embedded data URLs are fine for small images because they avoid additional delays, but they should still be cachable and they fit very well in reusable CSS stylesheets shared between users. But for individual user profiles it's probably preferable to use the external HTTP requests to your PHP script, and then use a URL parser to call the script with the relevant parameters; look for example how photos are stored and retrieved from Wikimedia Commons from a Wikipedia page, using a single script and an URL which is parsed to get the actual image name to retrieve (internally Wikimedia Commons indexes images by an internally generated unique ID specific to each image version, but images are retrieved by their name to query the database that returns the internal storage ID for specific versions, these images are then stored in a conventional filesystem using an hash of their ID and this hash is used to distribute the many files over a set of mounted disks and via a filesystem with a reasonnably cumfortable cache on the web server; the web server delivering the image is not necessarily the same as the werver for the database or the web server for your pages).

Some database servers also provide this mechanism for indexing and storing large blobs in an external fileserver directly, instead of actually storing these large blobs directly in indexed tables: I suppose you are using PostGresl use because of your code using "pg_connect()" and "pg_query()" to retrieve the bloc in PHP, but you may wonder if this is the best storage option if you need to store a lot of photos with possibly large resolutions (up to several MB per user photo). It may be faster and more efficient to store these photos on a separate mounted NFS folder where they will be served by something more efficient then a PHP script (directly using Apache capabilities), provided that you've configured the access rules for security or privacy (e.g. allowing such requests to be honored using unique access tokens in sessions coordinated between the file server and the main database server where access rights are managed).
Rate this:
Please Sign up or sign in to vote.

Solution 2

Note: that allowing users to post photos may not only take precious space on your servers, but may also cause issues:
- privacy
- deimpersonnation
- copyright abuses...
It may be useful to save space and avoid thoses uses by allowing users to choose onstead from a free reusable collection of avatars.

There exists external avatar providers (like Gravatar), but also free SVG avatar generators (which can generate random avatars from a set of known rules and a few predefined shapes and sets of colors, and that users can then tune up to their desire using some compositing palette of shapes and coloring options): proposing a set of generated avatars made from a few randomized parameters and a building script will just require a small URL for the avatar generator.

If you intend to open your site to people subscribing with their social network account (Facebook, Google, Twitter, wellknown blog providers, photo sharing sites), your user accounts in your database will only store URLs which may point to your local collection of prebuilt avatars or to your local avatar generator, or to your local small photo storage, or to the external avatar provider or social profile provider. It will be up to your users to use the option that best fits their desires and matches your site's privacy statements.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100