How to Store and Retrieve Images in MySQL using PHP?

Storing and Retrieving Images in MySQL with PHP

Storing and retrieving images in a MySQL database allows you to manage large amounts of visual content effectively. This article provides a comprehensive guide to achieve this task using PHP.

Database Structure

First, you need to create a MySQL table to store images. An example table structure for this purpose is as follows:

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 ''
);

Inserting an Image

To insert an image into the database, you can use the following PHP code:

$imgData = file_get_contents($filename);
$size = getimagesize($filename);
$link = mysqli_connect("localhost", "username", "password", "dbname");
$sql = sprintf("INSERT INTO testblob
    (image_type, image, image_size, image_name)
    VALUES
    ('%s', '%s', '%d', '%s')",
    mysqli_real_escape_string($link, $size['mime']),
    mysqli_real_escape_string($link, $imgData),
    $size[3],
    mysqli_real_escape_string($link, $_FILES['userfile']['name'])
    );
mysqli_query($link, $sql);

Retrieving an Image

To retrieve an image from the database, you can use the following PHP code:

$link = mysqli_connect("localhost", "username", "password", "dbname");
$sql = "SELECT image FROM testblob WHERE image_id=0";
$result = mysqli_query($link, $sql);
header("Content-type: image/jpeg");
echo mysqli_result($result, 0);
mysqli_close($link);

The above is the detailed content of How to Store and Retrieve Images in MySQL using PHP?. For more information, please follow other related articles on the PHP Chinese website!