SQL result to JSON

Question 1

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

Question 2

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.

Leave a Reply Cancel reply