MySql - PHP - JSON - Sencha Nested List

[mobile207]

See below:

[mobile207]

The following JSON works, I am having trouble with getting the structure of the json file to print out correctly using PHP.

I have four tables, one for each - Region, Category, Subcategory and Listing. Each are connected with an id. I can produce an unordered list and HTML ok, JSON is giving me problems.

(See JSON below)

[tomalex0]

what is the raw data that you get after fetching from Database ?

Also please enclose the json in code tag, so it will be readable.

[mobile207]

what is the raw data that you get after fetching from Database ?

Also please enclose the json in code tag, so it will be readable.


{
"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"
}
]
}

[mobile207]

Sorry, new to the forum. Anyway, the JSON I need to produce is above.

I'm thinking the PHP needs 4 arrays (one for each query) that connect to the category above it.

Thanks for any insight, really appreciate it.

[tomalex0]

can i see the raw array that you have.

[mobile207]

Ok, I think I am getting close. Using json_encode() helped. The problem I am having now is getting the arrays to nest properly. Right now it looks like:

A,B,C,D

But I need it to display nested like: A [ B [ C [ D ] ] ]

PHP is below, hoping I just have to tweak the last part of it.. Thanks,





$rs = mysql_query("SELECT distinct region_id, region_name FROM regions");
while( $row = mysql_fetch_array( $rs ) ){

$rows[] = array(
'text'=> $row[ 'region_name' ],
'leaf' => 'false',
'region_id' => $row[ 'region_id' ],
'model' => 'Genre'
);



$rs2 = mysql_query("SELECT category_name, category_id FROM categories WHERE category_id = '".$row['region_id']."'");

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'
);



$rs3 = mysql_query("SELECT subcategory_id, subcategory_name, parent_category FROM subcategories WHERE parent_category = '".$row2['category_id']."'");

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'
);



$rs4 = mysql_query("SELECT 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']."'");

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'
);



}


}



}


}




$return = array(
'items' => $rows,
'items' => $rows2,
'items' => $rows3,
'items' => $rows4
);



echo json_encode( $return );