I am trying to adapt the Auto-populate multiple select boxes plugin: http://remysharp.com/2007/09/18/auto-populate-multiple-select-boxes/ for my site. I work in PHP/MySQL a fair bit, but am a jQuery novice and JSON total noob.
My goal is to have three auto-populating select boxes:
My database table structure is as follows:
subcat_id, subcat_name, cat_id
So ultimately there may be a given item that is in multiple categories and/or sub-categories, etc.
My main question is how should I pull in these relationships as recordsets through JSON so that I am populating the data correctly on the fly (as nested data sets really)? Of course the first select box will just be populated by the list of category names from Table: Category.
I know the logic for the sub-category (second select box) is:
select subcat_id where cat_id = entered value
And similarly the third select box for Items is:
select item_id where subcat_id = entered value
However, I am not sure how to pull all of the data in to have every possible reference available. In one big array? As separate recordsets that are then somehow nicely nested?
Any ideas or assistance is greatly appreciated... thanks!