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

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

 



On Thu, Mar 1, 2012 at 6:29 AM, Jay Blanchard
<jay.blanchard@xxxxxxxxxxxxxxxxxxx> 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>
> <li>level a
> <ul>
> <li>level b</li> // has no children
> <li>level b
> <ul>
> <li>level 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




[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