How to Store and Retrieve Images from a MySQL Database Using PHP?

Storing and Retrieving Images from a MySQL Database with PHP

In order to store images in a MySQL database and retrieve them using PHP, several steps need to be taken.

Creating the MySQL Table:
Begin by creating a table in MySQL to store the image data. An example of a suitable table structure titled "testblob" is provided below:

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, obtain the image data and its dimensions using PHP's file utilities. Connect to the MySQL database and execute an SQL query similar to the following, ensuring that the image data is escaped to prevent SQL injection:

$imgData = file_get_contents($filename);
$size = getimagesize($filename);
$sql = sprintf("INSERT INTO testblob
    (image_type, image, image_size, image_name)
    VALUES
    ('%s', '%s', '%d', '%s')",
    mysql_real_escape_string($size['mime']),
    mysql_real_escape_string($imgData),
    $size[3],
    mysql_real_escape_string($_FILES['userfile']['name'])
    );
mysql_query($sql);

Retrieving an Image:
To display an image from the database on a web page, connect to the database and execute a query to retrieve the image data. Set the appropriate content type header and display the image using PHP's echo command:

$sql = "SELECT image FROM testblob WHERE image_id=0";
$result = mysql_query("$sql");
header("Content-type: image/jpeg");
echo mysql_result($result, 0);

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