How to store image in database using php and mysql

Introduction

Image uploading is very common functionality in any website. Image uploading is easy in PHP we have two ways, we can store images either in the server or in a database. The most common way is You can store images on the server. But sometimes you can’t save images directly into server for security reason or some other reason. So you can store images/files directly into the database. Today I will show you, How to store image in database using PHP and MySQL . And how to store images on a server using PHP.

You may also like : JQUERY IMAGE UPLOAD PREVIEW BEFORE UPLOAD IT 

How to store image in database using php and mysql

We will follow five steps to store images in database:
1. Create a Database
2. Create a Database Table
3. Make an HTML form to upload images
4. Connect to database and store selected images
5. Display images stored in database

Let’s start with each step one by one:

1. Create database

The very first step is database creation. We will use following query to create database:

 CREATE DATABASE demo;

Note:  demo is name of database you can change it with any name.

2.  Create a Database Table

Now we need table in database so we can store information about file information. Let’s run following command to create table in database:

CREATE TABLE images
(
id INT PRIMARY KEY AUTO_INCREMENT,
images_path VARCHAR(100),
images_data BLOB
);

Note: To store an images into database we have to use ‘BLOB‘ datatype of images_data column. MySQL only uses BLOB to store binary data such as images/files. You can use any type such as TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB based on your size requirements. You can read more about this here.

 3. Make an HTML form to upload images

It’s time to create HTML page which will let users to select files so they can upload those files to the database. We will create index.html page which will have form tag with post method:


<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
</head>
<body>

<form action="upload.php" method="post" enctype="multipart/form-data">

<table style="width:100%" border="1" cellspacing="5" cellpadding="5">
<tbody>

<tr>
<td><label>Please select file</label></td>
<td>
<input type="file" name="image" value="" />
</td>
</tr>
<tr><td colspan="2"><input type="submit" value="Upload" /></td></tr>
</tbody>
</table>
</form>
</body>
</html>

Note: To upload any file, It is mandatory to set enctype attribute as, enctype = “multipart/form-data” and action=”a target path where form-data will be posted” and form method as, method=”post” otherwise your code will not work at all. This is most common mistake many beginners do.

 

 4. Connect to database and store selected images

As we age going to use PHP and MySQL we will create new PHP file with name ‘upload.php’. You can see we have used this file in the form’s action attribute.


<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "demo";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

//check if files not empty
if (!empty($_FILES["image"]["name"])) {
//Get the content of the image and then add slashes to it
$temp_image=addslashes(file_get_contents($_FILES['image']['tmp_name']));

//Insert the image name and image content in table
$insert_query="INSERT INTO images(images_data) VALUES('$temp_image')";

if ($conn->query($insert_query) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();

}else
{
$conn->close();
echo "Please select file.";

}
?>

That’s all it’s time to display images from database so let’s move to next step.

5. Display images stored in database

This is also important step in which we are going to display images stored in database. We will create two separate file where one will fetch all images from database and second display all images one by one. Basically we will use Select MySQL query for list all images. Let’s create files.php file with following code:


<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "demo";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
//let's execute select query

$query = 'SELECT * from images';

$result = $conn->query($query);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<img src =fetch_single.php?id=".$row['id']." width=200 height=200 >";
}
} else {
echo "0 results";
}
$conn->close();
?>

 

Now create second file with name fetch_single.php in which we will pass image ID in query string and then display that image from database. Let’s do this guys :


<?php
header("content-type:image/jpeg");
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "demo";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
//let's execute select query

$query = 'SELECT * from images where id ='.$_GET['id'];

$result = $conn->query($query);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo $row['images_data'];
}
} else {
echo "0 results";
}
$conn->close();
?>

Note: Make sure you have add header(“content-type:image/jpeg”); on top of file as this header will convert blob data to image.

Now just navigate to http://localhost/upload/files.php and you will see all uploaded Images here.

HOW TO STORE IMAGE IN DATABASE USING PHP AND MYSQL

 

How to upload image on server using PHP

Now we will upload image on server instead  of database.  We will follow these steps:

1. Change action in index.html file

We will follow same steps but only one change in `index.html` just change action = ‘upload_server.php’ so we will make this file in next step.

2. Upload files on server

In this step we get the image and store the image in directory and store the path of the image with name in database. As we know we have add column for this in database with name images_path so we are going to use that column.


<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "demo";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

//check if files not empty
if (!empty($_FILES["image"]["name"])) {
//Get the content of the image
$temp_image=$_FILES['image']['tmp_name'];
$image_name=$_FILES["image"]["name"];

//folder name where we will store images
$folder="images/";

move_uploaded_file($temp_image, "$folder".$image_name);

//Insert the image name and image content in table
$insert_query="INSERT INTO images(images_path) VALUES('$image_name')";

if ($conn->query($insert_query) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();

}else
{
$conn->close();
echo "Please select file.";

}
?>

3. Display images from server

For display Images from server create new php file ‘server_files.php’ and add following code:


<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "demo";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
//let's execute select query

$query = 'SELECT * from images';

$result = $conn->query($query);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<img src =images/".$row['images_path']." width=200 height=200>";
}
} else {
echo "0 results";
}
$conn->close();
?>

HOW TO STORE IMAGE IN DATABASE USING PHP AND MYSQL

Download Code

Thanks for reading 🙂 keep sharing (y)

 

Total Views (184)