Create JSON with PHP - HELP!

mariolemieux · April 29, 2011, 6:41pm

Hopefully someone here can help me, I think I am pretty close to figuring it out.

Anyway, I am trying to build a nested JSON list with PHP for an app I am building but having a terrible time getting the commas in the right place.

What I am trying to do is create a JSON data structure like this:

Region
—Category
------Subcategory
---------Listings

I have a PHP file that queries the database and returns each of these. The JSON I am trying to produce must look like this:

{
"items": [
{




"items": [
{





"items": [
{


"items": [
{

"text": "Listing Detail",
"items": [

],
"leaf": true,
"info": "This will be the listing description",
"model": "Listing"
}

],





"text": "Subcategory 1",
"leaf": false,
"info": "This is the description for subcategory 1",
"model": "Subcategory"
}
],





"text": "Category 2",
"leaf": false,
"info": "This is the description for category 1",
"model": "Category"
},

{
"text": "Category 2",
"leaf": false,
"info": "This is the description for category 2",
"model": "Category"
}


],




"text": "Southern",
"leaf": false,
"info": "",
"model": "Region"
}
]
}

My PHP so far:


echo '{"items":[';	



$rs = mysql_query("select distinct region_id, region_name from regions");

$regions_array = array();
        echo '{"items":[';	

while( $row = mysql_fetch_array( $rs ) ){
        $rows = array(
            'text'=> $row[ 'region_name' ],
            'leaf' => false,
            'info' => $row[ 'region_name' ],
            'model' => 'Genre'
        );

	 $rows[] = rtrim($rows,",");
     $regions_array = $rows;
	 echo json_encode( $regions_array );
	
	


$rs2 = mysql_query("select distinct category_name, category_id from categories WHERE category_id = '".$row['region_id']."'");

$subcategories_array = array();
        echo ',{"items":[';	

while( $row2 = mysql_fetch_array( $rs2 ) ){
        $rows2 = array(
            'text'=> $row2[ 'category_name' ],
            'leaf' => false,
            'category_id' => $row2[ 'category_id' ],
            'info' => $row2[ 'category_name' ],
            'model' => 'Artist'
        );


	 $rows2[] = rtrim($rows2,",");
     $subcategories_array = $rows2;
	 echo json_encode( $subcategories_array );





$rs3 = mysql_query("select distinct subcategory_id, subcategory_name, parent_category from subcategories where parent_category = '".$row2['category_id']."'");

$categories_array = array();
        echo ',{"items":[';	

while( $row3 = mysql_fetch_array( $rs3 ) ){
        $rows3 = array(
            'parent_category'=> $row3[ 'parent_category' ],
            'subcategory_id'=> $row3[ 'subcategory_id' ],
            'leaf' => false,
            'info' => $row3[ 'subcategory_name' ],
            'text' => $row3[ 'subcategory_name' ],
            'model' => 'Album'
        );

	 $rows3[] = rtrim($rows3,",");
     $categories_array = $rows3;
	 echo json_encode( $categories_array );




$rs4 = mysql_query("select distinct listing_id, listing_name, listing_description, listing_subcat_id, listing_region_id from listings WHERE listing_subcat_id = '".$row3['subcategory_id']."' AND listing_region_id = '".$row['region_id']."' ");
        echo ',{"items":[';	

while( $row4 = mysql_fetch_array( $rs4 ) ){
        $rows4 = array(
            'listing_subcat_id'=> $row4[ 'listing_subcat_id' ],
            'listing_id'=> $row4[ 'listing_id' ],
            'leaf' => true,
            'text' => $row4[ 'listing_name' ],
            'info' => $row4[ 'listing_description' ],
            'duration' => '8',
            'model' => 'Track'
        );

     $listings_array = $rows4;
	 echo json_encode( $listings_array );


}


//End Category Loop

echo ']},';

}

//End Subcats Loop

echo ']},';

}

//End Regions Loop
echo ']},';

}




//End Listings Loop
echo ']},';

Thanks for your time. I would be happy to pay someone over Paypal to solve this for me, been going at it for a couple of weeks now.

Michael_Morris1 · April 29, 2011, 8:20pm

json_encode is perfectly happy to encode a multidimensional array.

So first, let’s put your queries together using some joins and use the PDO library instead of the ancient mysql library.



$db = new PDO(); // Look up how to connect @ [PHP: PDO - Manual](http://www.php.net/pdo)

$s = $db-&gt;prepare("
  SELECT 
    `r`.`region_id` AS `id`, 
    `r`.`region_name` AS `name`,
    `c`.`category_name` AS `category_name`
    `s`.`subcategory_name` AS `subcategory_name`
  FROM `regions`
    LEFT OUTER JOIN `catgories` `c` ON `c`.`category_id` = `r`.`region_id`
    LEFT OUTER JOIN `subcategories` `s` ON `s`.`parent_category` = `category_id`
    LEFT OUTER JOIN `listing_id` `l` ON `l`.`listing_subcat_id` = `s`.`subcategory_id`
");

$result = $s-&gt;fetchAll(PDO::FETCH_ASSOC);

Now at this point we’ll have the data, but it will be in a flat array. I’ll wait for you to get this far along before discussing transforming it into a multi-tier array in a subsequent post.

mariolemieux · April 29, 2011, 8:28pm

Thanks! Ok, following along.

Michael_Morris1 · April 29, 2011, 8:52pm

Ok, first the bad news - I’m too lazy to write an iteration that will handle the format you want. However, I have a tier arranging class that does this, which I’ll share partly because some of the programmers here helped get it hashed out. It’s source code is broken into two classes - TierArray, which extends ReadOnlyArray.

What TierArray needs is for you to do is prepare a “map” of the return you want, so that it understands how to build your output array. Based on your first post it will be something like this.


$parsedResult = new TierArray($result, array(
  'id',
  'name',
  'category id' => array (
    'category_name'
  )
));

echo json_encode($parsedResult);

I’ll let you experiment with it to get a sense of it and what it’s doing. Note the two code blocks below are intended to be their own files.

TierArray.php


<?php
namespace Gazelle;

/**
 * A Tier Array transforms a flat table array (such as a MySQL result set )
 * to a traversable tier set as defined by structure.
 *
 * @author Michael
 *
 */
class TierArray extends ReadOnlyArray {
	
	/**
	 * Callback Flag.
	 */
	const CALLBACK = 'CALLBACK';
	
	/**
	 * Structure of the return.
	 * @var array
	 */
	protected $structure = null;
	
	/**
	 * Key field of the source array. This will be the outermost key and
	 * typically will be 'id'.  Remember that it's usually going to be
	 * easier to assign this name using an AS alias in the SQL than change
	 * it with an extending class.
	 * @var string
	 */
	protected $key = 'id';
	
	/**
	 * CONSTRUCT
	 *
	 * @param array $source source array - should be 2 dimensional table style like a SQL result set.
	 * @param array $structure target array structure.rows.
	 */
	public function __construct( array $array, array $structure ) {
		$this->structure = $structure;
		$this->storage = $this->buildTiers( $array );
	}
	
	/**
	 *
	 * Build the tiers of the return set according to the structure and key
	 * data set for the class.
	 *
	 * @param array $data
	 */
	protected function buildTiers ( array $data ) {

		$return = array();
		
		foreach ($data as $row) {
			/*
			 * First case - we are indexing the results on a primary key of the result set.
			 * For this case the key must be known to us.
			 */
			if ($this->key) {
				// Have we set this particular key yet?
				if (isset($return[$row[$this->key]])) {
					// Yes, this call will merge this row.
					$return[$row[$this->key]] = $this->parseTierRow($row, $this->structure, $return[$row[$this->key]] );
				} else {
					// No - this call will start a new row.
					$return[$row[$this->key]] = $this->parseTierRow($row, $this->structure );
				}
			/*
			 * Second case - a keyless return.
			 */
			} else {
				$return = array_merge_recursive( $return, $this->parseTierRow($row, $this->structure ));
			}
		}
	
		return $return;
	}
	
	/**
	 * Parse a specific row. This function is VERY recursive.
	 * @param array Row, or row segment we are parsing.
	 * @param array Structure of the row.
	 * @param array Our working return.
	 * @param string Scope - our level of return.
	 * return array - this could be the full row or a segment - depending on recursion level.
	 */
	protected function parseTierRow( array $array, array $indexes, array $return = array(), $scope = '' ) {

		foreach ($indexes as $key => $value ) {
			$seek = (is_numeric($key) && !is_array($value)) ? $value : $key;
			
			$writeKey = $seek;
			
			if (!empty($scope)) {
				$seek = $scope.'_'.$seek;
			}

			if ($this->isValidCallBack($value)) {
				$return[$writeKey] = $this->$value[1]($array);
			} else if ( is_array($value) ) {

				// Branch 1 - $array[$seek] will be the key for the collection sent back.								
				if (array_key_exists($seek, $array) && !empty($array[$seek])) {					
					if (array_key_exists($array[$seek], $return)) {
						$return[$array[$seek]] = $this->parseTierRow( $array, $value, $return[$array[$seek]], $scope );
					} else {
						$return[$array[$seek]] = $this->parseTierRow( $array, $value, array(), $scope );
					}
					
				// Branch 2 - $writeKey will be the key for the collection.
				} else {
					if (array_key_exists($writeKey, $return)) {
						$return[$writeKey] = $this->parseTierRow( $array, $value, $return[$writeKey], $seek);
					} else {
						$return[$writeKey] = $this->parseTierRow( $array, $value, array(), $seek );
					}
				}
			// Setting the value here.	
			} else if ($writeKey == $seek) {
				if (is_numeric($key)) {
					$return[$value] = $array[$writeKey];
				} else if (isset($array[$seek])) {
					$return[$array[$writeKey]] = $array[$value];
				}
			} else {
				if (array_key_exists($seek, $array)) {		
					$return[$writeKey] = $array[$seek];
				} else {
					$return[$writeKey] = null;
				}
			}
		}
	
		return $return;
	}
	
	/**
	 * Test to see if the array given is a callback.
	 * @param array $array
	 */
	protected function isValidCallBack( $array ) {
		return (count($array) == 2 &&
			isset($array[0], $array[1]) &&
			$array[0] === self::CALLBACK &&
			method_exists($this, $array[1])
		);	
	}
}

ReadOnlyArray


<?php
namespace Gazelle;
/**
 * ReadOnlyArray
 *
 * What it says on the tin - An array that cannot be written to.
 * Extended by children which change themselves in special ways.
 *
 * Why not use ArrayObject? Well, most noticably is that in order
 * to enforce non-writability you'll end up overriding more methods
 * than this implements. Second, this class' storage is merely
 * protected instead of being private as it is with ArrayObject.
 * This gives you a bit more flexibility when extending.
 *
 * All methods of this class are required by its interfaces. See
 * PHP.net for more information on them and their precise purpose.
 *
 * @author Michael
 * @package Gazelle Core
 *
 */
class ReadOnlyArray implements \\Iterator, \\ArrayAccess, \\Countable, \\Serializable {
	/**
	 * Current Index of storage.
	 * @var mixed
	 */
	protected $index = '';
	
	/**
	 * The array of data that we shield to make it ReadOnly by the
	 * rest of the API except our children.
	 * @var array
	 */
    protected $storage = array();

    /**
     * CONSTRUCT - take an array and bind to storage. This is the
     * only time this class can be written to from the outside.
     * @param $array
     */
    public function __construct(array $array ) {
        $this->storage = $array;
        reset($this->storage);
        $this->index = key($this->storage);
    }

    /**
     * ITERATOR implementation. Sets index to first element.
     */
    public function rewind() {
        reset($this->storage);
    }

    /**
     * ITERATOR implementation. Returns current value.
     */
    public function current() {
        return $this->storage[ $this->index ];
    }

    /**
     * ITERATOR implementation. Returns current key.
     */
    public function key() {
        return $this->index;
    }

    /**
     * ITERATOR implementation. Advance index to next element.
     */
    public function next() {
 		next($this->storage);
 		$this->index = key($this->storage);
    }

    /**
     * ITERATOR implementation. Detects end of array.
     */
    public function valid() {
        return isset($this->storage[$this->index]);
    }

    /**
     * ARRAY ACCESS implementation. Set a value - disabled.
     * @param string $offset
     * @param mixed $value
     * @throws Exception
     */
	public function offsetSet($offset, $value) {
        throw new Exception('You cannot write values to a Read Only Array after it is created.');
    }

    /**
     * ARRAY ACCESS implementation. Detect if an element is set.
     * @param $offset
     */
    public function offsetExists($offset) {
        return isset( $this->storage[$offset] );
    }

    /**
     * ARRAY ACCESS implementation. Unset a value - disabled.
     * @param string $offset
     * @throws Exception
     */
    public function offsetUnset($offset) {
        throw new Exception('You cannot delete values from a Read Only Array after it is created.');
    }

    /**
     * ARRAY ACCESS implementation. Get a value.
     * @param $offset
     * @throws Exception
     */
    public function offsetGet($offset) {
        if ( isset($this->storage[$offset] )) {
        	return $this->storage[$offset];
        } else {
        	throw new Exception("$offset does not exist");
        }
    }

    /**
     * COUNTABLE implementation
     */
    public function count() {
    	return count($this->storage);
    }

    /**
     * SERIALIZABLE implementation
     */
    public function serialize() {
    	return serialize($this->storage);
    }

    /**
     * SERIALIZABLE implementation
     * @param $data
     */
    public function unserialize( $data ) {
    	$this->storage = unserialize($data);
    }
    /**
     * Return the stored array. Method name matches it's equivalent in ArrayObject.
     */
    public function getArrayCopy() {
    	return $this->storage;
    }
}

mariolemieux · April 30, 2011, 3:43am

Hi, think I figured it out with another method. If it doesn’t work I’ll be back. However I get it to work it I’ll post the answer here. Thanks again.