Store, retrieve, and update image from database in PHP

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:

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 Image Table Using Phpmyadmin
Create Image Table Using Phpmyadmin
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); 
	}

?>
  • $servername is the name of MySQL server.
  • $username is database username i.e. root.
  • $password is the database password.
  • $dbname is 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:

image upload to mysql database
Image upload page

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.php file where our database configaration is present.
  • By using isset($_POST["submit"]) function, we are checking if the method is POST.
  • basename() and pathinfo() 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.

Fetch image from database and display in table
Fetch image from database and display it in a table

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 query to 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.

Update image in database
Update image in the database

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 URL has a query string paramater i.e. id. If URL does not contain id as paramater, 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 updated value i.e. $db->query("UPDATE image SET image = '$img_content', title = '$title' WHERE id = $img_id");

6. Delete image from 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 about checking the image id in editImage.php .
  • If everything is ok make a delete query to image table i.e. $db->query("DELETE FROM image WHERE id = $img_id");
  • Depending on 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.

About Ashis Biswas

A web developer who has a love for creativity and enjoys experimenting with the various techniques in both web designing and web development. If you would like to be kept up to date with his post, you can follow him.

Leave a Comment