Images into MySQL

[webmannw]

Hi all,

I need a way to allow users to upload a picrure into a MySql database from a form on a web page.

Could anyone give this PHP newbie a shove in the right direction?:)

Thanks!

[vincevincevince]

Three popular ways:

1/ Hardest method. Save the image in the database as type BLOB. Load the image file either from the upload, as normal, or from another disc location. Read the file into a variable using file_get_contents() or similar. You need to pay careful attention to the type of the file, either using $_FILE[whatever][type] which is e.g. "Image/gif".

Now, when you output the file, send a header("Content-type: Image/gif"); and then output the contents of the BLOB. It should work fine.

2/ Easier method. Just upload the file, save it to disk, but store the filename in the database.

3/ Easy, but not easiest method. Upload the file, save it to disk using a unique identifier as filename (i.e. the auto_increment column from mysql), then save the original filename in the mysql database.

[mincklerstraat]

I'd use vincevincevince #3 method: just make sure you change the name of the file to fit the mysql row id, like '3.gif' or whatever - you don't have to add an extra row to your db, and that's not information anyone really needs or has got to search for.

The mysql people themselves recommend storing as a file and not as a blob in the db.

[webmannw]

Thanks guys you your advice. Till try #3.

One question though....when the db record containing the link to the pic is deleted I assume that the corresponding pic needs to be manually deleted from the pics folder?

Thanks again!

[mincklerstraat]

yeah, unless you automate your script to do that for you.

[webmannw]

Ok..managed to the images into a folder on the server and a link into MySQL.

The problem is that users are going to be uploading images so is there a simple way to rename an image as it is uploaded into the folder on the server...maybe with an auto-increment number?

Almost there...thanks again!

[JasonHamilton]

I used to use this method, but

1) it makes for some very large database backups.

2) any mysql corruption and your images are fubar.

It's actually not that hard to store the files to disk, I ilke using md5() a lot for file names.

[mincklerstraat]

Yeah, there's a simple way to rename. Look at the php.net thingie on uploads, and I think it'd probably be easiest to change the name to an autoincrement number that you grab from the db in the 'move the upload' stage of the game. If you're using somebody else's upload scrip, check out rename().

[webmannw]

Hi all...Thnaks for your advice.

I really apologise for being a dunce here but how do I save the file to disk using the the auto_increment column from mysql, then save the original filename in the mysql database.

I have searched around but cant find any info.

Thans again

[mincklerstraat]

You need to know which row the image is supposed to be associated with - like, if you're having a user input new content, it's the newest row - so do a SELECT id FROM whatever LIMIT=1 after you've input the info into the database, and that will give you the autoincrement field's value (in the case that it's named id of course) - and it's this value you need to use when you change the file's name, like to $var.'.gif'; later, in the rest of the script, you always will just use '<img src="'.$row['id'].'png'>. Assuming, of course, it's only .pngs that are being used, which I guess is a big assumption. If you really have to accept multiple formats, since you'll already have to have a field or piece of data corresponding to the extension, well, you may as well just save the whole name and forget about the id thing.

Note: You don't have to do *both*. Either get the id from the db, and always name your files $id.gif, or else save the name of the file in the db. Quite obviously, these are mutually exclusive techniques.