SQL result to JSON

Question

I am trying to return a JSON object to an Ajax call. Is this the best way to build a JSON object with PHP JSON encode?

$arr = array('states' => '');
$sql = "SELECT * FROM `states` WHERE `country_id` = 1";
$result = @mysql_query($sql, $db);
while( $list = mysql_fetch_assoc($result) ) {
 $arr['states'][] = array('id' => $list['state_id'], 'name' => $list['name'], 'regions' => ''); 
}
foreach( $arr['states'] as $k => $v ) {
 $sql = "SELECT * FROM `regions` WHERE `state_id` = ".$arr['states'][$k]['id']."";
 $result = @mysql_query($sql, $db);
 //add regions of states
 while( $list = mysql_fetch_assoc($result) ) {
 if( $list['state_id'] == $arr['states'][$k]['id'] ) {
 $arr['states'][$k]['regions'][] = array('id' => $list['region_id'], 'name' => $list['name'], 'cities' => '');
 }
 } //EO while
 //add cities of regions 
 foreach( $arr['states'][$k]['regions'] as $key => $val ) { 
 $sql2 = "SELECT * FROM `cities_n` WHERE `region_id` = ".$arr['states'][$k]['regions'][$key]['id']."";
 $result2 = @mysql_query($sql2, $db);
 while( $list2 = mysql_fetch_assoc($result2) ) {
 if( $list2['region_id'] == $arr['states'][$k]['regions'][$key]['id'] ) { 
 $arr['states'][$k]['regions'][$key]['cities'][] = array('id' => $list2['city_id'], 'name' => $list2['name']);
 }
 }
 }
}
echo json_encode($arr);

It gives me a format I can work with but this seems clunky to me - I would be very confused looking back at this a couple of months from now.

What's a better way to build an object like this?

NOTE: I will be upgrading to PDO/msqli soon (I know I need to stop using mysql). Please excuse lack of commenting.

Sample JSON: http://pastebin.com/nmeji2nY

• jsanc623: Can you post a sample of the resulting JSON data, considering we don't have the SQL table/data?
• Andy: @jsanc623 I've added a sample json data
• Raptor: never suppress mysql_query() call; check for mysql_error instead.
• smt: See serializer library it allows to convert any data(arrays, objects) to json/xml or any other format you'll specify.

Answer 1

You should just have 1 SQL query using JOINs as follows:

SELECT 
 states.state_id, 
 states.name, 
 region.region_id, 
 region.name, 
 cities_n.city_id, 
 cities_n.name 
FROM 
 states
LEFT JOIN
 regions 
ON 
 states.state_id = regions.state_id
LEFT JOIN 
 cities_n
ON
 regions.region_id = cities_n.region_id
WHERE 
 states.country_id = :country_id

But without knowing how you want your JSON to be defined, at the moment I can only suggest the following, which fetches all records in the SQL query and shunts them to the JSON encoder.

# General setup and initialization
$country_id = 1;
$db = new PDO('dblib:host=your_hostname;dbname=your_db;charset=UTF-8', $user, $pass);
$query =<<<SQL
SELECT states.state_id, states.name, region.region_id, 
 region.name, cities_n.city_id, cities_n.name 
FROM states LEFT JOIN regions ON states.state_id = regions.state_id
LEFT JOIN cities_n ON regions.region_id = cities_n.region_id
WHERE states.country_id = :country_id;
SQL;
# Fetch all states, regions and cities and shunt them to the JSON encoder
$sth = $db->prepare($query);
$sth->execute([':country_id' => $country_id]);
echo json_encode($sth->fetchAll(PDO::FETCH_ASSOC));

Just remember that when it comes to data, in 90% of situations it's best to let the database interweave the data for you than blocking CPU at the application level.

PS: After you post a reply/update, I'll modify my code and answer accordingly.

• Andy: I like the point about letting the database handle putting together relational data and to avoid blocking CPU at the app level, but how do I get a "pretty" JSON object like mine with sql alone - your solution outputs this pastebin.com/vWtvScNK