CODEWITHSUNDEEP
phpjquerymysql
BaconJuice
BaconJuice

Reputation: 3769

Dynamic Multiple dropdown select options from MySQL using PHP

I'm trying to come up with some sort solution to a problem where I have to provide a user with dynamic dropdowns depending on the options they choose.

Currently I have 3 tables that are normalized as such.

Tables

Currently this works well with my HTML select elements, where if I select John Doe I would get Paul, Kevin and Dick as my second options and if I were to choose Kevin I would get Drake and Kanye as a third option.

My issue is that I do not want to keep creating tables since I would like to add more layers of staff_level in my application.

How would I approach this and have a fully dynamic table structure using PHP and MySQL?

Thank you for taking your time to read this.

Upvotes: 1

Views: 769

Answers (1)

Aaron D
Aaron D

Reputation: 7700

You want an association table between the people. Put all of them in one table with unique IDs like so:

Table Staff
id  | Name        | <Other fields>
----+-------------+----------
1   | John Doe    | 
2   | Sam Smith   | 
3   | John Johns  | 
4   | Paul Pete   | 
5   | Kevin Mayor | 
6   | Dick Ross   | 
      ...

Then the association table named whatever you like - maybe StaffHeirarchy:

Table StaffRelationships
id | ManagerId | SubordinateId
---+-----------+--------------
*  | Null      | 1                       # Has no manager
*  | 2         | 6                       # Dick Ross is subordinate to Sam Smith

This table should have an id field for unique keys, but you don't have to care about what it is (it's not used as a Foreign Key as the Staff.id field is), which is why I put * there - in reality it would be some integer id.

I haven't seen your PHP for pulling values out of the database, but it is basically the same - query the association table filtering for the id of the manager you are looking for and you will get the ids of the subordinates (which you can JOIN on the staff table to get the names).

Upvotes: 1

Related Questions