[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