MySql / php system - Tag
I am implementing a tagging system ...
At the moment I have a table tags
with fields name
, description
, createdby
.
And a table images
that has a field tags
. Inside this field, I will put the name of the tags from the table tags
, separated by comma.
But if I want to get all the images with a tag Foo
, how do I form the request? as the field will contain multiple tags separated by comma.
Not sure if you thought about normalizing the image table, but that would be the optimal solution here.
Instead
Structure your table this way
Then you can run this SQL statement to return all images with a specific tag
SELECT * FROM images
WHERE tags = 'forest';
Or if there is a reason you need to keep the table in the current format, you can use
SELECT * FROM images
WHERE tags LIKE '%forest%';
The word "forest" in the examples above is the tag you are looking for
Try something like this:
SELECT * FROM images WHERE tags = 'Foo' OR tags LIKE 'Foo,%' OR tags LIKE '%,Foo' OR tags LIKE '%,Foo,%'
store the tag tags in the field tags
like this: ', 3,17,55,'
when you run a database query, you can use swift constructs like this:
SELECT * FROM images WHERE INSTR(tags,',$tag_id,')>'0'
instead of using "LIKE"% $ tag_id "which is slow