Cannot retrieve data from MySQL table which matches specific "uid"

Question

I am trying to learn more about MySQL and using Java (on Android) to access and retrieve information from a database on my WAMS server. The way my app is setup is that it has an initial login screen which also grabs the "uid" of the username that's logging in (from a different table) and stores it.

Upon login (which is functional - I setup a toast notification that displays the retrieved username and uid of the user logging in), it goes to a new screen (dashboard.xml) which has a TextView field setup to display the retrieved data (from table posted below) associated with the stored "uid". Here is the table I am trying to pull data from:

http://db.tt/4izVQuGB

Now, I have setup a PHP file that queries my db for rows that are associated with a specific "uid". I have tested this file using an HTML form.

$connect = mysql_connect($dbhost, $dbuser, $dbpass) or die("connection error");
mysql_select_db($dbdb)or die("database selection error");

//Retrieve the User ID
$uid = $_POST['uid'];

//Query
$query = mysql_query("SELECT * FROM node WHERE uid='$uid' AND type='goal'");

//store # of rows returned
$num_rows = mysql_num_rows($query);

if ($num_rows >= 1) {
    while($results=mysql_fetch_assoc($query)) {
        //Store the returned data into a variable
        $output = $results;

        //encode the returned data in JSON format
        echo json_encode($output);
    }
    mysql_close();
}

The result I get by testing the PHP file using uid value of 1 is:

{"nid":"1","vid":"1","type":"goal","language":"","title":"test","uid":"1","status":"1","created":"1342894493","changed":"1342894493","comment":"2","promote":"1","moderate":"0","sticky":"1","tnid":"0","translate":"0"}

{"nid":"2","vid":"2","type":"goal","language":"","title":"test2","uid":"1","status":"1","created":"1342894529","changed":"1342894529","comment":"2","promote":"1","moderate":"0","sticky":"1","tnid":"0","translate":"0"}

{"nid":"5","vid":"5","type":"goal","language":"","title":"run","uid":"1","status":"1","created":"1343506987","changed":"1343506987","comment":"2","promote":"1","moderate":"0","sticky":"1","tnid":"0","translate":"0"}

{"nid":"9","vid":"9","type":"goal","language":"","title":"run to the hills","uid":"1","status":"1","created":"1343604338","changed":"1343605100","comment":"2","promote":"0","moderate":"0","sticky":"0","tnid":"0","translate":"0"}

Now, I have written some android code which sets up httppost and is supposed to retrieve the "titles" in my database table. I know it is wrong (obviously since it doesn't work) but I am confused as to what to do next.

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNameValuePair;
import org.json.JSONObject;
import android.app.Activity;
import android.content.Intent;
import android.content.SharedPreferences;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

public class Dashboard extends Activity implements OnClickListener {

    // variable declarations
    String uid = "1";

    // create textview to display retrieved data
    TextView display;

    HttpClient httpclient;
    HttpPost httppost;
    HttpResponse httpresponse;
    HttpEntity httpentity;

    ArrayList<NameValuePair> resultArray;

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.dashboard);
        display = (TextView) findViewById(R.id.test);

        // initialize HttpClient
        httpclient = new DefaultHttpClient();

        // initialize HttpPost
        httppost = new HttpPost("http://192.168.1.112/android/fetch.php");

        try {
        // Create new List
        List<NameValuePair> resultList = new ArrayList<NameValuePair>();
        resultList.add(new BasicNameValuePair("uid", uid));

        httppost.setEntity(new UrlEncodedFormEntity(resultList));

        httpresponse = httpclient.execute(httppost);

        httpentity = httpresponse.getEntity();

        InputStream instream = entity.getContent();

        try {
                            // store incoming stream in an array
            JSONArray jArray = new JSONArray(streamToString(instream));
            JSONObject jData = null;

            for (int i = 0; i < jArray.length(); i++) {
                jData = jArray.getJSONObject(i);
                String goals = jData.getString("title");
                display.setText(goals);
            }
        //} catch (JSONException e) {
            //Toast.makeText(this, "No entries found", Toast.LENGTH_LONG).show();
        } catch (Exception e) {
            Toast.makeText(this, e.toString(), Toast.LENGTH_LONG)
                    .show();
        }

    } catch (Exception e) {
        e.printStackTrace();
        Notifications error = new Notifications();
        error.userPassErrorDialog();
    }

}

private static String streamToString(InputStream is) {

    BufferedReader reader = new BufferedReader(new InputStreamReader(is));
    StringBuilder sb = new StringBuilder();

    String line = null;
    try {
        while ((line = reader.readLine()) != null) {
            sb.append(line + "\n");
        }
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        try {
            is.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    return sb.toString();
}

public void onClick(View v) {
    // TODO Auto-generated method stub

}

}

I get the following error when testing it in the Android emulator:

http://db.tt/2vg9MqYh

Any help or suggestions will be greatly appreciated.

Could you include the code for `streamToString()`? – cyroxx Aug 04 '12 at 13:58 — cyroxx, Aug 04 '12 at 13:58

cyroxx · Accepted Answer · 2012-08-04T15:06:32.913

In your Android app, you expect a JSONArray:

// store incoming stream in an array
JSONArray jArray = new JSONArray(streamToString(instream));

However, in your PHP file you only output multiple separate JSON objects instead of a real array. I think, you should collect all items from the database in an PHP array first and then encode and output it only once.

My PHP skills are a bit rusted, but I hope this one will work:

//store # of rows returned
$num_rows = mysql_num_rows($query);

if ($num_rows >= 1) {
    $output = array();

    while($results = mysql_fetch_assoc($query)) {
        // append row to output
        $output[] = results
    }

    mysql_close();  // shouldn't that be outside the if block?

    //encode the returned data in JSON format
    echo json_encode($output);
}

I would expect the output then to be like this (maybe without indentation):

[
    {"nid":"1","vid":"1","type":"goal","language":"","title":"test","uid":"1","status":"1","created":"1342894493","changed":"1342894493","comment":"2","promote":"1","moderate":"0","sticky":"1","tnid":"0","translate":"0"},
    {"nid":"2","vid":"2","type":"goal","language":"","title":"test2","uid":"1","status":"1","created":"1342894529","changed":"1342894529","comment":"2","promote":"1","moderate":"0","sticky":"1","tnid":"0","translate":"0"},
    {"nid":"5","vid":"5","type":"goal","language":"","title":"run","uid":"1","status":"1","created":"1343506987","changed":"1343506987","comment":"2","promote":"1","moderate":"0","sticky":"1","tnid":"0","translate":"0"},
    {"nid":"9","vid":"9","type":"goal","language":"","title":"run to the hills","uid":"1","status":"1","created":"1343604338","changed":"1343605100","comment":"2","promote":"0","moderate":"0","sticky":"0","tnid":"0","translate":"0"}
]

score 0 · Answer 2 · answered Mar 23 '14 at 21:10

The problem lies in encoding and decoding of JSON. from your JSON response it looks like you are receiving JSON object from server also please try to validate you JSON response here. run your php file in browser, copy the entire response on the JSON validator and check the brackets that you are receiving the response in.

1. If your response starts with '[' it is and array and if it starts with '{' it is a JSON Object. while parsing JSON you have defined JSON array first but the server response is JSON object. While using JSON you have to be careful on server side for the format of response it will send and you have to be careful on the client side for the format of response you receive. I am posting example script for you.

-> Server side

if (mysql_num_rows($result)>0){
$response["data"] = array();        //this is an array
while($row= mysql_fetch_array($result))
{
$data = array();      //here I have created another temp array
$data["name"] = $row["name"];         
$data["surname"] = $row["surname"];   

array_push($response["data"], $data);   //this makes an array of objects in the response
}}
}//endif
else{
echo "no input";
}}
mysql_close();
echo json_encode($response);    //and finally I echo it as an JSON object

As this php script will return me one object of array of objects ( bit complex isn't it!!) below is the format of response

-> validated JSON response

{
    "data": [
        {
            "name": "Setu",
             "surname": "Desai",
        }
     ]
}

and to decode this my client site script need to be the following

-> parsing JSON object

JSONObject snObject = new JSONObject(jsonString);
    JSONArray snArray = snObject.getJSONArray("data");
    for (int i = 0; i < snArray.length(); i++) {
        JSONObject snObject2 = snArray.getJSONObject(i);
        String surname = snObject2.getString("surname");
        surnamearray.add(surname);
    }

the simple way to understand is to validate you JSON response and identify the position of JSON array and objects and then start decoding.

Cannot retrieve data from MySQL table which matches specific "uid"

2 Answers2

Linked