retrieve data in json format using php

Question

I want to retrieve data in json format given below using php. Code for the same is written as I tried a lot but not able to do it. Please Help Me :

    <?php  

    $db=new PDO('mysql:dbname=punehack;host=localhost;','*******','********');  
    $row=$db->prepare('select * from gyms where live="yes" order by rand() LIMIT 2');  
    $row->execute();//execute the query  
    $json_data=array();//create the array
    $json_latdata=array();

      $result = array();
      $result1 = array();
       foreach($row as $rec)//foreach loop  
       {  
               $json_array['id']=$rec['id']; 
               $json_array['username']=$rec['username'];   
               $json_array['images']=$rec['images'];  
               $json_array['name']=$rec['name']; 
               $json_array['location']=$rec['location'];     
               $json_array['category1']=$rec['category1']; 
               $json_array['category2']=$rec['category2']; 
               $json_array['category3']=$rec['category3']; 
               $json_array['city']=$rec['city'];  
               $json_array['type']=$rec['type'];
               $json_array['male']=$rec['male'];
               $json_array['female']=$rec['female'];
               $json_array['fitdiary']=$rec['fitdiary'];
               $json_array1['latitude']=$rec['latitude'];  
               $json_array1['longitude']=$rec['longitude'];
               array_push($json_data,$json_array); 
              array_push($json_latdata,$json_array1);   
           }  
        $result = $json_data;
        $result1 = $json_latdata;
        echo json_encode(array($result,$result1));
          ?>

Output for this is :

[
    [{
        "id": "101",
        "username": "thefitnessfloor-shivajinagar",
        "images": "thefitnessfloor\/shivajinagar\/ambiance\/1.jpg",
        "name": "The Fitness Floor",
        "location": "Shivaji Nagar",
        "city": "Pune",
        "type": "Gym",
        "male": "yes",
        "female": "yes",
        "fitdiary": "yes"
    }, {
        "id": "97",
        "username": "bodyworks-wanowrie",
        "images": "bodyworks\/wanowrie\/ambiance\/1.jpg",
        "name": "Body Works",
        "location": "Wanowrie",
        "city": "Pune",
        "type": "Gym",
        "male": "yes",
        "female": "yes",
        "fitdiary": "yes"
    }],
    [{
        "latitude": "18.526776",
        "longitude": "73.843531"
    }, {
        "latitude": "18.481161",
        "longitude": "73.901035"
    }]
]

Need to retrieve the data in the below given format :

{
     "id": 1,
    "name": "The Flying Falafel",
    "username": "jhhkjhkhkh",
    "images": "bashfjdskjfksjdkj",
     "categories": [
         "category1",
         "category2",
         "category3"
    ],
    "location": "1051 Market St, SoMa, San Francisco, CA 94103",
    "city": "hjhjhjhjh",
    "type": "hjhjhjhjh",
    "male": "y",
    "female": "y",
    "coordinate": {
        "latitude": 37.78125,
        "longitude": -122.4113007
    }
}

So why dont you try and output it like that, your code makes no effort to create the output in the format you want — RiggsFolly
– RiggsFolly, Commented Feb 21, 2016 at 19:31

IeuanG · Accepted Answer · 2016-02-21 19:31:06Z

1

Try this:

           $json_array['id']=$rec['id']; 
           $json_array['username']=$rec['username'];   
           $json_array['images']=$rec['images'];  
           $json_array['name']=$rec['name']; 
           $json_array['location']=$rec['location'];     
           $json_array['categories'][0]=$rec['category1']; 
           $json_array['categories'][1]=$rec['category2']; 
           $json_array['categories'][2]=$rec['category3']; 
           $json_array['city']=$rec['city'];  
           $json_array['type']=$rec['type'];
           $json_array['male']=$rec['male'];
           $json_array['female']=$rec['female'];
           $json_array['fitdiary']=$rec['fitdiary'];
           $json_array['coordinate']['latitude']=$rec['latitude'];  
           $json_array['coordinate']['longitude']=$rec['longitude'];

answered Feb 21, 2016 at 19:31

IeuanG

5045 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matt · Accepted Answer · 2016-02-21 19:31:19Z

0

You're not too far off. Coordinates needs to be attached to the original array or you will get all the coordinates at the end detached from their corresponding record.

           ...
           $coordinates['latitude']=$rec['latitude'];  
           $coordinates['longitude']=$rec['longitude'];
           $json_data['coordinate'] = $coordinates;
           array_push($json_data,$json_array);

answered Feb 21, 2016 at 19:31

Matt

5,4381 gold badge34 silver badges59 bronze badges

Comments

Community · Accepted Answer · 2023-11-17 20:08:53Z

First of all, in your examples doesn't exists categories.

Remove the $json_data, $json_latdata and $result1 declarations:

$result = array();

foreach($row as $rec)//foreach loop  
{

Init the $json_array (it is not mandatory, but it is a good practice):

    $json_array = array(); 
    $json_array['id']=$rec['id']; 
    $json_array['username']=$rec['username'];   
    $json_array['images']=$rec['images'];  
    $json_array['name']=$rec['name']; 
    $json_array['location']=$rec['location'];  
    $json_array['city']=$rec['city'];  
    $json_array['type']=$rec['type'];
    $json_array['male']=$rec['male'];
    $json_array['female']=$rec['female'];
    $json_array['fitdiary']=$rec['fitdiary'];

Create the sub-array coordinate in this way:

    $json_array['coordinate'] = array();
    $json_array['coordinate']['latitude']=$rec['latitude'];
    $json_array['coordinate']['longitude']=$rec['longitude'];

Add the array to $result (when only one item is appended to array, is preferable the [] syntax instead of array_push()):

    $result[] = $json_array; 
}

Finally, echo your JSON encoded result:

echo json_encode( $result );
?>

Collectives™ on Stack Overflow

retrieve data in json format using php

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related