Whirlpool

Hey guys,

Im creating a website on computer products. However storing an image in a database isn't as easy as it seems, to a novice. So i was wondering, is a MYSQL database the best way for store images? If so, does anyone recommend any good tutorials?

dgamma3 writes...

So i was wondering, is a MYSQL database the best way for store images?

Wait Wait, what are you trying to do are you trying to say is mysql the the best way to store an image on website? or are you referring to some web store?

Either way it usually isn't a good idea to store images in a mysql database. You could store the location of the images on the server in the database which would probably be easier for you to implement.

If you did want to store images in a database here is a tutorial:

http://www.phpriot.com/articles/images-in-mysql

Have you ever built a website before?

If not, try starting at http://www.w3schools.com/html/default.asp

Should never store images in a database unless you absolutely must. Just store a file path reference and place the images in the file system.

It's a serious performance penalty that will bring your website to its knees with any half serious traffic.

Perhaps store your image on disk and then storing the file location in MYSQL. If your site is a busy one, you don't want to hold up your request at MYSQL for too long. Imagine denial of service attacks.

Better still cache frequent used images.

ParadiseAve writes...

Better still cache frequent used images.

Careful with that.

Operating systems already have buffer caches, which do a pretty fine job of caching frequently used files. You would only add an extra cache if you could show that it's a net win.

dgamma3 writes...

However storing an image in a database isn't as easy as it seems, to a novice.

Key word: novice.

Don't do it. You encounter all sorts of problems doing it like this. Do what others have suggested, just have a path to the file stored on the server.

Edit: I just read up on a few of these articles again (having investigated the method in...uhhh 2005/6). Here's a pair of advantages to using the DB over the filesystem taken from http://www.phpriot.com/articles/storing-images-in-mysql/2:

Advantages of Database Over Filesystem

All file data is stored in a single location (not on both filesystem and database). If you want to back up your images you only need to do this in a single location.

Using a filesystem you may lose a file then have a stale database record pointing to it. The opposite may also happen (have a file without corresponding record).

1. If you want to back up your images from a single location, you could be somewhat foresighted to use a directory to contain all your uploaded images. You might even go so far as to use a subdirectory or two. Regardless, you only need to back up the parent directory which would grab all the ones within (simple gzip can do it, or you can php your own zlib)

2. How would you "lose a file"? Provided that you aren't stupid to rm -rf a folder, it's no different than being irresponsible in a db where you go "delete from myTable"

There just don't seem to be any significant advantage doing it this way. I wonder why people do. You have to contend with encoding issues which complicate a seemingly straightforward task, not to mention this encoding has a tendency to bloat filesizes anyway.

Having said that, it does seem like a way to "hide" where an image lives on your server, to keep curious people away but imho that's a problem for system security, not image storage.

There is another advantage which ties in with #2 – you can process the image (file) and its meta data as one atomic transaction. Without this, it is possible for either the file or database operation to fail by itself. It just means that you need to be more diligent rolling back the part that succeeded, which isn't rocket science.

First of all, thanks for all your responses

Second: you guys suggest store the location of the images on the server in the database. I have no idea of how to do this. Would this method allow me to do the following:

When adding new products to a products page, I would need to be able to link the images to the product and display it . Much like this website.

How do i do this (storing the image, and displaying the correct image), php? sql?

Anyone know of a good tut?

whats the google term i should be searching for this?

dgamma3 writes...

php? sql?

yep

just look up mysql tutorials for now. You sound like you're really jumping in the deep end. It's not hard to pick up, but is' very very easy for beginners to run into massive problems in the early days.

ironheart writes...

just look up mysql tutorials for now.

What do I look up, I need at least a Google term to search or something

ironheart writes...

It's not hard to pick up, but is' very very easy for beginners to run into massive problems in the early days.

Well I don't really know how hard it is, because i don't know how to do it. Anyway Ive got to develop this for a school project within the next 2 months (assignment is just html + css. its too easy. so i want to go a step further), and i want to make sure i understand the theory before I start.

dgamma3 writes...

What do I look up, I need at least a Google term to search or something

Do you want to understand the theory of designing a good database?

If so get this textbook: Information Modelling and Relational Databases by Terry Haplin and Tony Morgan. It is a great book that teaches you all the steps required to create a normalised database.

If you don't care about good design and just want to get to the good stuff check out http://www.tizag.com/mysqlTutorial/

To learn how to query stuff from the database you use sql a list of good books can be found here: http://databases.about.com/od/reviews/tp/sqlbooks.htm though I haven't read any of them.

As for google queries its not hard to formulate a simple thing. I a simple search could be php and mysql tutorial.

for more tips on how to google check out http://www.googleguide.com

Hmm, thanks for that. But that isn't my question. my question is how do you setup a file system with a database, so i can upload images and read them to a website, as stated in post 1.

if anyone could just tell me the terms to search for, that would be much appreciated.

Everyone's saying to use a file system + DB, that's great. It may be obvious to you, but i have no clue, on how to link the two.

NOTE: i know basic php and sql, I don't need to learn from scratch. I just need to learn the functions and methods to achieve my goal.

dgamma3 writes...

But that isn't my question. my question is how do you setup a file system with a database, so i can upload images and read them to a website, as stated in post 1.

This has already been posted by me and others in this thread:
http://www.phpriot.com/articles/images-in-mysql

It shows you how to upload images into a database and retrieve them.

As the general consensus of this thread shows this is a bad idea you would be better to look up.

"uploading a file php tutorial"

reading the tizag tutorial on php mysql.

then when you upload a file you set the directory on the server, so basically all you need to do is store the file name on the database.

when you want to display the file you just need to call the database and with the results put them in an image tag such as <img src=$sqlResult>

jesse_cool5 writes...

"uploading a file php tutorial"

Great help, thanks. This is what i want

http://www.tizag.com/phpT/fileupload.php

Simple Tutorial it even gives you the command to read the file name which is what you will want to store in a database.

Thanks for that, just tried some random images. Some work, some don't.

dgamma3 writes...

Thanks for that, just tried some random images. Some work, some don't.

Check the file size the script is limiting files to under 97 kilobytes (assuming your using the tizag example). Also if you increase the limit in the script to more than 2mb you will find a php setting will stop you from uploading more than 2mb the limit can be changed in php.ini

if that doesn't work check the file names.

jesse_cool5 writes...

than 2mb the limit can be changed in php.ini

If your hosting allows that. Sometimes you have to use the .htaccess file to do it, and even then, on some hosts (like mine) this might be disabled.

some file types might be excluded too.

if i wanted to increase the file size, would it be 1024 X .. or 1000 X ...

dgamma3 writes...

if i wanted to increase the file size, would it be 1024 X .. or 1000 X ...

shoot the moon, just use 4G