[thelist] Struggling with mySQL synatx
Andy Warwick
mailing.lists at creed.co.uk
Fri Aug 27 11:01:30 CDT 2004
I'm trying to write a page that lists the staff within each department,
but I'm having trouble with the database SQL statement syntax.
My (simplified) data structure is:
=======================
PEOPLE
--------------------------------
UID Name Department
--------------------------------
1 Alpha 1
2 Bravo 2
3 Charlie 2
4 Delta 5
5 Echo 6
6 Foxtrot 7
========================
========================
DEPARTMENTS
---------------------------------
UID Name
---------------------------------
1 Marketing
2 Studio
3 Sales
4 Overseas Sales
5 Local Sales
6 American Sales
7 European Sales
========================
========================
DEPARTMENT_STRUCTURE
---------------------------------
UID Parent Child
---------------------------------
1 NULL 1
2 NULL 2
3 NULL 3
4 3 4
5 3 5
6 4 6
7 4 7
========================
Using the DEPARTMENT_STRUCTURE table, we can see at the root level of
departments we have: Marketing, Studio and Sales; Overseas Sales and
Local Sales are children of Sales; American Sales and European Sales
are children of Overseas Sales.
What I want to do is have a series of pop-up menus to 'drill-down' the
data, revealing the staff in each department as it does so.
So the first pop-up would read: <Marketing> <Studio> <Sales>
If you chose Marketing, you'd be shown the PEOPLE's entry for Alpha.
If you chose Studio, you'd be shown the PEOPLE's entry for Bravo and
Charlie.
Fairly easy so far.
My problem comes with the third option: Sales.
I'd initially like it to show all the staff that work in all the
sub-categories of Sales--Delta, Echo & Foxtrot--plus another pop-up
with the two Sales sub-options. Only when one of these sub-options is
chosen, would the list shorten to the appropriate names. If the chosen
option is for Overseas Sales, you'd see Echo and Foxtrot and *another*
pop-up for American Sales and European Sales. Choose American Sales,
and you'd just be left with Echo.
Make sense?
What I can't figure is the syntax of the mySQL call I need to make to
retrieve the list of people at each stage, before you've drilled down
in sufficient detail. Indeed, in terms of PHP and mySQL I'm struggling
with the general, correct way to code this, given the structure could
theoretically be many, many levels deep.
Any help or insight gratefully received.
TIA
--
Andy Warwick
Creed New Media. <http://www.creed.co.uk>
More information about the thelist
mailing list