Re: Nested database loops and completing an unordered list....

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

 




On 2012-03-01, at 9:20 PM, Jay Blanchard <jay.blanchard@xxxxxxxxxxxxxxxxxxx> wrote:

>> [snip]
>> Can you show the output of the function above?
>> [/snip]
> 
> 0
> SELECT DISTINCT `TIER1DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' 
> Executives and Management
> 
> Normally this query alone returns 9 rows of data. Each of these rows should be included in the next query where TIER1DATA = each of the nine in succession
> 
> 1
> SELECT DISTINCT `TIER2DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' AND `TIER1DATA` = 'Executives and Management' 
> Executives and ManagementLeadership
> 2
> SELECT DISTINCT `TIER3DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' AND `TIER2DATA` = 'Executives and ManagementLeadership' 
>  Executives and ManagementLeadershipManager
> 3
> SELECT DISTINCT `BUSTIER1DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' AND `TIER3DATA` = 'Executives and ManagementLeadershipManager' 
>   Knee
> 4
> SELECT DISTINCT `BUSTIER2DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' AND `BUSTIER1DATA` = 'Knee' 
>    KneeDIV01
> 5
> SELECT DISTINCT `BUSTIER3DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' AND `BUSTIER2DATA` = 'KneeDIV01' 
>     KneeDIV01DEPT02
> 6
> SELECT DISTINCT `BUSTIER4DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' AND `BUSTIER3DATA` = 'KneeDIV01DEPT02' 
>      KneeDIV01DEPT02GRP04
> 7
> SELECT DISTINCT `` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' AND `BUSTIER4DATA` = 'KneeDIV01DEPT02GRP04' 
> 1054    Unknown column '' in 'field list' 
> -- 
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 

My usual approach to a problem like this to to includes a parent column in the table

ID (int pk)
Parent ( default null ) // no parent
Item
Itemtype
[etc]

Parent will then hold either a null if a top level item, or a structured path ( 1/10/24 ) that notes the parents of the item all the way up to the parent. That way, a single query will get you all items in that parent's lineage to whatever depth is needed by using the child's value

Select * from table where parent = '1/10' 

Would retrieve all items that are children of a top level of 1 and a second level of 10

Hth

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




[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux