RE: [PHP-DB] using php and mysql with images

Addressed to: <Ian.Willis@spider.ento.csiro.au> <php-db@lists.php.net> ** Reply to note from <Ian.Willis@spider.ento.csiro.au> Thu, 29 Jun 2000 13:31:44 +1000 > > Yes the performance may suffer but storing all the information in a > database make sense for a number of applications where The data is > structured and the image is part of the structure, performance gains > may be better realized by hardware and other optimisations. The > quantity of data is such that the organisational benefits of the > database more than outweighs the performance hit. > Even if the images are in the same table as the data on the insect, you still have to have one HTTP transaction to read the text data, and one or more additional HTTP transactons to transfer each image. Each of those transactions has to connect to the database, do a select, transfer its subset of data and send it over the web. Until the HTML standards provide a way to send images inline, this will be true. > Basically storing images external to the database is a performance > kludge, it is inelegant but useful in a number of situations. Actually it is probably less of a performance issue, the time difference is miliseconds or less, than a programming hassle issue. > Once > this fact is accepted the whole response of images is database is bad > can be seen as a load of twaddle. I did not say they are bad, just lots of extra work with little advantage. HTML is designed to serve images from files, and does it well. > An example is that I work for a > research organisation that studies insects in Australia, there are > around 200000 species of insects in australia. Each species may have > have multiple blob associated with it such as DNA sequences, images > etc. Will this be a public database? I'd like to see it when you are done... > Should I have in excess of 200000 image files in a directory? No. If you have 200000 images they probably need to be in a directory tree. The terminfo database is a good example. If you have too many entries in a single directory it takes too long to search for the desired file. > A > useful answer would be to point to some figures that allow the user to > glean when storing images in a database would be appropriate, When other, non-web programs use the images, and need to get them from the database. > when it > is a borderline case and when it is silly. When the images are only sent out thru a web server. > Currently I don't know > these things but I would appreciate some rough benchmarks came out > that allowed users to find an appropriate compromise between > performance and data integrity. > I don't have any benchmarks, and have never heard of them. Maybe you could try it both ways and tell us... Anyway how to do it: UPLOAD: Start by reading chapter 18. Handling file uploads, in the PHP manual. This covers what is needed to upload a file thru the browser. You probably want to use POST method. One very important thing to note: ---------------------------------------------------------------- The PHP script which receives the uploaded file should implement whatever logic is necessary for determining what should be done with the uploaded file. You can for example use the $file_size variable to throw away any files that are either too small or too big. You could use the $file_type variable to throw away any files that didn't match a certain type criteria. Whatever the logic, you should either delete the file from the temporary directory or move it elsewhere. ---------------------------------------------------------------- In your case you will want to open the file, and read it into your database. Section XX. Filesystem functions of the manual covers what you will need. (fopen, fread, fclose should do it.) Read the data into a variable and INSERT or UPDATE your table with it. You need to do this in the page that is referenced in the <FORM Action=""> of the form that prompts for the images to upload. DOWNLOAD: I've seen two ways to handle this. One involves a single database query that obtains the base page, and the images, writes the images to temporary files, then includes the temporary file names in the <IMG> tags of the generated page. This is a worse housekeeping nightmare than finding a way to store 200000 image files, as you have to generate unique names, and make sure obsolete images are deleted. To make it interesting, the program that sends the html text and created the image files will have died before some of the images start transferring, so you can't delete them from there. A better choice involves creating a program to retrieve images. You point your <IMG> tags at this program, and pass the identity of the desired image to it. While you are composing the <IMG> tag you should try to have some way to have the size of the image available so you can fill in the optional height= and width= parameters of each <IMG> tag. The two methods of sending the image identity that come to mind are: <IMG SRC="/getimage.php?Bug=03428&ImgType=dna" height=150 width=150> This will create variables $Bug and $ImgType in the global context, and in $HTTP_GET_VARS[] that you can use in your SELECT. and <IMG SRC="/getimage.php/bug03428/dna.jpg" height=150 width=150> With this technique you retreive the selection information from $PATH_INFO, or possibly one of the other variables you can look at with phpinfo(). There is a technique using ForceType and <Location> in you httpd configuration files so you don't have to have the .php in the program. If you want to do this either search the php3 mailing list archive for ForceType and my email addresss, or let me know. I'll see if I can find one of the messages where I explained how to set this up. Once you decide how you want to select the image to send, write a program that SELECTs it from the database, send a header like 'Content-Type: image/jpeg' and transfer the image. Rick Widmer Internet Marketing Specialists www.developersdesk.com