PDA

View Full Version : Create JSON With PHP - Almost there!



mobile207
29 Apr 2011, 10:37 AM
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.

pmatsumura
29 Apr 2011, 11:09 AM
Why don't you just create one big assoc. array and put one into the other like so:



$subcategory_array['items'] = $listings_array;
$category_array['items'] = $subcategory_array;
$regions_array['items'] = $category_array;

$bigArray = array();
$bigArray['items'] = $regions_array;

$json = json_encode($bigArray);


Then you don't have to take care of the syntax. Just make sure your array structure looks the way you want it to and let json_encode do the rest.

mobile207
29 Apr 2011, 11:31 AM
Thanks for your reply, I really appreciate it.

Two things:
1) Only one array is coming through, it is nested perfectly and it validates, I just need to repeat it somehow (there are 5 listings but only the first is showing up in the JSON)

2) There are no [ ] brackets in the JSON file so it fails in my app. If I manually add them to what the following PHP code produces everything is kosher. At least for that one array.

So my question is how do I get the brackets to show up and echo all 5 listings in the "listings" table?





Here is where I am at:



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

$regions_array = array();

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

$regions_array = $rows;




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

$subcategories_array = array();


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


$subcategories_array = $rows2;





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

$categories_array = array();

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


$categories_array = $rows3;




$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']."' ");

$listings_array = array();


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' ],
'model' => 'Track'
);

$listings_array = $rows4;




$regions_array['items'] = $subcategories_array;
$subcategories_array['items'] = $categories_array;
$categories_array['items'] = $listings_array;



$bigArray = array();



$bigArray['items'] = $regions_array;





}

#echo ']';



//End Category Loop


}

//End Subcats Loop


}

//End Regions Loop


}

//End Listings Loop





echo json_encode($bigArray);

pmatsumura
1 May 2011, 11:03 PM
So my question is how do I get the brackets to show up and echo all 5 listings in the "listings" table?

In order to have the [] surround your bigArray, you need to put bigArray into another empty array.

Your logic keeps on overwriting your arrays. Think about you're trying to achieve and "build" your bigArray step by step.

mobile207
3 May 2011, 7:30 AM
Thanks.

So I should have some kind of break after each loop and then cycle through each region? How would I accomplish this so that I have the following hierarchy:


Northern
Category
Subcategory
Listing 1
Listing 2
Subcategory2
Listing 1
Listing 2
Listing 3

Western
Category
Subcategory
Listing 1
Listing 2

Southern
Category
Subcategory
Listing 1
Listing 2
Listing 3



I had it working when I was just echoing out the name (built a list using ul's and li's no problem). My trouble started when I put the results into arrays (had to be done this way to build the proper JSON structure for the app I am working on)..

pmatsumura
3 May 2011, 8:14 AM
Please try this. I can't test the code, since I don't have the model setup.

$bigArray = array();
$bigArray['items'] = array();

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

while( $row = mysql_fetch_array( $rs ) ){
$region = array(
'text'=> $row[ 'region_name' ],
'leaf' => false,
'info' => $row[ 'region_name' ],
'model' => 'Genre',
'items' => array() //category items go here
);

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

while( $row2 = mysql_fetch_array( $rs2 ) ){
$category = array(
'text'=> $row2[ 'category_name' ],
'leaf' => false,
'category_id' => $row2[ 'category_id' ],
'info' => $row2[ 'category_name' ],
'model' => 'Artist',
'items' => array() //sub category items go here
);

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

while( $row3 = mysql_fetch_array( $rs3 ) ){
$subcategory = array(
'parent_category'=> $row3[ 'parent_category' ],
'subcategory_id'=> $row3[ 'subcategory_id' ],
'leaf' => false,
'info' => $row3[ 'subcategory_name' ],
'text' => $row3[ 'subcategory_name' ],
'model' => 'Album',
'items' => array() //listing items go here
);

//start listings
$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 ) ){
$listing = array(
'listing_subcat_id'=> $row4[ 'listing_subcat_id' ],
'listing_id'=> $row4[ 'listing_id' ],
'leaf' => true,
'text' => $row4[ 'listing_name' ],
'info' => $row4[ 'listing_description' ],
'model' => 'Track'
);

$subcategory['items'][] = $listing;

}

$category['items'][] = $subcategory;

}

$region['items'][] = $category;

}

$bigArray['items'][] = $region;
}
echo json_encode($bigArray);

mobile207
3 May 2011, 9:20 AM
All six regions are showing up perfectly.

Categories are listed 1 per region though

Region 1 - Category 1
Region 2 - Category 2
Region 3 - Category 3
Region 4 - Category 4
Region 5 - Category 5
Region 6 - Category 6


For categories that have subcategories with listings it seems to work.

I only want to show a category if it has a subcategory with a listing (if that makes sense).

Thanks for your help so far, really appreciate it.

pmatsumura
3 May 2011, 12:31 PM
Categories are listed 1 per region though
I don't quite understand.


I only want to show a category if it has a subcategory with a listing (if that makes sense).


Then only add it to your region array, if it has more than one sub category. And only add a sub category to the category items, if it has more than one listing. Get it? This (http://php.net/manual/en/function.count.php) may help.

mobile207
3 May 2011, 12:57 PM
Thanks! I got it to work, will post the full working code once I figure out the last part.

Thank you very much for your time

wearcheck1
26 Sep 2013, 6:36 AM
I know this thread is old but its still very useful. Are you using this method to create a JSON TreeStore? I'm trying to populate a Tree and I'd like to generate the proper JSON data from the SQL server rather than hard code it.

Am I on the right track here?