How can I store and retrieve images from a MySQL database using PHP?

Question

How can I insert an image in MySQL and then retrieve it using PHP?

I have limited experience in either area, and I could use a little code to get me started in figuring this out.

Answer 1

General tips:

• Read a MySQL tutorial.
• Create MySQL table with binary blob column
• Load image from disk, insert into database

To read out:

• Select correct column from MySQL
• Write to disk, display on screen, etc.

EDIT: Removed original comments on etiquette, because the question has been modified with a much nicer and humbler tone.

Answer 2

See this article. First you create a MySQL table to store images, like for example:

create table testblob (
    image_id        tinyint(3)  not null default '0',
    image_type      varchar(25) not null default '',
    image           blob        not null,
    image_size      varchar(25) not null default '',
    image_ctgy      varchar(25) not null default '',
    image_name      varchar(50) not null default ''
);

Then you can write an image to the database like:

$imgData = file_get_contents($filename);
$size = getimagesize($filename);
mysql_connect("localhost", "$username", "$password");
mysql_select_db ("$dbname");
$sql = "INSERT INTO testblob
    ( image_id , image_type ,image, image_size, image_name)
    VALUES
    ('', '{$size['mime']}', '{$imgData}', '{$size[3]}', 
     '{$_FILES['userfile']['name']}')";
mysql_query($sql);

You can display an image from the database in a web page with:

$link = mysql_connect("localhost", "username", "password");
mysql_select_db("testblob");
$sql = "SELECT image FROM testblob WHERE image_id=0";
$result = mysql_query("$sql");
header("Content-type: image/jpeg");
echo mysql_result($result, 0);
mysql_close($link);

Answer 3

great ,thanksssssssss

Answer 4

Be aware that serving images from DB is usually much, much much slower than serving them from disk.

You'll be starting PHP process, creating db connections, having DB use the same disk and RAM for cache as filesystem would, transfering it over few sockets and buffers and then pushing out via PHP, which by default makes it non-cacheable and adds overhead of chunked HTTP encoding.

OTOH modern web servers can serve images with just few optimized kernel calls (memory-mapped file and that memory area passed to TCP stack), so that they don't even copy memory around and there's almost no overhead.

That's difference between serving 20 or 2000 images in parallel on average machine.

So don't do it unless you absolutely need transactional integrity (and actually even that can be done with just image metadata in DB and filesystem cleanup routines) and know how to improve PHP's handling of HTTP to be suitable for images.

Answer 5

i also recommend thinking this thru and then choosing to store images in your file system rather than the DB .. see here: http://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay