outputting recursive table

gordonisgod

I'm trying to solve a problem that is probably really easy.

I have thre mysql tables:

DEPARTMENT

DEPTID
DEPTNAME
PARENTDEPTID

EMPLOYEE

EMPLOYEEID
LASTNAME
....

EMPLOYEE_DEPT

EMPLOYEEID
DEPTID

The DEPARTMENT table is recurisve, so for example

DEPTID=1
DEPTNAME = Photography
PARENTID = NULL

DEPTID=2
DEPTNAME = Editing
PARENTID = 1

The EMPLOYEE_DEPT table is a lookup table because one employee may belong to multilple depts.

So, my query is:
$query3 = "SELECT EMPLOYEE.firstname, EMPLOYEE.lastname, EMPLOYEE.email,
EMPLOYEE.phonex, EMPLOYEE.description, DEPARTMENT.departname, DEPARTMENT.DeptParentID
FROM EMPLOYEE, DEPARTMENT
LEFT JOIN EMPLOYEE_DEPT ON EMPLOYEE_DEPT.DeptID = DEPARTMENT.DEPTID
WHERE EMPLOYEE.employeeid = EMPLOYEE_DEPT.employeeid
ORDER BY DEPARTMENT.Departname, EMPLOYEE.lastname ASC";

I want to output this into a table in hierarchical format, eg.,

Photography Department
Editing
Jeanne Blue
Same Green
Marketing
Jeanne Blue
Lisa Read

Web Department
Joe Blow

I've been changing my SQL query around, but I can never get a list of ParentIDs and their child Depts.

Here's my code:

$result = mysql_query($query3) or die(mysql_error());

while ($row = mysql_fetch_array($result))
//assign the variables
$firstname = $row["firstname"];
$lastname = $row["lastname"];
$email = $row["email"];
$phonex = $row["phonex"]; $description = $row["description"];

$department = $row[5];
$parentdepartment = $row[6];

// Checks to see if a new category has
//started and if so draws the appropriate row.

If($row[5] != $previous) {
print "<tr><td colspan='3' bgcolor='pink'>$row[5]</td></tr>\n";;
}

// Sets the $previous variable to allow new
// category IF statement to have something to
// compare to on next loop.

$previous = $row[5];
if (empty($description)) {
print "<tr><td valign='top'>$lastname, $firstname </td>\n";
} else {
print "<tr><td valign='top'>$lastname, $firstname $description </td>\n";
}
print "<td valign='top'>$email</td>\n";
print "<td valign='top' align='right'>x$phonex</td>\n";
print "</tr>\n";

TIA

leatherback

This is what I use for 1 sub_level nested tables:

$result = mysql_query("select * from table where parentid=NULL");
// I actually use -1. Not sure if NULL works..

while ($row = mysql_fetch_array($result))
  {
      echo " Your mainset ouput";
  $query = select * from table where parentid = $row[id];
  $sub_result = mysql_query($query);
    while($sub_row = mysql_fetch_array($sub_result))
      {
      echo " Your subset ouput";
      }
  }

You could of course, for multiple levels, adapt this with an option where you set $result to 1 every time you have results, and call a function to query the database with a parent ID as long as you keep getting results.

if($results=1) {
$children = get_children($ParentId)
}