Sometimes, we want to store and retrieve images from a MySQL database using PHP.
In this article, we’ll look at how to store and retrieve images from a MySQL database using PHP.
How to store and retrieve images from a MySQL database using PHP?
To store and retrieve images from a MySQL database using PHP, we can store it as a blob.
For instance, we write
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 ''
);
to create the testblob
table that stores the image
field as a blob.
Then we write
$img_data = file_get_contents($filename);
$size = getimagesize($filename);
mysql_connect("localhost", "$username", "$password");
mysql_select_db("$dbname");
$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($img_data),
$size[3],
mysql_real_escape_string($_FILES["userfile"]["name"])
);
mysql_query($sql);
to insert the $image_data
image file as a blob by calling mysql_real_escape_string
with $img_data
to create a string from it.
Then we use '%d'
to interpolate $img_data
as an integer.
Then we call mysql_query
with $sql
to run the $sql
statement.
Next, we get the image from the database by writing
$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);
We call mysql_query
with $sql
to get the image from the database.
Then we call mysql_result
with $result
to return the image.
And then we echo
it.
We set the Content-type
header with header
so it’ll be returned as a jpeg image.
Conclusion
To store and retrieve images from a MySQL database using PHP, we can store it as a blob.