How to use BLOB with JSON and PHP?

问题:

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;
}

回答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.

回答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

回答3:

To answer your question: No, JSON doesn't support binary data, you must escape it in some way before sending it. Storing it as BLOB in MySQL is not going to fix the major infrastructure issues you have.

From what I understand you have an Android device that is uploading a picture to a PHP server, this PHP server is encoding the picture to Base64, putting that into a JSON string and then posting it to a remote(how remote is remote? same data center? across the country? across the world? in outer space orbiting the moon?) MySQL server through an HTTP interface of some sort, that MySQL server is storing the Base64 image as LONGTEXT. To get the image back, the Android Client sends a request to PHP, PHP sends a request to the remote MySQL server, PHP then has to Base64 decode the image and send it down.

This is horribly inefficient, you are going to suffer latency every step of the way.

Edit: okay it looks like this is a client side issue and not a server side issue...

If that's the case then I'd suggest checking the posts @ Uploading images to a PHP server from Android as they should have some more efficient examples.

回答4:

Is the slowness coming from json/base64 encoding 100K of data, or from the database hit? Its probably from the encoding, and putting the files in the file system (as everyone in the comments is crying), on a small scale, is not going to make a bit of difference.

Do some measurements on the different parts of the operation, and try to pinpoint why its slow. I don't know how else you'd get an image blob into a json encoded string without base64, i suppose you could try and escape everything, which might be just as slow, and hope the parser doesn't choke on it.

Are you using the json_encode function in php, or manually building the string? Try building it manually. Are you base64 encoding raw data from the database, or is it encoded before its stored, you should encode it before its stored to save time when outputting.