php dynamic checkboxes

Question

Currently I have a form that submits an image with textfields such as title, description and another field that autoincrements for imageID, another area for the actual file , called vfile, and *** another part that has 3 checkboxes and a text field. Everything works fine, and this is what it does. Submits the data to a database so that it can pull the information to a page on the website. The only part I am trying to update is: The 3 checkboxes and the textfield. Lets say the first checkbox reads: Apples The second : Oranges The Third: Grapes And in the other category is a blank textfield that if you add something, it would add it to a category called "Other".

So the database design has 4 fields: 1 - apples, 2 - oranges, 3 - grapes, 4 - other.

When I click a checkbox, it would add checked to the database under the correct one, either apples, oranges, or grapes. If I add a field to the textbox such as: Bannanas, then it would add "Bannanas" to the database field vother and show that in the database.

This is all fine, but what if the next picture has all 4 items, plus another one? Such as if the next picture had Apples, Oranges, Grapes, Bannanas, and Plums?

How could I have the "Bannanas" other category, change into a checkbox category that could be chosen for the next pics when I go to the add images page next time. So that when I go to the second picture to submit, it would give me the option of not just 3 checkboxes, but 4 checkboxes now, that I could check the first 4, "Apples, Oranges, Grapes, Bannanas" and then put Plums in the other category.

Basically upon submit it takes what is in the other feild and addes a new category to the database, which is then displayed in the array of checkbox choices and it is removed from the Other Category now, for it is a checkbox. (thus it would not want the value left in the old field, for it would keep creating the same category over and rewriting the old data possibly.

Anyway, any suggestions? Thanks in advance.

Answer 1

(It sounds like this is more of a database design question and not a php question, but I may be misunderstanding what it is you are looking for advice on)

It sounds like you are saying that these attributes (Apples, Orange, etc) are stored as columns in your main table; but the situation you are describing sounds more like Tagging. Typically you would maintain a list of things that get tagged (your images), and a separate list of all possible tags (Which would be a table containing the rows : Apple, Orange, Grape). Your UI has the option to select from pre-existing tags (rows in the tag table) or add a new tag using the "Other" box. New tags would be added as a new row to the tag table. Since tags and tagged items have a many-to-many relationship you would create a third table (called a join table) that stores keys of tagged items and keys of tags; that way you can select either side of the relationship easily : get all the tags for a given item; get all the items with a given tag.

Does that help?

(EDIT : for comments)

So, Activities sounds like the list of Tags. If I want to show a form with checkboxes for all the Activities I can query the activities table for them. Each of those checkboxes can have a name attribute or something that captures the ID of the row that its bound to.

Also I would select from the join table the ids of the tags that my currently viewed image has selected. As I am populating the checkbox list I can check this result set to see if the id of the checkbox I'm putting on the page is in the list of tags for the image.

To store this back to the db on submit, the easiest thing is probably to (in a transaction) delete all the entries for the image from the join table and replace them with new entries based on the state of the check boxes in the form.

Answer 2

Drop the apples, oranges and grapes columns.

Create a second table with two fields: imageID and itemtype. Don't make any of the two a key. Now you can list as many different types of items for each image as you need. It will be comparatively expensive to get the list of item types in use from the database but unless you have millions of items this shouldn't be a problem. (Mikeb is suggesting to keep the list of item types in use in a separate table to speed this up.)

The alternative is to dynamically add a column to the first table for each item type you encounter. This will create a very sparse table. I wouldn't do this.

Answer 3

You need to change your database schema to support an infinite number of attributes.

You should move your list of attributes from a set of fields in the record to a list of related records in a new table. This new table requires 2 columns. The first is the primary key from the existing table so you can establish the relationship between the tables. The second is the attribute field ('Bananas' or 'Apples' or 'Plums', etc.) You can have as many records in the attributes table as you like for each record in your main table. You could also have no attribute records if none are checked.

This kind of relationship between two tables is called a one-to-many relationship.