The following is a simple written tutorial with screenshot images and source code on how to insert, retrieve, and update images in MySQL database using PHP.
Why would you want to do something like this? I guess there’s only one reason: because you can! This will work for any kind of website with its own server (like Apache, Tomcat, etc.) and the most popular open-source web programming language PHP. By using this technique you can also create a simple PHP image gallery.
You’re going to need a few things before we get started:
- A working MYSQL database
- Working knowledge of MySQL databases
- Knowledge of HTML
- A working PHP installation
- Some sample images that will be inserted into your database table.
Also, it wouldn’t hurt if you have some basic knowledge about files, directories from operating systems.
Ok, so let’s start this! To be able to insert image into MySQL database using PHP, we’ll need to follow the steps below:
CONTENTS
1. Create an Image Table
In order to store an image file into a database, we have to create a table first. The following SQL query will create an image table in your database.
CREATE TABLE IF NOT EXISTS `image` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`image` longblob NOT NULL,
`title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`uploaded` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;As per the MySQL documentation, “A blob is a type for storing large, variable-length objects“. There are different types of blob i.e. TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB.
If you need to store binary data these are the types to use as they let you store data of variable length without taking up too much space on disk or in RAM.
2. Create Database Configaration File
Create a database configuration file is very easy. You can connect the database in PHP MySQL easily with mysqli and mysqli_* functions. Let’s create a database config file i.e. dbConfig.php and connect to the database.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "codehasbug";
// Create connection
$conn = new mysqli ($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>$servernameis the name of MySQL server.$usernameis database username i.e. root.$passwordis the database password.$dbnameis name of database.
if $conn->connect_error returns true, so display ‘Connection failed‘ message.
3. Create image upload page
Now we need to create a page where we can select an image and upload it to the database. For this example, I am using Bootstrap 4 to create a simple page layout. here is the preview:
I have added one text field where we can give our image title, one file upload field, and add two buttons i.e. Add Image and View Image.
Add Image will submit the form to upload the image into the database and View Image will show the list of uploaded images from the database. Now create a file addImage.php and insert the below code.
<?php
//include dbConfig file
require_once 'dbConfig.php';
//check if form submitted
if(isset($_POST["submit"])){
$error = false;
$status = "";
//check if file is not empty
if(!empty($_FILES["image"]["name"])) {
//file info
$file_name = basename($_FILES["image"]["name"]);
$file_type = pathinfo($file_name, PATHINFO_EXTENSION);
//make an array of allowed file extension
$allowed_file_types = array('jpg','jpeg','png','gif');
//check if upload file is an image
if( in_array($file_type, $allowed_file_types) ){
$tmp_image = $_FILES['image']['tmp_name'];
$img_content = addslashes(file_get_contents($tmp_image));
$title = $_POST['title'];
//Now run insert query
$query = $db->query("INSERT into image (image, title) VALUES ('$img_content', '$title')");
//check if successfully inserted
if($query){
$status = "Image has been successfully uploaded.";
}else{
$error = true;
$status = "Something went wrong when uploading image!!!";
}
}else{
$error = true;
$status = 'Only support jpg, jpeg, png, gif format';
}
}
}
?>
<!doctype html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
<title>Store, retrieve, and update image from database in PHP</title>
</head>
<body>
<div class="container h-100 mt-5">
<div class="row h-100 justify-content-center align-items-center">
<div class="col-8 mb-4">
<h1>Upload Image</h1>
<?php
if( isset($error) ){
if(!$error){
echo '
<div class="alert alert-success alert-dismissible fade show text-center" role="alert">
<strong>Well done!</strong> '.$status.'
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>';
}else{
echo '<div class="alert alert-danger alert-dismissible fade show text-center" role="alert">
<strong>Oh snap!</strong> '.$status.'
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>';
}
}
?>
</div>
<form class="col-8" method="post" action="addImage.php" enctype="multipart/form-data">
<div class="form-group">
<label for="title">Title</label>
<input type="text" class="form-control" id="title" name="title" placeholder="Enter Title" required>
</div>
<div class="form-group">
<label for="image">Select Image</label>
<input type="file" class="form-control-file" id="image" name="image" placeholder="select image" required>
</div>
<div class="form-group">
<button type="submit" name="submit" class="btn btn-success">Add Image</button>
<a href="viewImage.php" class="btn btn-info">View Image</a>
</div>
</form>
</div>
</div>
<!-- Optional JavaScript -->
<!-- jQuery first, then Popper.js, then Bootstrap JS -->
<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.3/umd/popper.min.js" integrity="sha384-ZMP7rVo3mIykV+2+9J3UJ46jBk0WLaUAdn689aCwoqbBJiSnjAK/l8WvCWPIPm49" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
</body>
</html>In the above code, the HTML part is pretty straightforward. Let’s check the PHP part step by step.
- I have included
dbConfig.phpfile where our database configaration is present. - By using
isset($_POST["submit"])function, we are checking if the method is POST. basename()andpathinfo()will return the file name and extension. Here I am using basic file extension checking. If you want to check exact file types, use mime_content_type() function.- I have also set some allowed file types i.e.
array('jpg','jpeg','png','gif'). By using this array we are going to filter uploaded file. - To store an image into database, we need to get it’s content first. To get the content I am using file_get_contents() and then addslashes() on it.
$db->query("INSERT into image (image, title) VALUES ('$img_content', '$title')");will insert image content and title into databse.
4. Retrive the image from database and display in a table.
The View Image button will open a new page where we will fetch the image from the database and display it in a table. You can also display images from a folder without using a database.
Make a viewImage.php file and paste the below code:
<?php
//include dbConfig file
require_once 'dbConfig.php';
//get all images
$images = $db->query("SELECT * FROM image ORDER BY uploaded DESC");
//no of rows
$no_of_rows = $images->num_rows;
?>
<!doctype html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
<title>Store, retrieve, and update image from database in PHP</title>
<style type="text/css">
.img-200{
width: 200px;
}
</style>
</head>
<body>
<div class="container h-100 mt-5">
<div class="row h-100 justify-content-center align-items-center">
<div class="col-10 mb-4">
<h1>View Image</h1>
</div>
<div class="col-10 m-auto">
<table class="table table-striped table-border text-center">
<thead>
<th>#</th>
<th>Image</th>
<th>Title</th>
<th>Uploaded</th>
<th>Option</th>
</thead>
<tbody>
<?php
//check if image present
if( $no_of_rows > 0 ){
//counter
$i = 1;
while( $img = $images->fetch_object() ){
$img_file = base64_encode( $img->image );
?>
<tr>
<td><?php echo $i;?></td>
<td>
<img class="img-200 img-thumbnail" src="data:image/jpg;charset=utf8;base64,<?php echo $img_file; ?>" />
</td>
<td><?php echo $img->title;?></td>
<td><?php echo date( 'm/d/y', strtotime($img->uploaded));?></td>
<td>
<a class="btn btn-sm btn-success" href="<?php echo 'editImg.php?id='.$img->id?>">edit</a>
<a class="btn btn-sm btn-danger" href="<?php echo 'delImg.php?id='.$img->id?>">del</a>
</td>
</tr>
<?php
$i++;
}
}
?>
</tbody>
</table>
</div>
</div>
</div>
<!-- Optional JavaScript -->
<!-- jQuery first, then Popper.js, then Bootstrap JS -->
<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.3/umd/popper.min.js" integrity="sha384-ZMP7rVo3mIykV+2+9J3UJ46jBk0WLaUAdn689aCwoqbBJiSnjAK/l8WvCWPIPm49" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
</body>
</html>Let’s check the PHP and HTML part step by step.
- By using
$db->query("SELECT * FROM image ORDER BY uploaded DESC");code, we are making a query to the image table order by uploaded field. - Check if there is any image in our table by using if( $no_of_rows > 0 ). If the value is greater than 0, fetch the record by fetch_object().
- Convert the BLOB data to base64 string using base64_encode().
- Finally, append the base64 string with
data:image/jpg;charset=utf8;to display the image in<img>tag.
5. Edit image in database.
Now let’s check how to update the images stored in a database using image id.
The code is almost the same like addImage.php. But if you notice in viewImage.php file, we were passing the image ID as a query string i.e. editImage.php?id=10. So, make a PHP file with the name editImage.php and add the below code.
<?php
//include dbConfig file
require_once 'dbConfig.php';
if( isset($_GET['id']) ){
//get image id
$img_id = $_GET['id'];
//check if image is present
$img = $db->query("SELECT * FROM image WHERE id = $img_id");
//no of rows
$no_of_rows = $img->num_rows;
if( !$no_of_rows ){
die("Image not found!");
}
}//end of get check
else{
die("Image not found!");
}
//check if form submitted
if(isset($_POST["submit"])){
$error = false;
$status = "";
//check if file is not empty
if(!empty($_FILES["image"]["name"])) {
//file info
$file_name = basename($_FILES["image"]["name"]);
$file_type = pathinfo($file_name, PATHINFO_EXTENSION);
//make an array of allowed file extension
$allowed_file_types = array('jpg','jpeg','png','gif');
//check if upload file is an image
if( in_array($file_type, $allowed_file_types) ){
$tmp_image = $_FILES['image']['tmp_name'];
$img_content = addslashes(file_get_contents($tmp_image));
$title = $_POST['title'];
//Now run update query
$query = $db->query("UPDATE image SET image = '$img_content', title = '$title' WHERE id = $img_id");
//check if successfully inserted
if($query){
$status = "Image has been successfully updated.";
}else{
$error = true;
$status = "Something went wrong when updating image!!!";
}
}else{
$error = true;
$status = 'Only support jpg, jpeg, png, gif format';
}
}
}//end of post check
$img = $db->query("SELECT * FROM image WHERE id = $img_id");
$row = $img->fetch_row();
//image title
$img_title = $row[2];
?>
<!doctype html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
<title>Store, retrieve, and update image from database in PHP</title>
</head>
<body>
<div class="container h-100 mt-5">
<div class="row h-100 justify-content-center align-items-center">
<div class="col-8 mb-4">
<h1>Update Image</h1>
<?php
if( isset($error) ){
if(!$error){
echo '
<div class="alert alert-success alert-dismissible fade show text-center" role="alert">
<strong>Well done!</strong> '.$status.'
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>';
}else{
echo '<div class="alert alert-danger alert-dismissible fade show text-center" role="alert">
<strong>Oh snap!</strong> '.$status.'
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>';
}
}
?>
</div>
<form class="col-8" method="post" enctype="multipart/form-data">
<div class="form-group">
<label for="title">Title</label>
<input type="text" class="form-control" id="title" name="title" placeholder="Enter Title" value="<?php echo $img_title;?>" required>
</div>
<div class="form-group">
<label for="image">Select Image</label>
<input type="file" class="form-control-file" id="image" name="image" placeholder="select image" required>
</div>
<div class="form-group">
<button type="submit" name="submit" class="btn btn-success">Update Image</button>
<a href="addImage.php" class="btn btn-warning">Add New Image</a>
<a href="viewImage.php" class="btn btn-info">View Image</a>
</div>
</form>
</div>
</div>
<!-- Optional JavaScript -->
<!-- jQuery first, then Popper.js, then Bootstrap JS -->
<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.3/umd/popper.min.js" integrity="sha384-ZMP7rVo3mIykV+2+9J3UJ46jBk0WLaUAdn689aCwoqbBJiSnjAK/l8WvCWPIPm49" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
</body>
</html>Let’s understand the above code step by step:
- The
isset($_GET['id'])will detect if the URL has a query string parameter i.e. id. If URL does not contain the id as a parameter, it will directly show the error message i.e.die("Image not found!");. - Now query to the database to check if the specified image is present or not by $db->query(“SELECT * FROM image WHERE id = $img_id”).
- Now if everything is ok, just update the image database with the updated value i.e.
$db->query("UPDATE image SET image = '$img_content', title = '$title' WHERE id = $img_id");
6. Delete the image from the MySql database.
The letter “D” in the CRUD operation denotes “delete”. We have already done the others i.e. C – Create, R – Read, and U – Update. Let’s check how to delete the image from the MySql database. Create a delImage.php file and paste the below code:
<?php
//include dbConfig file
require_once 'dbConfig.php';
if( isset($_GET['id']) ){
//get image id
$img_id = $_GET['id'];
//check if image is present
$img = $db->query("SELECT * FROM image WHERE id = $img_id");
//no of rows
$no_of_rows = $img->num_rows;
if( !$no_of_rows ){
die("Image not found!");
}else{
//Now run update query
$query = $db->query("DELETE FROM image WHERE id = $img_id");
//check if successfully inserted
if($query){
echo "Image has been successfully deleted.";
}else{
echo "Something went wrong when deleting image!!!";
}
}
}//end of get check
else{
die("Image not found!");
}The above code is really simple. Let’s check the main points:
- I have already discussed checking the image id in
editImage.php. - If everything is ok make a delete query to the image table i.e. $db->query(“DELETE FROM image WHERE id = $img_id”);
- Depending on the query result show the success/error message.
Conclusion
In conclusion, the article discussed how to store and retrieve images from a database in PHP. The code examples are available on GitHub for those who would like to use them. I’ve covered a lot of ground in this blog post, but I hope you found my explanations clear and easy to follow. If there is anything that was unclear or if you have any questions, please don’t hesitate to leave a comment below! I look forward to hearing from you soon.
thank you very much