storing images in a database

@mrchipps69Mar 08.2004

I have a few small images, smilies, that I want to store with their corrisponding set code and smilie code in a database, but how do I go about storing the images in the database… do I just set the blob to be the value in the return of createJpeg or seomthing like that I am unsure about how to get it to work so I thought I would ask first…

to post a comment

PHP

12 Comments(s) _↴

@NedalsMar 08.2004 — #Don't save the images themselves in the database. Put them in a directory and store the URL's to the images.

@mrchipps69authorMar 09.2004 — #I don't understand... I don't see why there have to be two hard drive accesses... one to the database and one to the folder where the image is stored.... I will be accessing it at least 20 times in a row and I know that it isn't really all that big of a deal but I want to make my code most efficient and I don't see any reason why it would have to go to the database then to the file... unless you have to actually store a copy of the file in the database and create an outside file just to creatjpeg it...

@NedalsMar 09.2004 — #This is for a webpage, is it not?

How is your image going to be placed on the HTML page? Usually this is done with an <img> tag. Unless you know of another technique.

The <img> tag tells the browser to go get the image from the server. So all your PHP program needs to do is to provide the browser, via your HTML doc, with the image URL.

Unless you know another method!!

It just occured to me that you may be building an image, from multipule small images, on the server. If that's the case, there maybe some small advantage in storing the image in the database. blob is probably the way to go.

@mrchipps69authorMar 10.2004 — #it is actually for smilies I have over 1000 or more I think, that is one main reason why I don't want to save it as a bunch of files... I was generally going to access it using a single php file to create the grid of smilies of one set... and then the same php file to return an image for each smilie... it will call them based upon either the smilie code or a number representing one since there are only 20 diff smilies... I just thought that would be more efficient than accessing the file structure while I was already on the db instead of just using all the info from the db....

@NedalsMar 10.2004 — #[i]Originally posted by mrchipps69 [/i]

[B]<snip>I was generally going to access it using a single php file to create the grid of smilies of one set... and then the same php file to return an image for each smilie</snip>[/B][/QUOTE]
Have you got any code on how you propose to do this? My guess is that when you create it you will understand my earlier post.

Once you got some code, post it and I'll take a look. I may even retract this statement. ?

@DaiWelshMar 10.2004 — #I have to agree with nedals, in the scenario you describe you are much better off storing the files on disk and recording the filenames in the db.

Yes, you will have two disk accesses when [b]creating[/b] the entry (write to disk and to db) but when users are viewing it you are going to save considerable load on your server. To explain that let me run through the load each method would generate for displaying a page with 20 smilie images (assuming they are all seperate <img> tags).

All in db:

one hit on php file to get the list of smilies from db

twenty hitsfrom <img> tags on php file to get actual image contents from db

Files on disk, names in db:

one hit on php file to get the list of smilies from db

twenty hits from <img> tags on image files stored on webserver disk.

Now, when an image file is requested from your webserver it quickly establishes from the extension that it is a file it should load straight from disk, so it opens it and streams it to the browser. This is pretty damn quick.

When your php file is called however the web server has to call your php interpreter with the name of the php script, the php interpreter has to set up the environment for your script, compile it and then run it. When the script runs it has to (potentially) set up a connection to your db and read the data, then send it back to the browser.

I have no benchmarks to quote but I would expect the former to be an order of magnitude quicker than the latter, which is one reason the images are best stored in files.

Benefits of storing things in dbs are mostly related to ability to index/search, but binary image data is not generally suited to such functionality anyway.

Also (again no idea of significance of impact) the database caching will not be optimised for large chunks of arbritrary data whereas the disk cache will.

Probably more signifiantly the browser/proxy caches of your users will be able to cache images loaded directly, thereby considerably easing load on your server (and bandwidth). Images loaded via a PHP script with url parameters may be cached but it is far less certain, depending on what headers you send with them.

There are also peripheral benefits like the ability to access the files directly on the disk without the complication of getting them out of the db, but since performance was your concern that is where I have focused the response.

@mrchipps69authorMar 10.2004 — #Since I wanted to know how to put images in the blob datatype... (which you still haven't answered) before coding I haven't any code sorry... right now I could give you a pseudo code sorta though...

[code=php]If !$code {
 output grid
 // table 4x4 with pictures in current set and codes 1-16
 }else{
 add image type header
 connect to db
 select table
 send query for smilie with set and code
 output the image from the blob
 }
 [/code]

this would then create a grid... which could be done without php but I want the functionality of different smilie sets... and then if it is requested with a code it gets the smilie for that code in that set and returns the image...

this way I don't have to worry about having thousands of images with names that all have to be diff and I cant use the set code becuase that can contain bad chars like + and /.... that is why I wanted to know how to use images in a database... so they are in one place and the picture is directly associated with the smilie set and code... with no name getting in the middle.

@NedalsMar 10.2004 — #Getting your images into the database

Where are the images at the moment?

1. Open and read each image file (binary mode) into a variable.

(I don't know exactly how to do this in PHP but I'm sure your manual will tell you)

2. set your SQL query.

"INSERT INTO table_name (id, imagedata) VALUES(id=$the_id, imagedata=$the_variable)"

where imagedata is TYPE blob.

!!! BUT READ OVER THE EARLIER POSTS BEFORE YOU MESS WITH THIS !!!

When I said give me some code, I should have said, the resulting HTML code, that will be created by your PHP script. Somewhere in that final HTML code you are going to have a javascript array or <img> tags that the user will click on to set the smilie.

ie:

For EACH image... smilie01.gif .. smilie16.gif

img[n]=new Image(); img[n].src="image/smile01.gif";

or

<img src="image/smile01.gif">

In both cases, the browser will need to get the images from the 'image' directory on your server. If they don't exist (they are in the database), you'll get NO images!

Does that make sense? If you are not doing this, how are you planning to do it?

I cant use the set code becuase that can contain bad chars like + and /....[/quote]
What does this mean?

@NedalsMar 10.2004 — #I had a little free time; so here is how you do what I think you want.

1. Save all your smilies to an 'smilies' directory and name them

'sm0001.gif', sm0002.gif, ...., 'sm1000.gif'

2. Create a database table

id | sm_file | set

--------------------

1 | sm0001 | 1

2 | sm0002 | 2

3 | sm0003 | 1

etc...

You don't really need the id, but it may come in useful at a future date.

You could also a save the full image URL but it uses less memory if you do it this way.

3. When you need a smilie SET use

"SELECT sm_file FROM table_name WHERE set=1"; // or what ever set you want

4. In your PHP code, generate the appropiate HTML code. Something like this...

while ($row = mysql_fetch_row($result)) {

$img_matrix .= "<img src="smilies/".$row[0].".gif">";

}

@mrchipps69authorMar 12.2004 — #ok sorry I explained this poorly at first... I guess you need the whole lot... but the images are currently in chunks... examples at http://aim.ansme.com and I am planning on making a php program to cut it up into the respective smilie faces and save them somehow... I thought that the best option would be database because the code you use for the set of smilies is basicaly three char combination it can vary but examples are Q/q and Q+t and Etw so the first one would not work as a file name.. and would only slightly work as a directory name... I also didn't want to mess with arbitrary numbers as a filename but it may have to be done that way... and also there are 2208 single smilies which I know if i use a single image will actually be more able to be cached but if you guys really think it would be best to do it with the images on the disk I guess I will give up the fight... and the explanations... but thanks for the help you have given me lots of insight...

@NedalsMar 12.2004 — #I hate to get negative and I'm really here to help, but I'm lost on this one. Maybe another member has some more constructive comments.

[b]<snip>and I am planning on making a php program to cut it up into the respective smilie faces and save them somehow... </snip>[/b][/quote]Once you cut them up 'somehow', how do you plan to display them on an HTML page?

[i]From your earlier post[/i][b]

output the image from the blob[/b][/quote]Output to what?

... from the 'aim.ansme.com site

"....and paste the generated text to your IM window".

What's an IM window? Do you l plan to use one?

Do you plan to download the images from this site?

Please note: "Copyright © 1997 - 2004 CompuCated Technologies".

@mrchipps69authorMar 12.2004 — # If !$code {

output grid

// table 4x4 with pictures in current set and codes 1-16

}
[/QUOTE]

this is where the images are displayed... they are img tags referencing this same php file only with ?set=...&code=...

the next part I did just returns an image file...

[QOUTE]

else{

add image type header

connect to db

select table

send query for smilie with set and code

output the image from the blob

}

[/QUOTE]

This means the image is outputed using imagejpeg() from the gd library... you only output the image as if it were read from the server... that is what I am doing to show it in the html....

Also in #PHP _↴

Edit MySQL data with php Help with strstr()most effective form validation

Success!

Help @mrchipps69 spread the word by sharing this article on Twitter...

Tweet This

storing images in a database

12 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

storing images in a database

12 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

12 Comments(s) _↴

Also in #PHP _↴