Re: Thinking out loud - a continuation...

On 12-03-27 11:11 AM, Jay Blanchard wrote:

[snip]On 3/27/2012 12:21 AM, Robert Cummings wrote:

[-- SNIP --] Essentially, entries at the root and entries for the children are just auto indexed array items but the actual entries in those arrays retain the associative index structure for retrieval of the specific information. let me know and I can probably whip you up something.

Robert that looks correct. Here is an example of the JSON that the guy provided for me - var json = {

          id: "node02",
          name: "0.2",
          data: {},
          children: [{
              id: "node13",
              name: "1.3",
              data: {},
              children: [{
                  id: "node24",
                  name: "2.4",
                  data: {},
                  children: [{
                      id: "node35",
                      name: "3.5",
                      data: {},
                      children: [{
                          id: "node46",
                          name: "4.6",
                          data: {},
                          children: []
                      }]
                  }, {
                      id: "node37",
                      name: "3.7",
                      data: {},
                      children: [{
                          id: "node48",
                          name: "4.8",
                          data: {},
                          children: []
                      }, {
                          id: "node49",
                          name: "4.9",
                          data: {},
                          children: []
                      }, {
                          id: "node410",
                          name: "4.10",
                          data: {},
                          children: []
                      }, {
                          id: "node411",
                          name: "4.11",
                          data: {},
                          children: []
                      }]
                  },

Of course he properly closes up the JSON. I inserted id's (just an auto-incrementing number) and the data portion where needed. The name: is the part that has been the result of what you did before.

Here's the code... I did a bit of shuffling and actually tested against a test db table: <?mysql :) DROP TABLE IF EXISTS tiers; CREATE TABLE tiers (

    company     INT                 NOT NULL,
    tier1       VARCHAR( 32 )       NOT NULL        DEFAULT '',
    tier2       VARCHAR( 32 )       NOT NULL        DEFAULT '',
    tier3       VARCHAR( 32 )       NOT NULL        DEFAULT '',
    tier4       VARCHAR( 32 )       NOT NULL        DEFAULT '',
    tier5       VARCHAR( 32 )       NOT NULL        DEFAULT '',
    tier6       VARCHAR( 32 )       NOT NULL        DEFAULT '',
    tier7       VARCHAR( 32 )       NOT NULL        DEFAULT '',
    tier8       VARCHAR( 32 )       NOT NULL        DEFAULT '',
    tier9       VARCHAR( 32 )       NOT NULL        DEFAULT '',
    tier10      VARCHAR( 32 )       NOT NULL        DEFAULT '',
    tier11      VARCHAR( 32 )       NOT NULL        DEFAULT '',
    tier12      VARCHAR( 32 )       NOT NULL        DEFAULT '',
    tier13      VARCHAR( 32 )       NOT NULL        DEFAULT '',
    tier14      VARCHAR( 32 )       NOT NULL        DEFAULT ''

); INSERT INTO tiers (company, tier1, tier2, tier3) VALUES (1, 'exec-001','sub-exec-011','sub-sub-exec-111'), (1, 'exec-001','sub-exec-011','sub-sub-exec-112'), (1, 'exec-001','sub-exec-012','sub-sub-exec-121'), (1, 'exec-001','sub-exec-012','sub-sub-exec-122'), (1, 'exec-002','sub-exec-021','sub-sub-exec-211'), (1, 'exec-002','sub-exec-021','sub-sub-exec-212'), (1, 'exec-002','sub-exec-022','sub-sub-exec-221'), (1, 'exec-002','sub-exec-022','sub-sub-exec-222'); ?> And here's the code: <?php function getTiers( $company ) {

    //
    // Establish the root.
    //

$sDb = &ijinn_getServiceRef( 'dbManager' ); $db = &$sDb->getConnectionRef();

    $query =
        "SELECT DISTINCT "
       ."   * "
       ."FROM "
       ."   tiers "
       ."WHERE "
       ."   company = {$company} ";

    $root = array();
    if( $db->query( $query ) )
    {
        while( ($row = $db->fetchRow()) )
        {
            $focus = &$root;
            for( $i = 1; $i <= 14; $i++ )
            {
                $name = trim( $row['tier'.$i] );
                if( $name === '' )
                {
                    break;
                }

                if( !isset( $focus[$name] ) )
                {
                    $focus[$name] = array
                    (
                        'name' => $name,
                        'children' => array(),
                    );
                }

                $focus = &$focus[$name]['children'];
            }
        }
    }

    $wrapper = array
    (
        'children' => &$root
    );

    postProcessTiers( $wrapper );

    return $root;

} function postProcessTiers( &$root ) {

    $root['children'] = array_values( $root['children'] );

    foreach( array_keys( $root['children'] ) as $index )
    {
        postProcessTiers( $root['children'][$index] );
    }

} function getTiersJson( $company ) {

    $tiers = getTiers( $company );
    $json = JSON_encode( $tiers );

} $tiersJson = getTiersJson( 1 ); ?> This will output JSON with the following structure: <?js [

    {
        "name":"exec-001",
        "children":[
            {
                "name":"sub-exec-011",
                "children":[
                    {
                        "name":"sub-sub-exec-111",
                        "children":[]
                    },
                    {
                        "name":"sub-sub-exec-112",
                        "children":[]
                    }
                ]
            },
            {
                "name":"sub-exec-012",
                "children":[
                    {
                        "name":"sub-sub-exec-121",
                        "children":[]
                    },
                    {
                        "name":"sub-sub-exec-122",
                        "children":[]
                    }
                ]
            }
        ]
    },
    {
        "name":"exec-002",
        "children":[
            {
                "name":"sub-exec-021",
                "children":[
                    {
                        "name":"sub-sub-exec-211",
                        "children":[]
                    },
                    {
                        "name":"sub-sub-exec-212",
                        "children":[]
                    }
                ]
            },
            {
                "name":"sub-exec-022",
                "children":[
                    {
                        "name":"sub-sub-exec-221",
                        "children":[]
                    },
                    {
                        "name":"sub-sub-exec-222",
                        "children":[]
                    }
                ]
            }
        ]
    }

] ?> PHP is smart enough to detect an array that only has consecutive integer keys and create the appropriate JavaScript array object. So we don't have to do any special processing of the JSON after we've post processed the tier structure itself. Cheers, Rob -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized.