On 12-03-22 03:54 PM, Jay Blanchard wrote:
[snip]
At one point you indicated all the data was coming from one table. Can you send me the table fields and indicate which fields are used to determine parent child relationship? Also 2 sample rows of data which have a relationship would be helpful.
[/snip]
Columns - tier1, tier2, tier3, tier4 etc. (ends with tier14)
Children of tier1 are tier2 -
select distinct tier2 from table where tier1 = "foo" and company = "1"
select distinct tier2 from table where tier1 = "bar" and company = "1"
etc.
Children of tier2 are tier3, etc.
tier1 tier2 tier3
1, executive, ceo, ceo
1, executive, vp-ops, vp-ops
1, executive, vp-admin, vp-admin mgr
1, executive, vp-admin, vp-admin ops mgr
1, executive, vp-admin, vp-admin mgr
1, executive, vp-admin, vp-admin clerk
1, professional pro-mgr pro-admin
1, professional pro-IT pro-dev
1, professional pro-IT pro-infra
1, professional pro-IT pro-dev
1, technician tech-admin tech-admin mgr
1, technician tech-ops tech-ops mgr
Thanks for all of your help. I know I am being a PITA.
Your data structure doesn't appear to be very ummm normalized...
Nonetheless, the following should do it:
<?php
//
// Establish the root.
//
$company = 1;
$query =
"SELECT DISTINCT "
." tier1 AS id "
."FROM "
." tiers "
."WHERE "
." company = {$company} ";
$root = array();
$children = array();
if( $db->query( $query ) )
{
while( ($row = $db->fetchRow()) )
{
$id = $row['id'];
unset( $child );
$child = array
(
'id' => $id,
'parentId' => false,
'children' => array();
);
$root[$id] = &$child;
$children[$id][] = &$child;
}
}
//
// Establish the nested levels.
//
for( $tier = 2; $tier <= 14; $tier++ )
{
if( !($parents = &$children) )
{
break;
}
$parentTier = $tier - 1;
$parentIds = array();
foreach( array_keys( $parents ) as $parentId )
{
$parentIds[$parentId] = $db->quote( $parentId );
}
$query =
"SELECT DISTINCT "
." tier{$tier} AS id, "
." tier{$parentTier} AS parentId "
."FROM "
." tiers "
."WHERE "
." company = {$company} "
." AND "
." tier{$parentTier} IN (".implode( ',', $parentIds ).") ";
if( $db->query( $query ) )
{
unset( $children );
$children = array();
while( ($row = $db->fetchRow()) )
{
$id = $row['id'];
$pid = $row['parentId'];
unset( $child );
$child = array
(
'id' => $id,
'parentId' => $pid,
'children' => array();
);
$children[$id][] = &$child;
foreach( $parents[$pid] as &$items )
{
foreach( $items as &$item )
{
$item['children'][$id] = &$child;
}
}
}
}
}
$json = JSON_encode( $root );
?>
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.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
