Lots of loops

[chrisleon]

I have a program/script that I have to write, and I am boggled. I hope I can explain this correctly.

I have a database with a field for employee numbers and supervisors for the employee.

I start with the CEO's employee number (5505) and run a quick query on the supervisors field with anyone that has a supervisor of 5505. From there I run another query to find out there employee numbers, and any employees under them. In essense, I am creating an employee family tree. But currently, I am writing a query then a loop then another query then another loop, and I just do not know where it ends. There are some branches that go 5 down, others that only go down 3. I write enough querys and loops to go down through the VP's and midlevel managers, but once it gets to the employees, it craps out. Can I do this through a function or something else besides what I am currently doing.

[bpat1434]

Have you looked at JOIN-ing tables in the SQL query so you get a more proper result set?

I mean, here's a very basic example:

SELECT a.*, b.*, c.* FROM employees a
INNER JOIN managers b
ON a.manager_id=b.id
INNER JOIN supervisors c
ON b.supervisor_id=c.id
WHERE c.id='5505'
ORDER BY a.id ASC

That would return only the employees under the managers that are under the supervisor with id 5505.

Now, your database structure may need work, but that would be the easiest way to actually do it. Let mySQL do the grunt work (it's what it's there for) and just let PHP run one loop to display the info 😉

SQL can be tricky, so I'd suggest a book. One book that I've found is clear and concise is: "Learning SQL" by Alan Bealieau (published by O'Reily). It goes over plenty of info on SQL and how to use it to your advantage. Plus, it has a very similar query to your situation 🙂