How to get BLOB image file from MYSQL server via JSON efficiently

Question

My MYSQL db contains 5 rows

id, name, username, password, email, image

The image type is BLOB.

My PHP codes below to get JSON data from MYSQL.

while($row = mysqli_fetch_array($res)){
     array_push($result,
         array('id'=>$row[0],
               'name'=>$row[1],
               'username'=>$row[2],
               'password'=>$row[3],
               'email'=>$row[4],
               'image'=>$row[5])
               ));
}

echo json_encode(array("result"=>$result));

My application will load longer than expected to parse the base64 image string from the returned JSON.

So what's the best way to store and retrieve image file efficiently?

one suggestion is to store `imagepath` and not image in your `db` , and show images in android from that `imagepath` you received via `json` — Satyen Udeshi, Oct 05 '15 at 06:26
The simple answer is it's completely inefficient. and the only way to make it efficient is not to store images as blobs. — e4c5, Oct 05 '15 at 07:17

score 3 · Accepted Answer · answered Oct 05 '15 at 06:31

3

I had a similar problem. So I avoided storing images in MySql. I am storing them in the server as image files and using picasso to show the images. Its super easy to transform and use.

answered Oct 05 '15 at 06:31

Panda

152
2
10

score 0 · Answer 2 · edited May 23 '17 at 12:16

0

Assuming your Above code is correct and you are getting base64 as image you can simply do this

while($row = mysqli_fetch_array($res)){
     array_push($result,
         array('id'=>$row[0],
               'name'=>$row[1],
               'username'=>$row[2],
               'password'=>$row[3],
               'email'=>$row[4],
               'image'=>$row[5])
               ));
}

echo json_encode(array("result"=>$result));

on Android side you can do some thing like this to convert it to image.

byte[] decodedString = Base64.decode(encodedImage, Base64.DEFAULT);
Bitmap decodedByte = BitmapFactory.decodeByteArray(decodedString, 0, decodedString.length);

Convert base64 string to Image in Java taken as reference

edited May 23 '17 at 12:16

Community

1
1

answered Oct 05 '15 at 06:29

noobie-php

6,817
15
54
101

I've tried this, I got a `textview` to display JSON data, it show null when use this method – cw fei Oct 05 '15 at 06:31
@cwfei : originally you were using reference to the image like `&row[5]` and not to value itself i.e `$row[5]` try this approach again – noobie-php Oct 05 '15 at 06:33
That's my mistake but I've correct it, without `base64_decode` i get something like `"image":"\/9j\/4AAQSkZJRASJDASDJA\/........"`, a very long string – cw fei Oct 05 '15 at 06:35
thats the actual `base 64` converted image all you need to do is send the data as it is to client i.e android device and decode it on client end. – noobie-php Oct 05 '15 at 06:37
@cwfei: All you have to do is send the base64 string to client i.e android and decode it there, i have shared a sample on how to do it, there are other few ways to do that too. – noobie-php Oct 05 '15 at 06:42
Thanks for your edited answer, I've tried this before and it works, but will it be lag if obtain multiple images base64 string at once? – cw fei Oct 05 '15 at 06:44
Well yes, but depends how you render them, e.g if you are loading 10 images or 20 , it wont be much of a problem, but if it is a huge number it will be lagy, to avoid this, you can call data in a `paginated` way. Even with async calls we cannot guarantee if the images are small or large. Loading images with URL in android still can be laggy. So its best if you have a strategy about loading images. I personally load images in a paginated way, e.g ask server to send me 10 items on activity swipe down etc – noobie-php Oct 05 '15 at 06:47

How to get BLOB image file from MYSQL server via JSON efficiently

2 Answers2