Pls i need a code on inserting a picture in a mysql db. Thanks

Don't do that. You should upload the image to a special image directory and just store the filename in MySQL.

This is what you need to do

just read
http://w3schools.com/php/php_file_upload.asp

Seun: Don't do that. You should upload the image to a special image directory and just store the filename in MySQL.

why? what difference does it make? slight speed difference?

@Seun, you are very correct, uploading images into the db server with cause performance problems
@OP , you are better off uploading the image to a directory

ekt_bear:

why? what difference does it make? slight speed difference?

It makes a lot of different, if it's a small site, mehh, but the large it gets, it takes a toll on READing and Writing, not to mention you can't index, or trigger, etc and it's just bad, bad, DB design.

Eh. mySQL if I remember correctly has the BLOB data type. So presumably sql DBs are capable of handling binary data of some sort.

ekt_bear: Eh. mySQL if I remember correctly has the BLOB data type. So presumably sql DBs are capable of handling binary data of some sort.

I do agree to your point, it can handle images. The issue is, it doesn't scale upwards well.

Imagine have to store people's uploads in your DB, and then retrieve several of them. When your app gets popular, you'r DB will lockup soon or later (not to mention having a huge DB), it's very doable, but expensive. An efficient manner is (which is the accepted standard), store the image in a file sys, and have a reference to it in the DB.

On some cloud computing hosts (e.g. heroku), you aren't given write access to the file system.

Plus, for security reasons, perhaps I don't want any tom d[i]i[/i]ck and harry to be able to write to my FS

If you are talking about storing VIDEO in a DB, sure. But images should be easy as cake

delete

Whether or not to store images in a DB or the filesystem is sometime one of those "holy war" type of debates; each side feels their way of doing things is the one right way.

In general:

To store in the DB:

Easier to manage back-up/ replicate everything at once in once place.

Helps with your data consistency and integrity.

You can set the BLOB field to disallow NULLs.

To store on the filesystem:

A filesystem is designed to serve files. (but its not a must u follow dat)

some will tend to come down on the side of the file system bcuz dey blive it scales much better.

But dis is wrong cause the database also scales much better.

depending on the size of your project, either choice will likely work fine.

With NoSQL, the differences are even less apparent.
also on issue of performance hit i dont see that as a problem when the files are much.

see what u store in d database is d string format of the file. u put it in a couloumn. last i checked d mysql has d BLOB and LONG bloB. while d Blob can take over 16million bytes PER coloumn while d LONG blog can take above.

this shud not b a problem if you use an Oracle db.

As with most issues, it's not as simple as it sounds.

There are cases where it would make sense to store the images in the database.

You are storing images that are changing dynamically, say invoices and you wanted to get an invoice as it was on 1 Jan 2007?


The government wants you to maintain 6 years of history Images stored in the database do not require a different backup strategy.

Images stored on filesystem do


It is easier to control access to the images if they are in a database.

Idle admins can access any folder on disk.
It takes a really determined admin to go snooping in a database to extract the images

On the other hand there are problems associated
Require additional code to extract and stream the images Latency may be slower than direct file access Heavier load on the database server.


but d load should not be a problem if you have the application managed well. also on oracle db the load happens to be less a night mare.

on mysql u need to manage it effectively and you will get your desired result.

Seun: Don't do that. You should upload the image to a special image directory and just store the filename in MySQL.

seun i have known you to be an adamant preacher of filesystem to database. yes

i see your post on stackoverflow, i see your comments to me i see your points as bin flawed.

also have you tot off the problem you encounter in deleting a file on the file system and the url in d database?

blive me at times some file system tends to be funny especially.

i know a nigerian host that plays with your file permission like it is dia own. and u begin avin problems.

D question now is how do reference an image uploaded to a file in mysql db using php

leastpay: D question now is how do reference an image uploaded to a file in mysql db using php

There are a few ways to do it, PHP has a bunch of built-in function for dealing with system files (where your image would sit), read through this PHP Man section and Google around to see how the problem is approached (and quite frankly, you will be re-inventing a wheel trying to write this vanilla) :: http://www.php.net/manual/en/function.file.php

(this assume the upload is from a verified user on your app)
The general idea is, upload the file and check it's something you expect, prepend or added unique filename to the path (store that to the DB), and it's be referenced in the app per user, somethings like /home/wwwroot/images/[username]/local/[somerandomSHA].jpg < (this sits in the DB)

So your DB srtucture would look something like:

[User] -- has many --> [Images]

[Images Table] : user_id (FK) | image_name | image_ref

user_id: this is a foreign that attaches to the repective User
image_name: the name the user gives their stuff
image_ref: the real path it sit on the DB

AS you can see, that approach is a lot more flexible, takes some wrapping head around, but it's the common pattern.