CODEWITHSUNDEEP
phpandroidmysqljson
Eric
Eric

Reputation: 1557

Writing a proper JSON structure from mysql

I am trying to retrieve data from mysql then display it on android listview. I had try on some tutorial but I had no idea how to write a proper code that could send the data from mysql to android.

This is the JSON Structure that I had been doing for testing.

{
"users": [
    {
            "id": "001",
            "name": "John",
            "email": "[email protected]",
            "gender" : "male"
    },
    {
            "id": "002",
            "name": "Mary",
            "email": "[email protected]",
            "gender" : "female"
    } 
]}

What if all of this data is on mysql database, with table "USERS" and consist of "ID","NAME","EMAIL","GENDER".
How to write a JSON with mysql??

This are the code in Android to display the data.

// JSON Node names
private static final String TAG_USERS = "users";
private static final String TAG_ID = "id";
private static final String TAG_NAME = "name";
private static final String TAG_EMAIL = "email";
private static final String TAG_GENDER = "gender";
JSONArray users = null;
.
.
.
try {
        // Getting Array of Users
        contacts = json.getJSONArray(TAG_USERS);

        // looping through All Users
        for(int i = 0; i < users.length(); i++){
            JSONObject c = users.getJSONObject(i);

            // Storing each json item in variable
            String id = c.getString(TAG_ID);
            String name = c.getString(TAG_NAME);
            String email = c.getString(TAG_EMAIL);
            String gender = c.getString(TAG_GENDER);

Upvotes: 1

Views: 584

Answers (2)

lorenzo-s
lorenzo-s

Reputation: 17010

You have to use json_encode() function.

// MySQL query, maybe you already have something similar
$query = 'SELECT * FROM users LIMIT 5';
$res = mysql_query($query);

// Push all records from the database into a single array
$users = array();
$users['users'] = array(); // Your "users" key for JSON
while ($user = mysql_fetch_assoc($res)) {
    $users['users'][] = $user;
}

// Encode and output that array as JSON, then end the script
echo json_encode($users);
exit();

Upvotes: 4

danwellman
danwellman

Reputation: 9273

Use PHP's json_encode() function to turn an array of values into a JSON string

Upvotes: 3

Related Questions