I have a large table which will have content added to it via a form but i also require to be able to upload one image in three of the fields. How easy/possible is this? I have never tried image uploads let alone multiple image uploads all at once.
An example and any help is greatly appreciated.
Dont put the images into the table - put their filepaths into the table instead.
I cant just put their filepath as its kind of a cms style and so each image needs to be uploaded alongside its data
Then i’d find a different CMS.
I agree with StartLion, it’s never a good idea to save images on the database but if you really want to, i THINK the blob data type will allow you to store images on it. to upload multiple files you could create three <input type=‘file’ /> in your form. Just make sure the names are different ex:
<input type='file' name='file_1' />
<input type='file' name='file_2' />
<!--or-->
<input type='file' name='file[]' />
<input type='file' name='file[]' />
I would suggest using a jquery or moo tools plugin to handle multiple uploads
I have heard of situations where NOT placing the images in the database resulted in all the images being lost because they weren’t backed up.
There are as many arguments for inserting the images into the database as there are for not doing so. If you read chapter 12 of the book “SQL Antipatterns” you will find that the author argues that storing the images outside the database the author argues that you might be creating a maintenance nightmare. For example, they don’t obey delete row requests, they don’t obey transaction isolation, they don’t obey ROLLBACK, they don’t obey access privileges. That chapter also gives example code for how to handle images both inside and outside the database for once you have worked out which of the two is actually going to cause you fewer problems in the future.
I would argue that the number of times you will lose data will pale in comparison to the number of times you will search/index across that data… but that’s more a question for the SQL forum.
If your mySQL server is on the same machine as the web data, then if you lose the machine, it wont matter whether the data was in the database or the site… gone is gone.
I’ve never had the need to store images in the database since I’ve never been in a situation where I’ve needed transactions and image handling at the same time but I can’t imagine trying to serve thousands of images and having those images come from the database unless of course, this is better performance wise.
It will if the IT support staff are backing up the database but are not backing up the images because no one asked them to - more common than you might think. Then the database can be restored onto a new server but the images will still be gone. After all if all the data is presumed to be in the database then the rest only needs to be backed up once since the application will not change.
Of course since deleting records from the database will not affect the images it will be likely that most of the images are no longer required anyway as the data they belonged with is long gone but since they are not in the database the integrity of the data isn’t maintained.
Of course since deleting records from the database will not affect the images it will be likely that most of the images are no longer required anyway as the data they belonged with is long gone but since they are not in the database the integrity of the data isn’t maintained.
I would think that this assumption really depends on the scenario the web developer is in. Like if more that one record uses the same image or something like that.
Will saving images to the database be any better performance wise?
You would have the additional overhead if storing in the database of having to convert the file to a blob when you store it and to convert it back to a file when you retrieve it. So storing it separately would possibly be slightly faster.
It really depends on whether you require database features such as data integrity and access control to be applied to the image. If the image is only supposed to be accessed by people who are logged in then the only way to enforce that is to store the image in the database - if it is stored separately then it can be accessed directly by anyone who figures out its address whether they are logged in or not.
There are advantages and disadvantages both ways and you need to make the decision on which way to go based on which way works the best for your particular requirements.
If you do decide to insert the images into your mySQL database in order to make use of the database management features then defining a field as a MEDIUMBLOB will allow for images up to 16Mb in size.
You could then update that field using
UPDATE tablename SET imagefieldname = LOAD_FILE('theimage.jpg') WHERE ...
and retrieve it back into a file using
SELECT imagefieldname INTO DUMPFILE 'tempimage.jpg' FROM...
alternatively you can set up PHP that is generating the image and simply output the content of the blob after writing the appropriate headers.