1. #1
    Sencha User
    Join Date
    Apr 2011
    Posts
    49
    Vote Rating
    0
    mobile207 is on a distinguished road

      0  

    Cool Create JSON With PHP - Almost there!

    Create JSON With PHP - Almost there!


    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:
    Code:
    {
    "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:
    PHP Code:
    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.

  2. #2
    Ext JS Premium Member
    Join Date
    Jun 2009
    Location
    Switzerland
    Posts
    83
    Vote Rating
    0
    pmatsumura is on a distinguished road

      0  

    Default


    Why don't you just create one big assoc. array and put one into the other like so:
    PHP Code:

    $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.
    Talk to me on Twitter @patmatsumura

  3. #3
    Sencha User
    Join Date
    Apr 2011
    Posts
    49
    Vote Rating
    0
    mobile207 is on a distinguished road

      0  

    Default


    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:
    PHP Code:


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

  4. #4
    Ext JS Premium Member
    Join Date
    Jun 2009
    Location
    Switzerland
    Posts
    83
    Vote Rating
    0
    pmatsumura is on a distinguished road

      0  

    Default


    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.
    Talk to me on Twitter @patmatsumura

  5. #5
    Sencha User
    Join Date
    Apr 2011
    Posts
    49
    Vote Rating
    0
    mobile207 is on a distinguished road

      0  

    Default


    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)..

  6. #6
    Ext JS Premium Member
    Join Date
    Jun 2009
    Location
    Switzerland
    Posts
    83
    Vote Rating
    0
    pmatsumura is on a distinguished road

      0  

    Default


    Please try this. I can't test the code, since I don't have the model setup.
    PHP Code:
    $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); 
    Last edited by pmatsumura; 3 May 2011 at 8:22 AM. Reason: Typo
    Talk to me on Twitter @patmatsumura

  7. #7
    Sencha User
    Join Date
    Apr 2011
    Posts
    49
    Vote Rating
    0
    mobile207 is on a distinguished road

      0  

    Default


    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.

  8. #8
    Ext JS Premium Member
    Join Date
    Jun 2009
    Location
    Switzerland
    Posts
    83
    Vote Rating
    0
    pmatsumura is on a distinguished road

      0  

    Default


    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 may help.
    Talk to me on Twitter @patmatsumura

  9. #9
    Sencha User
    Join Date
    Apr 2011
    Posts
    49
    Vote Rating
    0
    mobile207 is on a distinguished road

      0  

    Default


    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

  10. #10
    Sencha User
    Join Date
    Aug 2013
    Posts
    6
    Vote Rating
    0
    wearcheck1 is on a distinguished road

      0  

    Default Old thread but useful

    Old thread but useful


    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?

Similar Threads

  1. How to create JSON object
    By samwells in forum Ext GWT: Help & Discussion (1.x)
    Replies: 7
    Last Post: 10 Feb 2009, 5:07 AM
  2. How to create JSON object
    By samwells in forum Community Discussion
    Replies: 0
    Last Post: 9 Feb 2009, 7:36 PM
  3. Is it possible to create a JSON file in ext
    By D-L in forum Ext 2.x: Help & Discussion
    Replies: 5
    Last Post: 7 Jan 2009, 11:22 PM
  4. record create from JSON
    By Radziu in forum Ext 2.x: Help & Discussion
    Replies: 17
    Last Post: 25 Jan 2008, 6:11 AM
  5. create Json store with local json data
    By gadgetfbi in forum Ext 2.x: Help & Discussion
    Replies: 1
    Last Post: 21 Dec 2007, 7:05 AM

Thread Participants: 2