Structuring MySQL

[lister86]

Hello

I have been trying to produce an organisation chart using mysql and php but i have been running into problems.

1. Tried nested arrays then found google API
2. Tried Googles API but couldn't get it workign with php.
3. Storing Hierarchical Data in a Database from sitepoint.

It seems i keep switching and changing but just trying to find an easy way to display an organisation chart.

So any help would be great i like the 3 option but i am unable to get information as i just keep getting blank pages.

PS. Keep in mind i am new to php and mysql.

Thank you any help would be helpful.

[r937]

there's not a whole lot that we can say, other than perhaps to offer our condolences for your difficulties

i think the first place you could start is with a flat file containing a list of everybody in your organization, along with the name of their supervisor

just a simple text file, each row with 2 names on it

write a php page that lets you click on a link, so that it gets the file and reads through it and prints out all the names in the file on the web page

once you have successfully done this, come back and i'll show you how to store the data in a database table

[lister86]

ok done.

I understand how to do basic level of php and mysql its just the displaying the data in a hierarchical chart which i am finding the hardest.

[r937]

so you have already stored your data in a table? could i see what you get for the SHOW CREATE TABLE command please?

[Vali]

Have your table like:
Employee
----------
id
boss_id
name
etc.

Your "boss_id" links to "id", so you have a 1 to many relationship there.

When you display it, do it like this:
----------
SELECT * WHERE id = 5; (5 is my boss)
> show the row
SELECT * WHERE id = (id I just got)
> show the row
----------
And so on.

Basically, select #1 returns 1 record. Select #2 returns everyone who that the first record for a boss.

You loop that for each result, in a recursive function, and your done.

Ps:
- This method works, but it's not the fastest way to do it (just the simplest to explain/code)

[r937]

You loop that for each result, in a recursive function, and your done.

ouch!~

but yes, that works

[lister86]

+-------+--------------------------------------------------------
----------------------------------------------------------------+
| Table | Create Table
|
+-------+--------------------------------------------------------
----------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id1` int(6) NOT NULL,
`id2` int(6) NOT NULL,
`name` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------
----------------------------------------------------------------+
1 row in set (0.00 sec)

I was thinking of a similar to what was suggested like ID. id2(manager), name.

[r937]

id1 and id2 are not the best names, i prefer id and manager_id

[lister86]

ok i'll sort that out.

+-------+--------------------------------------------------------------
----------------------------------------------------------------------+
| Table | Create Table
|
+-------+--------------------------------------------------------------
----------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(6) NOT NULL,
`manager_id` int(6) NOT NULL,
`name` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------
----------------------------------------------------------------------+

[imaginethis]

Sounds like someone needs to do a little domain modeling.