How to use BLOB with JSON and PHP?

Question

I have a remote database with MySQL, and I am storing photos of the users of my app on the database as a row of the database with LONGTEXT type.

I transform the photos to a string with Base64.

I connect to my remote database with JSON and PHP, because this, I have to use Base64, because as I know, JSON and PHP need to send strings on the parameters, and with Base64 I can transform the photo into a string.

It works ok, but it's very slow. When I am loading a photo of 100 KB, it takes a lot of time, but when I am loading a photo of 5 KB it takes only two or three seconds.

A friend told me to use BLOB instead of Base64, but how do I use BLOB with JSON and a PHP connection to the database? Also, I need to store the images on a row of the table USER. This is because the users don't have privileges to upload files into the remote server, but they can upload photos by uploading them as a string in a row of the table USER.

thanks

EDIT:

this is the code where it takes a looot time waiting (it waits in the line: while ((line = reader.readLine()) != null) { , it is waiting on reader.readLine() )

this code gets one user from the remote database, it takes a loooooot of time to show the user on my app

public Friend RetrieveOneUser(String email)
{

    Friend friend=null;

    String result = "";
    //the parameter data to send
    ArrayList<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>();
    nameValuePairs.add(new BasicNameValuePair("email",email));

    //http post
    InputStream is=null;
    try{
            HttpClient httpclient = new DefaultHttpClient();
            HttpPost httppost = new HttpPost(this.BaseURL + this.GetOneUser_URL);
            httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
            HttpResponse response = httpclient.execute(httppost);
            HttpEntity entity = response.getEntity();
            is = entity.getContent();
    }catch(Exception e){
            Log.e("log_tag", "Error in http connection "+e.toString());
    }
    //convert response to string
    try{

            BufferedReader reader = new BufferedReader(new InputStreamReader(is,"iso-8859-1"),8);
            StringBuilder sb = new StringBuilder();
            String line = null;
            while ((line = reader.readLine()) != null) {
                    sb.append(line + "\n");
            }
            is.close();

            result=sb.toString();
    }catch(Exception e){
            Log.e("log_tag", "Error converting result "+e.toString());
    }

    //parse json data
    try{
            JSONArray jArray = new JSONArray(result);


            for(int i=0;i<jArray.length();i++)
            {
                    JSONObject json_data = jArray.getJSONObject(i);
                    friend=new Friend(json_data.getString("email"),json_data.getString("password"), json_data.getString("fullName"), json_data.getString("mobilePhone"), json_data.getString("mobileOperatingSystem"),"",json_data.getString("photo"));
            }
    }
    catch(JSONException e){
            Log.e("log_tag", "Error parsing data "+e.toString());
    }

    return friend;
}

Answer 1

Segment the request into two parts:

• First downloads the JSON with everything except the image, return a reference to the image as a URL instead
• Second download the image as a binary chunk, potentially asynchronously depending on the app

I'm assuming you have something like http://example.com/userinfo/xxx as an endpoint that returns the JSON? Add an endpoint like http://example.com/userinfo_image/xxx to return just the image, then you can return it as a binary chunk instead of Base64 encoding it in the JSON.

It means you make two HTTP requests instead of one, but depending on the app you might be able to do the image load asynchronously, and if so you normally get a big gain in perceived application response time from the users perspective.

For info about lazy loading images in the background see the post on the Android Developers blog for a sample:

http://android-developers.blogspot.com/2010/07/multithreading-for-performance.html

If you can't lazy load the image consider doing parallel requests for both the image and the JSON at the same time. With the binary version of the image taking a lot less network bandwidth, and a lot less processing once you get the data onto the handset, it should still seem a lot more speedy.

Answer 2

Why not dump your image as a file on the server and return the url of the written file in your json? This is really how you should do what you want to do since http is the protocol you should use for transfering images over the web.

Code similar to this should do what you want on the server

    //code to get your row from database

    //Code that writes it to a file.
    $Data = $row['myblobfield'];

    $fp = fopen('myimgname.jpg', 'w');
    fwrite($fp, $Data);

    fclose($fp);    

This will write your blob or longtext fields as a file on your server which you can then download from your mobile app. You can then delete this temp files after an interval.

Hope this is useful