Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I am trying to return a set of results from PHP to android.

I have been able to return a single results but now I am trying to return multiple ones I am having some trouble figuring out how to do this as an array.

PHP function:

   public function searchForPeople($tower) {
    $uuid = uniqid('', true);

    $result = mysql_query("SELECT * FROM users WHERE tower='$tower'") or die(mysql_error());
    $resultNo = mysql_num_rows($result);

    // check for successful store
    if ($result != null) {

        //if just one result return it
        if ($resultNo == 1) {

            // return result
            $resultSet[] = mysql_fetch_array($result);
            return $resultSet;

    //if more than one loop through
    } else {

        //add each row to an array
        while($row = mysql_fetch_array($result)) {
            $resultSet[] = $row;
        }
        return $resultSet;
        }

    } else {
        return false;
    }
}

Section of index.php where i POST my data from android to:

    //SEARCH FOR PEOPLE
     else if ($tag == 'searchPeople') {

        $tower = $_POST['tower'];

        $result = $db->searchForPeople($tower);


    // check array has been created
    if ($result != false) {

        $response["success"] = 1;

        $count = 0;
        foreach($result as $row) {

        $response[$count]["user"]["name"] = $row["name"];
        $response[$count]["user"]["email"] = $row["email"];

        $count++;
        }

        echo json_encode($response);


      } else {
        $response["error"] = 2;
        $response["error_msg"] = "No users found";
        echo json_encode($response);
    }
    }

        else {
    echo "Invalid Request";
}

I am then trying to get the information back in android as below however recieving the error that there is no value for 0 meaning there must be a problem in the way I have returned the json in PHP.

JSONObject results  = new JSONObject(resultsString);
JSONObject json_row = results.getJSONObject("0");
JSONObject json_user = json_row.getJSONObject("user");

Im sure this is a problem with returning the PHP array of SQL results. Probably when I am looping through them to add them to either $resultSet or $response.

Any help greatly appreciated.

EDIT:

Here are the errors I am getting:

11-14 19:42:37.270: E/JSON(639): "[{\"uid\":\"4\",\"unique_id\":\"505efc638e0f48.78430999\",\"name\":\"fish\",\"email\":\"fish\",\"encrypted_password\":\"r\/Hb7uXrHN8bFuRoKlG8+Y5LdKFjM2QyZDUyYzQ1\",\"salt\":\"c3d2d52c45\",\"created_at\":\"2012-09-23 13:11:15\",\"updated_at\":\"2012-11-03 09:56:15\",\"location\":\"888\",\"tower\":\"IS\",\"base_location\":\"\",\"client_site\":\"\",\"graduate\":\"0\",\"location_updated\":\"0000-00-00 00:00:00\"}]"
11-14 19:42:37.270: E/JSON Parser(639): Error parsing data org.json.JSONException: Value [{"uid":"4","unique_id":"505efc638e0f48.78430999","name":"fish","email":"fish","encrypted_password":"r/Hb7uXrHN8bFuRoKlG8+Y5LdKFjM2QyZDUyYzQ1","salt":"c3d2d52c45","created_at":"2012-09-23 13:11:15","updated_at":"2012-11-03 09:56:15","location":"888","tower":"IS","base_location":"","client_site":"","graduate":"0","location_updated":"0000-00-00 00:00:00"}] of type java.lang.String cannot be converted to JSONArray
share|improve this question

3 Answers

up vote 0 down vote

I recently used a routine similar to the one below. No matter if there is just one result or many results, it always works.

$dbuser = // user name;
$dbpass = // password;
$dbhost = // host name;
$dbname = // database name;

$sql = "SELECT * FROM users WHERE tower=" . $tower;

try {
$dbh = new PDO("mysql:host=$dbhost;dbname=$dbname;charset=utf8", $dbuser, $dbpass); 
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $dbh->query($sql);  
$results = $stmt->fetchAll(PDO::FETCH_OBJ);
$dbh = null;
echo '({"items":'. json_encode($results) .'});';
} catch(PDOException $e) {
echo '{"error":{"text":'. $e->getMessage() .'}}'; 
}
share|improve this answer
up vote 0 down vote

You should dump your resultsString to debug Log to see how it looks like and whether it is the expected JSON string. Then you can see where, in the server or the client, you must fix the code.

I'd also look into JSONObject.getJSONArray(), since the response you're sending back consists of nested arrays, AFAICS.

share|improve this answer
up vote 0 down vote

I believe you should use 

mysql_fetch_assoc

To pull the rows in PHP. Then your result comes ready to be json_encoded into the string you'll need to return to Android.

So try this:

function sqlToJSON($sql){
     $sth = mysql_query($sql);

    //if this is an update or an insert nothing to return
    if($sth === false || $sth === true){    

        return null;
    }    

    $rows = array();
    while($r = mysql_fetch_assoc($sth)) {
        $rows[] = $r;
    }   
    return json_encode($rows);
 }

You should be able to run the result of this method through new JSONObject(res); on the Java side without having to do any additional translation... I'm saying this from memory, so, it may not be 100% right, but if not, it's pretty close.

share|improve this answer
Thanks this does indeed return more than on result however I am getting an JSONerror exception when parsing the returning string in java. Could this be due to my something in the database such as timestamps or blank values? – EHarpham Nov 13 '12 at 21:48
hard to say without seeing what your return value actually is. Can you paste it? Also, what exactly is the Exception you're getting saying? One thing I'm not sure of is whether the entire result is wrapped in { and } as it needs to be, to be well formed JSON. – Dr.Dredel Nov 14 '12 at 1:19
Errors posted in EDIT. I have tried various ways now to return this include addslashes and converting to JSONArray instead of JSONObject at the other end but still not luck – EHarpham Nov 14 '12 at 19:43
right, as I said, you need to wrap the result in { and }. Something that starts with [ isn't going to be parsed as JSON. So, on the java side try new JSONObject("{" + myPHPResult + "}"); and see what happens. – Dr.Dredel Nov 14 '12 at 23:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.