Storing Images in MySQL db tables

[CTekGirl]

I recently found that I need a table that stores images not text based data. Any suggestions? The tables are already built. Is the image actually stored in the database or there a reference a directory on the server where the file is.

IE

TABLE 1

PRODUCT_ID - TEXT
PRODUCT - IMAGE

 

[LittleSmudge]

The recommended solution ( even with databases that inherently have the capability to hold images ) is to actually store the text string that points to the file.

If the root of the folder that contains the images is always the same then you can trunkate the "\\server\root\" section and store it in a tblINI or similar.

Then just store the "path\FileName.img" in the data field.


'ope-that'elps.

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[TonyGroves]

In one of my databases, there is a MyISAM table which stores images and a small amount of associated data. The image field is a MEDIUMBLOB and it holds the image in compressed 2-colour BMP format, with an average field size of 40k. There are about 50,000 records, giving a table size of about 2GB.

This system has never given any problems, and performance is quite good. I also find it a lot simpler to manage than my previous set-up which had the images stored in separate files and involved having to keep the table data and the files in sync.

However, in this case, there are just a few custom-written applications accessing the image data. If you wanted the images to be accessible to generic image-processing programs, then separate files would be called for.

 

[CTekGirl]

I like the suggestion of littlesmudge of storing the path in the table. I have not created this type of table before so I am uncertain about the tblINI term. I don't mind storing the entire path; because when I complete this I am turning it over to someone to upload, they don't know MySQL at all, so I am documenting each table. I am trying to build a table to work with some PHP scripts, because the database no longer exists.

The table will be used in reference to this PHP code:

this code:
$datacontent .= "<a href=display.php?product_id=$row[product_id] >$row[product_name]</a><br />";

refers to this code display.php:

mysql_select_db($database, $db);
$result = mysql_query("SELECT * from images where product_id = '$product_id'", $db);
$row = mysql_fetch_array($result);
$linknums = mysql_num_rows($result);
if ($linknums == 0) {
$reverberation = "Sorry, No Image Uploaded";
} else {
$reverberation = "<img src=uploads/" . $row['image_name'] . ">";

The product_id is a numeric field, but it refers to a image file.

I have a folder of images so my question is how many fields
do I need for the table? Is the path just a text field?

IMAGES
product_id 1
path "\users\image\computer part.jpg" --
product_id 2
path "\users\image\monitor.jpg"

Basically in a select query, if product_id = 1 it should bring up the image associated with product_id, not '1'.

thanks in advance.

 

[LittleSmudge]

Putting the WHOLE path in the table is certainly the easiest option.

The (minor) downside is that something like 80 % of the length of the text string is exactly the same in every record.

So you can store the "\\Server\Path\" part somewhere else and then concatenate that with the "folder\file.img" that you store in the main table.


tblINI is just a Setups and Constants table.
A table containing ONE record only that holds all the values that 'should' never change but might want to be set up at install time or changed very infrequently.


The name tblINI is a hark back to the old Windoz 3.x days when set up information for an application was stored in a Xxxx.ini file.
Many DBAs ( of the older vintage ) call their SetUps and Constants table tblINI because it is a modern database equivalent of the Xxxx.ini file.


'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[CTekGirl]

Thanks for clarifying. I think the 'images' table should have two fields. One to store the ID and one to store the path to the images(QUESTION: should the path be enclosed in quotes?). I did not find a fieldname reference to where these images paths are stored.

So I am unclear how the image would appear if the selection process only selects the 'product_id', based on the code in my previous post.

In reviewing the code(prev. posting) I am going to change the SQL from SELECT * from the images where product id=1 to an actual field name like product_image. As I stated previously I am trying to build the backend(database) to match the code of a previous developer; hopefully. I have the option of editing; but since I have the build the database I am trying to minimize my PHP editing.

I just need a checkpoint if I am going the right direction.

 

[LittleSmudge]

If you are designing the BE database to work with an existing FE/UI then you are really putting cart before horse !

But what you have posted above is sensible enough

SELECT ProductImage FROM tblProduct WHERE ProductId = 23

Then use ProductImage as the \\Server\Path\File.img for your picture.

The ProductImage field data type will be VarChar() and need not contain " delimiters because you should be adding this in the text string hangling yourself.If the text string DATA already contains the " delimiters then you'll get into an almighty mess.


'ope-that-'elps.

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[ingresman]

The question I like to ask when thsi subject comes up is:
If you store your images in the file system with a pointer in the DB, how do you handle load balancing and how do you cope with ,000's of files in a single directory.
I like the idea (amongst others)of using the db as I can then get at my images from virtualy any tool via odbc

 

[CTekGirl]

I am not completely clear on what your point is ingresman, but I will respond. Your point is not an issue with this database because of the size. I certainly would not want the syntax nightmare of typing in unique image names to link with an ID field. This is a small database with less than 50 images +/-. The position is a unique one because the project was left half finished with no documentation.

There is someonewhat of a time constraint since this project has already had some delays. So I am doing what I can to use some of what's here and create what I need to get this to work.

Thanks for all the great tips Little Smudge, you are at the top of your game. I am building the image table data now. I just needed a few checkpoints along the way.

 

[LittleSmudge]

ingresman raised a good point - but one that is clearly not of direct concern to you CTekGirl in your current challenge.

It's horses for courses ingresman and I must admit that all of the work I've done to date has been on single server application where load balancing has not been an issue. However, now that you raise the point, I can see that a load balanced server farm will need a different approach.
I shall have to ponder that !



G.

 

[ingresman]

LittleSmudge and CTekGirl,

I think we are all orrect here !, I didn't realise the app was only for 50 images.
I've had loads of debate around this on the PHP forum.
The other area that load balancing impacts is session handling in PHP (it uses files) which wont scale, but hey not a debate for here !

regards