SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Structuring MySQL

    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    so you have already stored your data in a table? could i see what you get for the SHOW CREATE TABLE command please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Vali View Post
    You loop that for each result, in a recursive function, and your done.
    ouch!~

    but yes, that works

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    +-------+--------------------------------------------------------
    ----------------------------------------------------------------+
    | 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.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    id1 and id2 are not the best names, i prefer id and manager_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 |
    +-------+--------------------------------------------------------------
    ----------------------------------------------------------------------+

  10. #10
    SitePoint Guru
    Join Date
    Jan 2005
    Location
    heaven
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sounds like someone needs to do a little domain modeling.
    Creativity knows no other restraint than the
    confines of a small mind.
    - Me
    Geekly Humor
    Oh baby! Check out the design patterns on that framework!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •