Re: [PHP] Nested database loops and completing an unordered list....
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 I would do that under normal circumstance but I cannot modify the client's table in any way shape or form. I am considering the COMPANY_ID to be the parent at this point and I can get all who belong to a company. I just need to turn that lineage into a tree. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Nested database loops and completing an unordered list....
On 03/01/2012 06:20 PM, Jay Blanchard wrote: [snip] Can you show the output of the function above? [/snip] Doesn't this SQL query return everything that has company_id set to 3 which would it not contain all the data from the other queries combined into one large data set? At this point, I don't believe you have shown your output. Please show the output of your function. 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' 1054Unknown column '' in 'field list' -- Jim Lucas http://www.cmsws.com/ http://www.cmsws.com/examples/ http://www.bendsource.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Nested database loops and completing an unordered list....
On Fri, Mar 2, 2012 at 7:43 AM, Jay Blanchard jay.blanch...@sigmaphinothing.org wrote: 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 I would do that under normal circumstance but I cannot modify the client's table in any way shape or form. I am considering the COMPANY_ID to be the parent at this point and I can get all who belong to a company. I just need to turn that lineage into a tree. Would they let you make a copy or a join table where you could build what you need? Then maybe add a stored proc to move data as needed -- Bastien Cat, the other other white meat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Nested database loops and completing an unordered list....
[snip] Doesn't this SQL query return everything that has company_id set to 3 which would it not contain all the data from the other queries combined into one large data set? [/snip] I could do that, I can return one large dataset for all of the columns shown in the tiers array. I have to remove the DISTINCT's. When I return that dataset in this case I return seven columns of data with the parent being in the leftmost column and descending to the right. The goal with the the recursive function was to get each descendant line so that it could be formatted in a nested unordered list. So in the SQL below you get 9 records. For each of the 9 records you could get any number of children depending on which of the 9 you're looking at. Then for each of those children you could get their descendants and so on. I was doing it the long way at first, until a recursive function was suggested. The problem that I was having there was formatting the ul's and li's properly. Now I feel as if I am really close to a better solution than the brute force method. I may just be a little too frustrated to see what is a simple answer. Thanks for your help! At this point, I don't believe you have shown your output. Please show the output of your function. 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Nested database loops and completing an unordered list....
[snip] ...stuff ... [/snip] A thought occurred to me - I need to call the function at the end of the while loop and then again with different criteria after the while loop? I'll have to test that later today. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Nested database loops and completing an unordered list....
On 03/02/2012 08:27 AM, Jay Blanchard wrote: [snip] Doesn't this SQL query return everything that has company_id set to 3 which would it not contain all the data from the other queries combined into one large data set? [/snip] I could do that, I can return one large dataset for all of the columns shown in the tiers array. I have to remove the DISTINCT's. When I return that dataset in this case I return seven columns of data with the parent being in the leftmost column and descending to the right. The goal with the the recursive function was to get each descendant line so that it could be formatted in a nested unordered list. So in the SQL below you get 9 records. For each of the 9 records you could get any number of children depending on which of the 9 you're looking at. Then for each of those children you could get their descendants and so on. I was doing it the long way at first, until a recursive function was suggested. The problem that I was having there was formatting the ul's and li's properly. I'm not saying you should get rid of the recursive function calls, but rather, why not pull all your data in one SQL call, then use recursive functions on the returned array of data. It will save a little time by not hitting the DB on each function call too. Now I feel as if I am really close to a better solution than the brute force method. I may just be a little too frustrated to see what is a simple answer. Thanks for your help! -- Jim Lucas http://www.cmsws.com/ http://www.cmsws.com/examples/ http://www.bendsource.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Nested database loops and completing an unordered list....
[snip] I'm not saying you should get rid of the recursive function calls, but rather, why not pull all your data in one SQL call, then use recursive functions on the returned array of data. It will save a little time by not hitting the DB on each function call too. [/snip] I'll just need to wrap my head around using a recursive function to iterate through the array. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Nested database loops and completing an unordered list....
I don't how how you keep your data in your database but there is no need to issues that many queries to retrieve your data. From what I understand the data you want to display is hierarchical. Here's an article that will hopefully point you to a solution (there are more out there, some better than other, google mysql hierchical data or database hierarchical data). The article I was talking about is here: http://www.sitepoint.com/hierarchical-data-database/ On Thu, Mar 1, 2012 at 4:29 PM, Jay Blanchard jay.blanch...@sigmaphinothing.org wrote: Good morning PHP groupies! I am working on this tool that will ultimately display a collapsible org chart. The org chart is based on a nested unordered list and that is the heart of my question. The NUL(nested unordered list) is based on a set of database queries - sometimes as many as 14 queries. Each query relies on data returned by all of the the queries before it. So what I am doing right now is this - query generates a list item while this list item get the next level dependent upon this item query generates this list item while this list item get the next level dependent on each list item above ...and so on. (I have written about this before and thought I had it solved, but alas, that is not the case.) The result needs to be something like this: ul lilevel a ul lilevel b/li // has no children lilevel b ul lilevel c/li /ul /li /ul /li /ul This is a semantically and syntacticallycorrect UL. Keep in mind that this can go many levels deeper. The hardest part, and the part that I am looking to accomplish, is closing the list items properly regardless of how deep the tree is. If properly handled this could even be made into JSON with the proper syntax, but I am not worried about that now. I was hoping that a fresh set of eyes would point me to a solution that I obviously cannot see at the moment. Thanks! Jay -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Nested database loops and completing an unordered list....
And see also this, which focuses only on the database part of the problem: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ On Thu, Mar 1, 2012 at 5:08 PM, FeIn aci...@gmail.com wrote: I don't how how you keep your data in your database but there is no need to issues that many queries to retrieve your data. From what I understand the data you want to display is hierarchical. Here's an article that will hopefully point you to a solution (there are more out there, some better than other, google mysql hierchical data or database hierarchical data). The article I was talking about is here: http://www.sitepoint.com/hierarchical-data-database/ On Thu, Mar 1, 2012 at 4:29 PM, Jay Blanchard jay.blanch...@sigmaphinothing.org wrote: Good morning PHP groupies! I am working on this tool that will ultimately display a collapsible org chart. The org chart is based on a nested unordered list and that is the heart of my question. The NUL(nested unordered list) is based on a set of database queries - sometimes as many as 14 queries. Each query relies on data returned by all of the the queries before it. So what I am doing right now is this - query generates a list item while this list item get the next level dependent upon this item query generates this list item while this list item get the next level dependent on each list item above ...and so on. (I have written about this before and thought I had it solved, but alas, that is not the case.) The result needs to be something like this: ul lilevel a ul lilevel b/li // has no children lilevel b ul lilevel c/li /ul /li /ul /li /ul This is a semantically and syntacticallycorrect UL. Keep in mind that this can go many levels deeper. The hardest part, and the part that I am looking to accomplish, is closing the list items properly regardless of how deep the tree is. If properly handled this could even be made into JSON with the proper syntax, but I am not worried about that now. I was hoping that a fresh set of eyes would point me to a solution that I obviously cannot see at the moment. Thanks! Jay -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Nested database loops and completing an unordered list....
On Thu, Mar 1, 2012 at 6:29 AM, Jay Blanchard
jay.blanch...@sigmaphinothing.org wrote:
Good morning PHP groupies!
I am working on this tool that will ultimately display a collapsible org
chart. The org chart is based on a nested unordered list and that is the
heart of my question.
The NUL(nested unordered list) is based on a set of database queries -
sometimes as many as 14 queries. Each query relies on data returned by all
of the the queries before it. So what I am doing right now is this -
query generates a list item
while this list item get the next level dependent upon this item
query generates this list item
while this list item get the next level dependent on each
list item above
...and so on. (I have written about this before and thought I had it solved,
but alas, that is not the case.) The result needs to be something like this:
ul
lilevel a
ul
lilevel b/li // has no children
lilevel b
ul
lilevel c/li
/ul
/li
/ul
/li
/ul
This is a semantically and syntacticallycorrect UL. Keep in mind that this
can go many levels deeper. The hardest part, and the part that I am looking
to accomplish, is closing the list items properly regardless of how deep the
tree is. If properly handled this could even be made into JSON with the
proper syntax, but I am not worried about that now. I was hoping that a
fresh set of eyes would point me to a solution that I obviously cannot see
at the moment.
Thanks!
Jay
Your situation sounds like list of categories for a e-commerce site.
Each category (like a person) is unique and may have a parent category
(boss) like that of a CEO.
** table structure:
CREATE TABLE IF NOT EXIST employees (
employeeID INT NOT NULL PRIMARY KEY,
bossID INT NOT NULL DEFAULT '0',
firstName VARCHAR(50) NOT NULL,
middle VARCHAR(50) NOT NULL,
lastName VARCHAR(50) NOT NULL
)
** SQL query:
SELECT * FROM employees ORDER BY bossID;
** PHP pseudo code:
// $employees = array(0 = array());
// CEO doesn't have a boss thus you get his/her info via $bigBossID =
$employees[0]['subordinates'][0];
// connect to DB
// execute query
// iterate results
while ($row = $result-fetch_assoc()) {
$employees[$row['employeeID']] = $row;
if (!empty($row['bossID'])
$employees[$row['bossID']]['subordinates'] = $row['employeeID'];
}
// free result
// close connection if need be
You can get any person's info via $employees[$employeeID] and get the
manager/director/VP/etc subordinates:
if (isset($employees[$employeeID]['subordinates'])
!empty($employees[$employeeID]['subordinates']))
foreach ($employees[$employeeID]['subordinates'] = $subordinateID) {
// access subordinate's info via $employees[$subordinateID]
}
else
echo {$employees[$employeeID]['firstName']} does not have subordinates.;
If there's any organizational change, the code still works.
HTH,
Tommy
Disclaimer: the above syntax is from memory. I haven't done SQL
manipulation or PHP coding in over a year ;) so check and adjust
accordingly to your RDBMS and application's design.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Nested database loops and completing an unordered list....
Thanks FeIn, I'll give these articles and methods a look this afternoon. I'm sure that it will lead to more questions, so I'll be back. On 3/1/2012 9:16 AM, FeIn wrote: And see also this, which focuses only on the database part of the problem: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ On Thu, Mar 1, 2012 at 5:08 PM, FeIn aci...@gmail.com mailto:aci...@gmail.com wrote: I don't how how you keep your data in your database but there is no need to issues that many queries to retrieve your data. From what I understand the data you want to display is hierarchical. Here's an article that will hopefully point you to a solution (there are more out there, some better than other, google mysql hierchical data or database hierarchical data). The article I was talking about is here: http://www.sitepoint.com/hierarchical-data-database/
Re: [PHP] Nested database loops and completing an unordered list....
On 3/1/2012 9:59 AM, Jay Blanchard wrote: Thanks FeIn, I'll give these articles and methods a look this afternoon. I'm sure that it will lead to more questions, so I'll be back. I also forgot to say that I cannot modify the database structure - the client is very strict about that. But it does look like the adjacency list model - each records list each of its tiers from 1 to 14. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Nested database loops and completing an unordered list....
[snip]…stuff…[/snip]
I am getting close, but I am also getting frustrated. I probably need to walk
away for a bit.
I have an array of tiers….
Array
(
[0] = TIER1DATA
[1] = TIER2DATA
[2] = TIER3DATA
[3] = BUSTIER1DATA
[4] = BUSTIER2DATA
[5] = BUSTIER3DATA
[6] = BUSTIER4DATA
)
Each of the tiers which represent parents in children in pairs. TIER1DATA is
the parent of TIER2DATA, etc. representing 7 columns of data. Here is my code
so far - any insight would be valuable and extremely appreciated by me.
function display_children($i, $child) {
global $dbc;
global $tierArray;
echo $i.br /;
/* retrieve children of parent */
$get = SELECT DISTINCT `. $tierArray[$i] .` FROM `POSITION_SETUP` ;
$get .= WHERE `COMPANY_ID` = '3' ;
if($i 0) {
$get .= AND ` . $tierArray[$i - 1] . ` = ' . $child . ' ;
}
echo $get.br /;
if(!($result = mysql_query($get, $dbc))) {
echo mysql_errno() . \t . mysql_error() . \n;
exit();
}
while($row = mysql_fetch_array($result)) {
/* indent and display the title of this child */
echo str_repeat('nbsp;',$i).$row[$tierArray[$i]].br /\n;
/* get the children's children */
display_children($i + 1, $row[$tierArray[$i]]);
}
}
It does produce the right query for only the first child in each case, I cannot
get it to go deeper. I have just been looking at it for too long. TVMIA!
Re: [PHP] Nested database loops and completing an unordered list....
On Mar 1, 2012, at 6:36 PM, Jay Blanchard wrote:
[snip]…stuff…[/snip]
I am getting close, but I am also getting frustrated. I probably need to walk
away for a bit.
I have an array of tiers….
Array
(
[0] = TIER1DATA
[1] = TIER2DATA
[2] = TIER3DATA
[3] = BUSTIER1DATA
[4] = BUSTIER2DATA
[5] = BUSTIER3DATA
[6] = BUSTIER4DATA
)
Each of the tiers which represent parents in children in pairs. TIER1DATA is
the parent of TIER2DATA, etc. representing 7 columns of data. Here is my code
so far - any insight would be valuable and extremely appreciated by me.
function display_children($i, $child) {
global $dbc;
global $tierArray;
echo $i.br /;
/* retrieve children of parent */
$get = SELECT DISTINCT `. $tierArray[$i] .` FROM `POSITION_SETUP` ;
$get .= WHERE `COMPANY_ID` = '3' ;
if($i 0) {
$get .= AND ` . $tierArray[$i - 1] . ` = ' . $child . ' ;
}
echo $get.br /;
if(!($result = mysql_query($get, $dbc))) {
echo mysql_errno() . \t . mysql_error() . \n;
exit();
}
while($row = mysql_fetch_array($result)) {
/* indent and display the title of this child */
echo str_repeat('nbsp;',$i).$row[$tierArray[$i]].br /\n;
/* get the children's children */
display_children($i + 1, $row[$tierArray[$i]]);
}
}
It does produce the right query for only the first child in each case, I
cannot get it to go deeper. I have just been looking at it for too long.
TVMIA!
Forgot to say that if I isolate any one level without recursion I get all of
the results for that level.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Nested database loops and completing an unordered list....
On 03/01/2012 04:39 PM, Jay Blanchard wrote:
On Mar 1, 2012, at 6:36 PM, Jay Blanchard wrote:
[snip]…stuff…[/snip]
I am getting close, but I am also getting frustrated. I probably need to walk
away for a bit.
I have an array of tiers….
Array
(
[0] = TIER1DATA
[1] = TIER2DATA
[2] = TIER3DATA
[3] = BUSTIER1DATA
[4] = BUSTIER2DATA
[5] = BUSTIER3DATA
[6] = BUSTIER4DATA
)
Each of the tiers which represent parents in children in pairs. TIER1DATA is
the parent of TIER2DATA, etc. representing 7 columns of data. Here is my code
so far - any insight would be valuable and extremely appreciated by me.
function display_children($i, $child) {
global $dbc;
global $tierArray;
echo $i.br /;
/* retrieve children of parent */
$get = SELECT DISTINCT `. $tierArray[$i] .` FROM `POSITION_SETUP` ;
$get .= WHERE `COMPANY_ID` = '3' ;
if($i 0) {
$get .= AND ` . $tierArray[$i - 1] . ` = ' . $child . ' ;
}
echo $get.br /;
if(!($result = mysql_query($get, $dbc))) {
echo mysql_errno() . \t . mysql_error() . \n;
exit();
}
while($row = mysql_fetch_array($result)) {
/* indent and display the title of this child */
echo str_repeat('nbsp;',$i).$row[$tierArray[$i]].br /\n;
/* get the children's children */
display_children($i + 1, $row[$tierArray[$i]]);
}
}
It does produce the right query for only the first child in each case, I cannot
get it to go deeper. I have just been looking at it for too long. TVMIA!
Forgot to say that if I isolate any one level without recursion I get all of
the results for that level.
How are you calling this? What are your initial arguments?
What does your DB schema look like?
Can you show a brief example of the data in the table?
Dump: SELECT * FROM position_setup WHERE company_id='3'
Can you show the output of the function above?
--
Jim Lucas
http://www.cmsws.com/
http://www.cmsws.com/examples/
http://www.bendsource.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Nested database loops and completing an unordered list....
On Thu, Mar 1, 2012 at 4:36 PM, Jay Blanchard jay.blanch...@sigmaphinothing.org wrote: [snip]…stuff…[/snip] I am getting close, but I am also getting frustrated. I probably need to walk away for a bit. I have an array of tiers…. Array ( [0] = TIER1DATA [1] = TIER2DATA [2] = TIER3DATA [3] = BUSTIER1DATA [4] = BUSTIER2DATA [5] = BUSTIER3DATA [6] = BUSTIER4DATA ) Each of the tiers which represent parents in children in pairs. TIER1DATA is the parent of TIER2DATA, etc. representing 7 columns of data. snip/ How are those tiers assembled? Do you know what the table structure is? Can you disclose it in an obfuscate fashion? You're asking for help on the best way to retrieve and manipulate the data in PHP but you haven't disclose how it's represented at the source. That's like asking us how to get water without telling the location of the water: ocean, lake, underground, etc.. You get the idea. Best regards, Tommy -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Nested database loops and completing an unordered list....
On Mar 1, 2012, at 7:45 PM, Jim Lucas wrote:
On 03/01/2012 04:39 PM, Jay Blanchard wrote:
On Mar 1, 2012, at 6:36 PM, Jay Blanchard wrote:
[snip]…stuff…[/snip]
I am getting close, but I am also getting frustrated. I probably need to
walk away for a bit.
I have an array of tiers….
Array
(
[0] = TIER1DATA
[1] = TIER2DATA
[2] = TIER3DATA
[3] = BUSTIER1DATA
[4] = BUSTIER2DATA
[5] = BUSTIER3DATA
[6] = BUSTIER4DATA
)
Each of the tiers which represent parents in children in pairs. TIER1DATA
is the parent of TIER2DATA, etc. representing 7 columns of data. Here is my
code so far - any insight would be valuable and extremely appreciated by me.
function display_children($i, $child) {
global $dbc;
global $tierArray;
echo $i.br /;
/* retrieve children of parent */
$get = SELECT DISTINCT `. $tierArray[$i] .` FROM `POSITION_SETUP` ;
$get .= WHERE `COMPANY_ID` = '3' ;
if($i 0) {
$get .= AND ` . $tierArray[$i - 1] . ` = ' . $child . ' ;
}
echo $get.br /;
if(!($result = mysql_query($get, $dbc))) {
echo mysql_errno() . \t . mysql_error() . \n;
exit();
}
while($row = mysql_fetch_array($result)) {
/* indent and display the title of this child */
echo str_repeat('nbsp;',$i).$row[$tierArray[$i]].br /\n;
/* get the children's children */
display_children($i + 1, $row[$tierArray[$i]]);
}
}
It does produce the right query for only the first child in each case, I
cannot get it to go deeper. I have just been looking at it for too long.
TVMIA!
Forgot to say that if I isolate any one level without recursion I get all of
the results for that level.
How are you calling this? What are your initial arguments?
What does your DB schema look like?
Can you show a brief example of the data in the table?
Dump: SELECT * FROM position_setup WHERE company_id='3'
Can you show the output of the function above?
I initialize it by calling the function like this:
display_children(0, 0)
Each of the tiers is located in adjacent columns in a single table
tier1data, tier2data, tier 3 data……bustier1data, bustier2data…..
The array retrieves only the relevant tiers for this company and that is the
output of that array at the top of my example.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Nested database loops and completing an unordered list....
[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' 1054Unknown column '' in 'field list' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Nested database loops and completing an unordered list....
On 2012-03-01, at 9:20 PM, Jay Blanchard jay.blanch...@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 `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' 1054Unknown 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
