插入并搜索多个关键字

Question

I am planning designing a PHP MySQL database for a local museum's photo gallery.

One thing that terrifies me now is how to implement saving multiple keywords for a single image for items where more than one technic or material was used to design them, e.g. PAINTING TECHNICS:(liner, wiping, scumbling), MATERIAL USED: (oil, canvas,).

Ideally, I would have to use the select option lists to save them for each record. But if I want to save two or more of the words from the same list, I cant.

Do you have any suggestions I might consider?

joseph

Answer 1

You will need to implement a one-to-many relationship between your image table and your keyword table. One image can have many keywords. Each row in your keyword table will need to reference a row in the image table through a foreign key. For example:

images

id | asset
---------------
1  | image1.png 
2  | image2.png

keywords

id | image_id | keyword   | category
-----------------------------------------------
1  | 1        | liner     | painting techniques
2  | 1        | wiping    | painting techniques
3  | 1        | scumbling | painting techniques
3  | 2        | oil       | material used

Or something along those lines. This is how you allow one row (an image) to be associated with many rows (keywords) in a different table.

For each keyword submitted, you would add another row to the keywords table, making sure to reference the associated image.