Categories w/ Custom Fields

I have a menu system set up for unlimited categories and subcategories, in the same database/table called categories.
| catid | catname | catparent | cattype |

What I would like to do is have each category have there own defined fields. So, If I add a new category or subcategory if would ask for the fields for that category.

I'm basically lost at the moment on this and don't know what would be the best way to achieve this.

1. Should I have the script build a new table and put that table name into "catName", then I could do a "select" statement that would grab the table. But this way would have a lot of tables in the database.
2. Should there be a custom table that would hold all these fields types.

~~~~~ Adding Category Walkthru
User adds category then ask how many fields, fields name and type.
If it is a subcategory you can choose to build new fields or use the same fields from it's main category.
~~~~~

I'm just getting started on this, so I have on code to post as examples.

I wouldn't build a table for each category. The second option sounds better. I'd probably set up the category meta table roughly as follows:

id int(x)
catid int(x)
field_name varchar(x)
field_value varchar(x)

You'd have one row per custom field per category and could select these out by catid. To insert, you'd first need to insert the basic category data into your categories table. Then, using lastinsertid, you'd insert a row per custom field into the meta table.

Thanks for the reply, dhouston
I understand now that I could link to that table with using the same catId to retrieve the values of the fields, but what if I needed a check box or an drop down select menu.

I'm just really totally lost of how would these fields hold the data of what type of form I needed for the category.

Basically, I'm trying to setup something like a classified ads would have.
Example:
Products /
~~~~ List all latest products

Products / Music CD's
~~~~ Title, Genre, Label <= these would be forms from mysql, linked from it's catId

Products / Shirts
~~~~ Type, Color, Size <= these would be forms from mysql, linked from it's catId

Sorry if I do not make this clear, I just get so confused.

I suppose you could add a field_type column to the meta table that would contain values like "select" and "radio." Then you'd select all rows from the db where catid satisfies your condition, ordering by field_type and then field_name (or you could add an "order" field to let your users determine the display order). Then you'd loop through the results and, based on the value in field_type, build the necessary form controls. In pseudocode:



Obviously, there's more to it than that, but maybe this'll get you thinking in the right direction.

Yes that helps alot, thanks dhouston
I will work with this and see what I can come up with.