Converting MySQL results to JSON

Question

HI i have a peice of code which takes information from a xml file using the below peice of code:

if (file_exists('locations.xml')) {
    $xml = simplexml_load_file('locations.xml');
    $json = json_encode($xml);
    print_r($json);
} else {
    exit('Failed to open locations.xml.');
}

Below is the layout of the xml page

   <locations>
     <location>
       <id>12196</title>
       <uid>010203</uid>
       <lat>34.134103</lat>
       <lng>-118.321694</lng>
      </location>
    <location>

This works fine, now what i want to do is replace the code above to take information straight from the database. and have the following peice of Code:

  $query = mysql_query("SELECT * FROM track ORDER BY id");
  $rows = array();
  while($row = mysql_fetch_assoc($query)) {
   $rows[] = $row;
  }
  print json_encode($rows);

When retrieving the information from the database it works well. However i have noticed that when i retrieve the data the format is slightly different for example.

using the first method places location in front of the results, like this.

  {"location":[{"id":"12196"

and the second method just returns this,

  [{"id":"12196","uid":"010203"

I have tried severals ways to get the second format to include the location, but can not seem to get it to work. and i think the rest of my code uses the location tag to assign the information.

Any suggestions would be appreciated.

Thanks

GBD · Accepted Answer · 2012-12-01 15:39:03Z

3

Try

while($row = mysql_fetch_assoc($query)) {
   $rows['location'][] = $row;
  }
  print json_encode($rows);

answered Dec 1, 2012 at 15:39

GBD

16k2 gold badges49 silver badges52 bronze badges

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

Collectives™ on Stack Overflow

Converting MySQL results to JSON

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related