Re: Thinking out loud - a continuation...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]


[snip]
On 3/30/2012 1:14 PM, Robert Cummings wrote:
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.
[/snip]
Thanks Robert - I'll give this a go later today.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



[PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [Find Someone]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux