Tag Archives: MySQL

Saving Image To Database Using PHP

Images are usually located in assets/images folder of your application. Web users can easily access your images using direct URL without logging in. If you are storing users’ uploaded images in your web server folder, someone might be able to steal your important images. For example, photo of employees.

Another issue is if you have multiple application servers running the same application to serve many HTTP request concurrently, images uploaded by users may be stored in either one of the server. Hence, when another user trying to view image uploaded, the image may appear missing. See image below.

Image Missing When Using Multiple Application Server

Image Missing When Using Multiple Application Server

There are many ways to solve this issue. Here is how I solve it. Saving the image into the database.

Summary of workflow:
Saving Image
1) Uploaded image will be saved in one of the server folder.
2) Convert the image binary string to base64 string using PHP base64_encode.
3) Store base64 string in database as longtext type.

Loading Image
4) Load base64 string containing image from database.
5) Convert base64 string to binary using PHP base64_decode.
6) Set return type header as ‘image/png‘.
7) Echo the binary data.

See sample code below.

File Upload Form:

<html>
<body>

<form action="backend.php" method="POST" enctype="multipart/form-data">
	<input type="file" name="file"/>
	<br />
	<input type="submit"/>
</form>

</body>
</html>

Backend:

<?php
if(!isset($_GET["getfile"])){
	if ($_FILES["file"]["error"] > 0){
		echo "Error: " . $_FILES["file"]["error"] . "<br>";
	}else{
		move_uploaded_file($_FILES["file"]["tmp_name"], $_FILES["file"]["name"]);
		$bin_string = file_get_contents($_FILES["file"]["name"]);
		$hex_string = base64_encode($bin_string);
		$mysqli = mysqli_init();
		if (!$mysqli->real_connect('localhost', 'root', '', 'test')) {
			die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
		}
		$mysqli->query("INSERT INTO upload(image) VALUES ('" . $hex_string . "')");
	}
}else{
	$mysqli = mysqli_init();
	if ($mysqli->real_connect('localhost', 'root', '', 'test')) {
		if ($result = $mysqli->query("SELECT * FROM upload ORDER BY id DESC")){
			if($row = $result->fetch_assoc()){
				$output_hex_string = $row["image"];
				$output_bin_string = base64_decode($output_hex_string);
				header("Content-Type: image/png");
				header("Content-Length: " . strlen($output_bin_string));
				$result->free();	
				echo $output_bin_string;
			}
		}
	}
  }
?>  

<img src="backend.php?getfile=1" />

Note: The last line of code ‘<img src=”backend.php?getfile=1″ />’. Note the “src”  parameter. By using this method, we can just call a PHP function within the src tag and the browser will take the PHP output as image to display. This inline method is clean and easy to use. In addition, we can preprocess the image output or perform access control before displaying.

MySQL allows storage of binary data but I am storing it as hex string. The reason is I won’t need to handle not 8-bit clean characters or null characters that might break the code during saving process.