In a perfect world Dynamic Dependent Dropdown Menus are really easy, and generally for me quite reliant on the database structure. When I create a site and i know before starting that it might use geographic entities I might have a database structure like this.
|id||country||continent foreign key id|
|id||city||continent foreign key id||country foreign key id|
So thats a perfect world example.
And here is a real world example.
I start off a simple website that is for a single hotel in Washington DC, I complete the website and all are happy. On the website there is an enquiry form that takes user details.
√ Sign up to our Newsletter
The users complete the form and 2 things happen, an email gets sent to the hotel, and an entry gets stored in the database, this entry has some added info like a datetime lets say.
So far all is looking quite straight forward, but 3 years later the client asks for a backend system so they can see all these enquiries as well as many other things. They want to create a mailing list to communicate with all these users.
Now my SQL table looks something like this
You get the idea, for the mailing list they want to filter all this data by Status then Source then Country
So they want 3 select menus to be dynamically filled with all this data
serverconnect1 - I make a simple query to get all records for display in a table
I then bind serverconnect1 to the status select menu with value and title set to status
I then bind serverconnect1 to the source select menu with value and title set to source
I then bind serverconnect1 to the countries select menu with value and title set to countries
My selects are now dynamic and dependant on what is selected but there are 100 duplicate entries for each record, and after trying multiple things, such as data formatters of unique I can not get it sorted out.
So I add other queries to only get distinct values and multiple conditions to only show dependant on the first selection, but each time a select is used it updates the others and they revert to the first value.
serverconnect2 - I query the same table but only the status field to get distinct values
serverconnect3 - I query the same table but only the source field to get distinct values
serverconnect4 - I query the same table but only the countries field to get distinct values
I must be just overthinking this, anyone got some ideas for me how to best do this, would be great to keep just the one query solution but get rid of all the duplication.
Sorry for the long post and explanation.