How to store image in MySQL database with PHP

store-images-in-db-theonlytutorials

 

If you are a newbie PHP developer, you will not easily get the idea of storing images in the database, but the answer is very simple, you are not going to store the actual image in the database but the image name which is actually uploaded in a folder.

There are two methods you can follow to store an image into the MySQL database,

1. Uploading images in a folder and storing the reference in the database (usually the image name with extension)
2. Converting images into binaries and storing the binaries in the database using a special data type called BLOB.

The first method is often used by almost all of the website developers and it is the best way. The main reason is images are uploaded in the folder, If you want to optimize all the uploaded images in future, you can easily download the images from the server and do the optimization and upload it back.

But in the second case, it is pretty difficult, you have to update each and every record in the database and also your database size will become very large and cannot be easily downloaded.

So, In this example, we are going to do the First method which is uploading the images in a folder and storing the reference into the MySQL database.

Let’s start the coding part:

1. In this example, I have used mysqli instead of mysql, because mysql will be deprecated soon.
2. Also, I have used the OOPS concept to upload and retrieve images from MySQL database to keep the code clean and neat.

So, let’s begin with the upload class (upload_class.php):

<?php

class upload{
	
	//connection strings
	private $db_host 	 = 'your_hostname';
	private $db_username = 'your_db_username';
	private $db_password = 'your_db_password';
	private $db_name	 = 'your_db_name';
	private $con;
	//image folder path
	private $img_path 	 = 'uploads/';
	
	public function __construct()
	{
		//this connects to the database immediately after the instance created
		$this->con = mysqli_connect($this->db_host,$this->db_username,$this->db_password,$this->db_name);
		if(mysqli_connect_errno()){
			echo "Connection Failed, Please check your DB connection"; 
		}
	}
	
	public function upload_image_and_store($post, $files)
	{
		//get extension from the file
		$allowedExts = array("gif", "jpeg", "jpg", "png");
		$temp = explode(".", $files["img_file"]["name"]);
		$extension = end($temp);

		if ((($files["img_file"]["type"] == "image/gif") //check image is gif
		|| ($files["img_file"]["type"] == "image/jpeg") //check image is jpeg
		|| ($files["img_file"]["type"] == "image/jpg") 	//check image is jpg
		|| ($files["img_file"]["type"] == "image/png")) //check image is png
		&& ($files["img_file"]["size"] < 2000000) //check if image size is below 2MB
		&& in_array(trim($extension), $allowedExts)) //check the extensions also
		{
			if ($files["img_file"]["error"] > 0) //check if any file error
			{
				echo $files["img_file"]["error"];
			}
			else 
			{
				//unique file name to avoid overwriting
				$filename = time().$files["img_file"]["name"];
				
				//move the uploaded file to folder
				$upload =  move_uploaded_file($files["img_file"]["tmp_name"], $this->img_path . $filename);
				
				//after upload the file store the image path and other details in database
				if($upload){
					$this->store_image_in_db($filename, $post);
				}
			}
		}
		else 
		{
			echo "Please upload only image files and should be less than 2MB";
		}
	}
	
	private function store_image_in_db($filename, $post)
	{
		//sanitize the input
		$img_name = mysqli_real_escape_string($this->con, $post['img_name']);
		$img_cat  = mysqli_real_escape_string($this->con, $post['img_cat']);
		
		//insert a record into the table image_uploads
		$sql_string 	= "insert into image_uploads (img_name, img_cat, img_uploaded_name, upload_dt) values ('$img_name', '$img_cat', '$filename', now())";
		$query  		= mysqli_query($this->con, $sql_string);
		if($query){
			
			echo "Image uploaded and added into the database!";
		}else{
			echo "Check your DB or Table";
		}
	}
	
	public function get_images()
	{
		$arr = array();
		//get all images from table image_uploads
		$sql_string = "select * from image_uploads";
		$query 		= mysqli_query($this->con, $sql_string);
		
		while($row = mysqli_fetch_assoc($query)){
			$arr[] = $row;
		}
		//return the result set
		return $arr;
	}
}

?>

The code is self-explanatory, just read the comments in the class to understand.

Basically this upload class has four functions,

1. __construct() is a PHP magic function, this function will be called automatically by PHP when you create an object from your class. In the program I’m using __construct() to connect to the MySQL database.

2. upload_image_and_store() is to validate the file and also to upload the image in a folder.

3. store_image_in_db() is to store the image reference (name) into the MySQL table.

4. get_images() is to retrieve the image list from the database table.

 

Here is the usage of the above class:

1. Upload image form (index.php):

<?php 

	require_once('upload_class.php'); 
	
	if($_POST)
	{
		$obj = new upload();
		
		//upload and store image
		//$_POST will send name and category text values
		//$_FILES will send file name, size, type etc.,
		$obj->upload_image_and_store($_POST, $_FILES);
	}
?>

<html>
<head>
	<script>
		function validate(){
			var img_name = document.getElementById("img_name").value;
			var img_file = document.getElementById("img_file").value;
			if(img_name == ''){
				alert("Please enter image name");
				return false;
			}
			if(img_file == ''){
				alert("Please upload an image");
				return false;
			}
			return true;
		}
	</script>
</head>
<body>
	<h3>Upload image and store it in MySQL Database example by <a href="https://theonlytutorials.com" target="_blank">Theonlytutorials.com</a></h3>
	<form action="" method="post" enctype="multipart/form-data" onsubmit="return validate()">
		<table border="1">
			<tr>
				<td>Image Name:</td>
				<td><input type="text" name="img_name" id="img_name"></td>
			</tr>
			<tr>
				<td>Image Category:</td>
				<td>
					<select name="img_cat" id="img_cat">
						<option>Category1</option>
						<option>Category2</option>
					</select>
				</td>
			</tr>
			<tr>
				<td>Select Image:</td>
				<td><input type="file" name="img_file" id="img_file" ></td>
			</tr>
			<tr>
				<td colspan="2" align="right"><input type="submit" name="submit" value="Submit"></td>
			</tr>
		</table>
	</form>

	<a href="list_images.php">See the uploaded images</a>
</body>
</html>

If you see the above code, the PHP part is very minimal because of the Object-Oriented methodology.

Here is the SQL Dump for the table ‘image_uploads

--
-- Table structure for table `image_uploads`
--

CREATE TABLE IF NOT EXISTS `image_uploads` (
  `img_id` int(7) NOT NULL AUTO_INCREMENT,
  `img_name` varchar(150) NOT NULL,
  `img_cat` varchar(100) NOT NULL,
  `img_uploaded_name` varchar(255) NOT NULL,
  `upload_dt` datetime NOT NULL,
  PRIMARY KEY (`img_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

2. Retrieve image from the database (list_images.php):

<?php 

	require_once('upload_class.php'); 
	
		$obj = new upload();
		$images = $obj->get_images();
		
		if(!$images){
			echo "No Images uploaded, <a href='index.php'>Upload some images</a>";
			exit;
		}
?>

<html>
<body>

	<h3>List of images stored in database</h3>
	
	<table border="1">
		<tr>
			<th>S.No</th>
			<th>Image Name</th>
			<th>Image Category</th>
			<th>Image Preview</th>
		</tr>
		<?php 
			$i = 1;
			foreach($images as $image) { ?>
		<tr>
			<td><?php echo $i; ?></td>
			<td><?php echo $image['img_name']; ?></td>
			<td><?php echo $image['img_cat']; ?></td>
			<td><a href="<?php echo "uploads/".$image['img_uploaded_name']; ?>" title="click to see full size image" target="_blank"><img src="<?php echo "uploads/".$image['img_uploaded_name']; ?>" width="100" border="0" /></a></td>
		</tr>
		<?php 
			$i++;
			} 
		?>
	</table>
	<br />
		<a href="index.php">Upload more images</a>
</body>
</html>

In this file, I’m reading the image names from the table ‘image_uploads‘ and showing a preview by pointing it to the appropriate image folder (uploads/).
For your convenience, I have also given a demo and download of this script here, enjoy:

demo download

 

7 thoughts on “How to store image in MySQL database with PHP

  1. Hello Sir,
    I got the following error while running the php file:
    Parse error: parse error, expecting `T_OLD_FUNCTION’ or `T_FUNCTION’ or `T_VAR’ or `’}” in c:\program files\easyphp1-8\www\upload_image_php_mysql\upload_class.php on line 6

    1. Please ceheck your db-connection.
      Your line 6 is this one:
      private $db_host = ‘your_hostname’;
      here you must write ‘localhost’ if you use it at home
      or ‘db123456’ -> this db-name is normaly given by your hoster. Maybe you don’t have MySQL in your host…then it woun’t work!

  2. This not what it supose to be! 🙁

    `img_id` int(7) NOT NULL AUTO_INCREMENT,
    `img_name` varchar(150) NOT NULL,
    `img_cat` varchar(100) NOT NULL,
    `img_uploaded_name` varchar(255) NOT NULL,
    `upload_dt` datetime NOT NULL,

    >How to store image in MySQL database with PHP
    >private $img_path=’uploads/’;
    Images are stored in normal folder, only description text etc are stored in db!

Leave a Reply

Theme: Overlay by Kaira
Agurchand