Saving and Retrieving image in MySQL blob variable using PHP for Android developers

For many developers saving and retrieving an image to and from the database may be a simple requirement. Although it may seem easy, I had personally faced problems when I was coding on an Android application that needed the functionality.

The functionality required was to save an image in MySQL blob datatype column using PHP. The image was to be saved in the database on image capture or from photo gallery and displayed on the application by fetching the image bytes from blob. 

I found some solutions which helped in parts. I was surprised that I could not find a single solution for a such a common functionality. So, I decided to write a simple and generic solution for android developers who are seeking the same functionality in their app. I will be sharing and explaining the source code further in the blog.

For people who are new to blob datatype, can refer to following link. This will give a basic idea to the developer regarding the purpose and usage of blob datatype. 

For your reference, I have shared the link for blob datatype in MySQL and Oracle database. 

For MS-SQL Server users, you can use the VARBINARY datatype. 

http://dev.mysql.com/doc/refman/5.7/en/blob.html

http://docs.oracle.com/javadb/10.8.3.0/ref/rrefblob.html

The next step is to define a column in a table whose datatype is blob. This column will be used to store images captured by the Android application.

Now lets see the PHP file for storing image to MySQL database. I have also shared the PHP code for connection with the database.

//Code for connection to database

$con = mysqli_connect($servername , $username, $password, $dbname );

if(!$con) {

  die("Error in connection. " . mysqli_connect_error());

}

else {

  echo "Connection Success!";

}

//Code to store Image in database

$Image=$_POST["Image"];

$buffer = base64_decode($Image);

$buffer = $con->real_escape_string($buffer); // You can save the buffer variable in the blob column 

                                                                             using a simple insert query

//Code to fetch Image from database

$query = "SELECT profile_picture from `tbl` WHERE UserID =".$UserId; 

$result = mysqli_query($con, $query) or die(mysqli_error()); 

$photo = mysqli_fetch_array($result);

header('Content-Type:image/jpeg'); 

echo $photo[0];

Now, the last part is the android code. First lets see the part of code for displaying image stored in database

URL url = new URL(your url);
InputStream in = url.openStream();
Bitmap bitmap = BitmapFactory.decodeStream(in);
ImageView img = (ImageView) findViewById(R.id.imgProfile);
img.setImageBitmap(bitmap);

Second, following is the code for storing image in database.

Bitmap bitmap = ((BitmapDrawable)imageToUpload.getDrawable()).getBitmap();
ByteArrayOutputStream stream = new ByteArrayOutputStream();
bitmap.compress(Bitmap.CompressFormat.PNG, 90, stream);
byte[] byte_arr = stream.toByteArray();
String img_str = Base64.encodeToString(byte_arr, Base64.DEFAULT);

In the above code, imageToUpload is an ImageView. String img_str must be passed as parameter to the PHP file.

I hope the tutorial above will be helpful for developers. If you have any questions or trouble with the above code comment below.