Geek Answers Handbook

Storing an array of SQL strings and subsequent query

I want to save a list of variable size tags for images in an SQL database and then search the database for tag-based images. I am currently pulling out the tag lists from the database and checking to see if the request tag is in the array. Is there a way to format them as sql queries instead of making them in php after I pull the tags?

The list is saved as comma separated values:

"sunny, beach, palm" -im1

"palms, rainy, clouds" -im2

"winter, snow, snowman, shift" -img

I would like to be able to get a combination of request tags: The request "beach, palm" should return im1 and im2

thank

+5
source share
2 answers

You want to avoid delimited lists in your database, as you find that it negates the benefits of using a database to store structured data. Instead, you should create a new table with one tag for each row, and then reference that table using a foreign key from your main image table.

Images Table

  • ID
  • image_name

Tags Table

  • ID
  • tag_name

Image_Tags Table

  • image_id (links the primary key of the image table)
  • tag_id (links primary key of the tag table)

Thus, you can run such requests:

SELECT t.tag_name, i.image_name FROM image_tags it
    INNER JOIN images i on it.image_id = i.id
    INNER JOIN tags t on it.tag_id = t.id
WHERE t.tag_name in ('beach', 'palms')
+10
source

@dbaseman (+1), . , , , - , , , ( JOIN clustering :

:

  • id PK
  • _

image_tag :

  • tag_name PK
  • image_id PK, FK →

image_tag PK : {tag_name, image_id}, (.. ), , :

SELECT DISTINCT image.*
FROM image JOIN image_tag ON (image.id = image_tag.image_id)
WHERE image_tag.tag_name IN ('beach', 'palms')

, , .., @dbaseman , , Image_Tags PK ( , ).

+2

More articles:


All Articles