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.