MySql/php - Tag System

Question

I'm implementing a Tag system...

Currently i have the table tags with name, description, createdby fields.

And the table images that have a field tags. Inside this field i will put the tags name from the table tags, separated by comma.

But, if i want to get all images with tag Foo how i do form the query? since the field will contain multiple tags separated by comma.

I did not tried anything for now, since i do not really know how to from the query. — Fr0z3n
– Fr0z3n, Commented Jan 20, 2013 at 22:56
I would normalize the data. Create a table that contains a separate record for each tag for each image. This would make your query (and life in general) much simpler IMHO. — Tom
– Tom, Commented Jan 20, 2013 at 22:58
@Tom and in terms of database usage and speed it's better to use your method over mine? — Fr0z3n
– Fr0z3n, Commented Jan 20, 2013 at 23:00
Yes if you have a table with a single tag per record, you could index the tag field and it would be much faster to filter on. A field with multiple values in it will be much slower to query as you cannot use an index. — Tom
– Tom, Commented Jan 20, 2013 at 23:04

Lloyd Banks · Accepted Answer · 2013-01-21 00:26:10Z

3

Not sure if you have thought about normalizing the images table, but that would be the optimal solution here.

Instead of having

enter image description here

Structure your table this way

enter image description here

Then you can run this SQL statement to return all images with a certain tag

SELECT * FROM images
WHERE tags = 'forest';

Or if there is a reason you need to keep your table in the current format, you can use

SELECT * FROM images
WHERE tags LIKE '%forest%';

The word 'forest' in the above 2 examples is the tag you are searching for

answered Jan 21, 2013 at 0:26

Lloyd Banks

36.9k58 gold badges172 silver badges264 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

inhan Over a year ago

I would go further and create a urls table with id and url columns, and a tags table with url_id and tag columns.

Fr0z3n Over a year ago

Doing so, i need to rewrite all function to get/insert images from the database. So instead i did as Tom said, one table just for record images id and tags id, and join them in a query.

berty · Accepted Answer · 2013-01-20 22:56:02Z

2

Try something like that :

SELECT * FROM images WHERE tags = 'Foo' OR tags LIKE 'Foo,%' OR tags LIKE '%,Foo' OR tags LIKE '%,Foo,%'

answered Jan 20, 2013 at 22:56

berty

2,20612 silver badges20 bronze badges

Comments

dimaninc · Accepted Answer · 2013-01-20 23:03:38Z

0

store tag ids in tags field like this: ',3,17,55,'

when you will exec a query to database, you will be able to use fast constructions like this:

SELECT * FROM images WHERE INSTR(tags,',$tag_id,')>'0'

instead of using "LIKE '%$tag_id'" which are slow

answered Jan 20, 2013 at 23:03

dimaninc

8507 silver badges17 bronze badges

3 Comments

Zar Over a year ago

Storing tags separated by commas aren't a great idea, really.

dimaninc Over a year ago

i agree, but topic-starter is going to do this way

Zar Over a year ago

True. In addition to your answer, it might be a good idea to explain why this is bad and guide him to the better path.

Collectives™ on Stack Overflow

MySql/php - Tag System

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related