Re: Nested database loops and completing an unordered list....
| From: | Bastien | Date: | Fri, 02 Mar 2012 05:24:08 +0000 |
| Subject: | Re: Nested database loops and completing an unordered list.... | ||
| References: | 1 2 3 4 5 6 7 8 9 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-316833@lists.php.net to get a copy of this message | ||
On 2012-03-01, at 9:20 PM, Jay Blanchard <jay.blanchard@sigmaphinothing.org> 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 Ô„3Ö/í B
> Ì t(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