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:
  • Categories
  • Sub-categories
  • Items

My database table structure is as follows:

Table: Categories
cat_id, cat_name

Table: Sub-categories
subcat_id, subcat_name, cat_id

Table: Items
item_id, item_name

Table: Reference
item_id, subcat_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!