CODEWITHSUNDEEP
phpmysqlforeign-keysrelational-database
danishjo
danishjo

Reputation: 159

Add tags to photos mysql

I am implementing tag based search using PHP and MySQL where each photo has multiple tags. I will use this tags to search photos. For example, when I search for a query "building", I want all the photos that have the "building" tag.

Here are my tables:

photos(photo_id, name, url)
tags(tag_id, tag, photo_id)

is this true? or a better idea is appreciated

Upvotes: 1

Views: 210

Answers (2)

ustmaestro
ustmaestro

Reputation: 1283

photos(photo_id, name, url)

tags(tag_id, tag)

tags_photos(tag_id,photo_id)

Search photos by tag example:

Select * From `photos` p 
LEFT JOIN `tags_photos` tp ON p.photo_id = tp.photo_id 
LEFT JOIN `tags` t ON tp.tag_id = t.tag_id WHERE t.tag = 'building';

http://sqlfiddle.com/#!9/47a7e/1

Upvotes: 0

Sachink
Sachink

Reputation: 1530

Normalize up to Third Normal Form:

photos(photo_id, name, url)
tags(tag_id, tag_name, tag_desc)

refe : 

photo_tag_refr(photo_id,tag_id, other_attributes)

Upvotes: 1

Related Questions