I have an existing table with a list of forms in it that looks something like this:
| ID | FormName | Category |
|---|---|---|
| 1 | someform | a |
| 2 | otherform | b |
| 3 | yetotherform | c |
| 4 | etc | ... |
(The form table has many more columns, omitted here.)
I would like to make it possible to have some forms be in multiple categories. I have already made a PHP-driven menu using a simple distinct query that generates a menu from the above table based on the category.
SELECT DISTINCT category FROM tablename WHERE enabled = '1'
My first gut reaction was to make the category column into a CSV list of categories like so:
| ID | FormName | Category |
|---|---|---|
| 1 | someform | a,b,c |
| 2 | otherform | b |
| 3 | yetotherform | c |
| 4 | etc | ... |
But then I thought that this would break my menu because the distinct query would no longer work, and it also seems like trying to break up the CVS into something I could then compare against with a WHERE clause when later displaying a list of forms in a category somehow would be very inefficient.
I'm guessing I need some kind of second database table or some other column adding? I'm not sure what that would look like. I tried to do some searching, but I probably don't know the terminology for what I am asking.
The solution needs to:
- Be as unobtrusive of the current schema as reasonably possible.
- Be able to have forms in multiple categories, or only in one.
- Work in Mariadb
- Ideally not break my existing menu query/PHP work.
Thank you.
