Member Avatar for jrotunda85

I am in the process of creating a website in PHP/mySQL and the next thing I wanted to tackle was creating an option for users to be able to upload a small avatar (maybe max of 50x50 let’s say). I’m not as familiar with how images are stored/retrieved in a database – any guidance on getting this up and running?

Thanks!

  • 3 Contributors
  • 4 Replies
  • 309 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by Member #120589

Dani AI

Generated

Both common approaches mentioned by (store files on disk and save a path) and (store the binary in a BLOB) are valid. A quick rule of thumb: for small sites or when transactional backups of user data are important, a BLOB is convenient; for larger sites or when serving many images, store files on disk or object storage and keep only metadata in the database for performance and easier CDN/cache use. Note: MySQL uses BLOB types (not CLOB) and there are size choices (TINY/MEDIUM/LONGBLOB) to match expected needs.

A compact schema that supports either approach (path OR blob) and useful metadata:

CREATE TABLE avatars (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  mime VARCHAR(64) NOT NULL,
  width SMALLINT UNSIGNED,
  height SMALLINT UNSIGNED,
  size INT UNSIGNED,
  path VARCHAR(255),      -- store when using filesystem/object storage
  image MEDIUMBLOB,       -- store when choosing DB blobs
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Example workflows (high level):

  • Filesystem/object storage: safely move the uploaded file to a protected uploads/avatars directory (use a collision-resistant name like a hash), record mime, dimensions and path in the DB, and let the webserver or CDN serve the file directly (or use X-Sendfile/X-Accel-Redirect).
  • DB blob: read the binary and insert it with a prepared statement (bind as a LOB or send file contents). Serve through a lightweight script that emits the correct MIME and caching headers, and use caching headers so browsers/CDNs can avoid repeated hits.

Security and robustness notes (gaps in earlier replies): trust server-side MIME detection (finfo), re-encode/resample uploads with a safe image library to strip dangerous metadata and normalize formats, enforce php.ini limits (upload_max_filesize, post_max_size), use strict folder permissions, generate unique filenames, and add caching headers or ETags when serving avatars to reduce load. For large-scale sites, prefer object storage + CDN over DB blobs.

Recommended Answers

All 4 Replies

Member Avatar for Member #120589

You have two main options. You can save the uploaded image as data in a MySQL blob field or you can save the actual file in a folder and reference it in a MySQL varchar field (filepath).

You can access the file dimensions, file size and file type in order to access/refuse the image. You have a choice as to the dimensions (e.g. 50 x 50), file size (e.g. 100Kb), file type (usually GIF, JPG, JPEG, PNG).

If the dimensions are greater than the allowed, you can simply refuse them or scale them yourself to the maximum allowed size.

Example:

list($w, $h) = getimagesize("$_FILES['upload']['tmp_name']");

Will give you the file dimensions ($w and $h).

$maximagesize = 307200 //(300kb or is that kib - can never remember)
if ($_FILES['upload']['size'] > $maximagesize) { 
...
}

Will check your filesize

$xtn = strtolower(strrchr($_FILES['upload']['name'], ".")); //the reverse is impt because names could involve more than one period
if ($xtn!= ".gif" AND $xtn!= ".jpg" AND $xtn!= ".jpeg" AND $xtn!= ".png") { 
...
}

Will check your extensions. Don't rely on Ajax/JS-based stuff as these can be bypassed.

Here's a scaling script from the php manual: http://www.php.net/manual/en/function.getimagesize.php#97564


Anyway, have fun.

Edited by Member #120589 because: n/a
Member Avatar for smantscheff

Many people tend to store pictures as external files and keep only a reference in the database.
I'd rather recommend to storing them in the database in blob fields as you won't have to deal with file or directory names, updating image files and the like.
The process for that is:
Upload the file and read it from the local temporary into the database.
Create a PHP script which outputs the image field content to the browser. Include a matching image header into the script (e.g. header("Content-type: image/jpeg").
When generating the HTML with images in it, create references to your image display script instead of references to image file names.

// ardav beat me by 2 minutes ;-(

Edited by smantscheff because: n/a
Member Avatar for jrotunda85

Thank you both for the help, it seems that both of you suggest storing it in the DB as opposed to referencing it? If so, how would I go about creating the SQL to execute to actually store the attached image into the clob field? :?:

Member Avatar for Member #120589

First of all it's a blob field. I always use a ref and real files myself. I find the storage and retrieval a bit of a phaph. Anyway, each to his own. Each has its pros and cons.
AS smant said - use a ref to the script, if you go down the blob route, e.g.

<img src="createimg.php?id=634732" />
Edited by Member #120589 because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.