php - Search MySQL Database for Bookmarks that are related / contain multiple Tag records -

i want add capability search bookmark mysql records using php , able combine 2-3 tags filter results records contaain tags in search instead of typical search records containing single tag value.

pinboard.in bookmark service allows search/filter bookmarks site-wide , on specific user account 3 tags filtering result.

---

pinboard howto page (https://pinboard.in/howto/#rss) says:

you can filter feeds 3 tags:

http://feeds.pinboard.in/rss/u:username/t:tag1/t:tag2/ http://feeds.pinboard.in/rss/u:username/t:tag1/t:tag2/t:tag3/ http://feeds.pinboard.in/rss/t:tag1/t:tag2/ 

---

i have users db table, tags db table, bookmark table, , tag_to_bookmark relation table

i can tags related bookmark record sql:

select tags.name   tags   join bookmark_tag_relationship on tags.id = bookmark_tag_relationship.tag_id   join bookmarks on bookmarks.id = bookmark_tag_relationship.bookmark_id  bookmarks.id = $bookmarkid 

---

and bookmarks single tag sql:

select bookmarks.name   bookmarks   join bookmark_tag_relationship on bookmarks.id = bookmark_tag_relationship.bookmark_id   join tags on tags.id = bookmark_tag_relationship.tag_id  tags.id = 1 

---

now how can make sql return bookmarks contain 1, 2, or 3 tags?

---

my db structure:

create table bookmark_tag_relationship (   bookmark_id numeric,   tag_id text );  create table bookmarks (   id integer primary key,   user_id numeric,   name text,   description text,   url text,   project_url text,   github_url text,   demo_url text,   local_demo_url text,   image1 text,   image2 text,   image3 text,   image4 text,   notes text,   category text,   tags text,   click_count numeric,   created datetime,   last_viewed datetime,   active numeric );  create table tags (   id integer primary key,   user_id numeric,   name text,   description text,   color text,   bookmark_count numeric,   active numeric );  create table users(   id integer primary key,    numeric,   first_name text,   last_name text,   user_name text,   email text,   password text,   website text,   photo_url text,   bookmark_count numeric,   active numeric ); 

if understood (you want retrieve bookmarks having - i.e. - tags.id=1 , tags.id=2), me query works:

  select bookmarks.id, bookmarks.name, group_concat(tags.name) foundtags     tags     join bookmark_tag_relationship on tags.id = bookmark_tag_relationship.tag_id     join bookmarks on bookmarks.id = bookmark_tag_relationship.bookmark_id    ( tags.id in (1,2) ) group bookmarks.id   having count(distinct tags.id) = 2 

the select / / join same of query tags related bookmark, where condition matches tags id in specified values (‘or’ operator), group by groups results bookmarks.id , - last essential - having filters results 2 tags (without query returns bookmarks having tags.id =1 or tags.id=2).

with simple edit, query can search tag name instead of tag id:

  select bookmarks.id, bookmarks.name, group_concat(tags.name) foundtags     tags     join bookmark_tag_relationship on tags.id = bookmark_tag_relationship.tag_id     join bookmarks on bookmarks.id = bookmark_tag_relationship.bookmark_id    ( tags.name in ('tag1','tag2') ) group bookmarks.id   having count(distinct tags.id) = 2 

to retrieve bookmarks 2, 3 or more tags can use following function, can use whether querytag(1) or querytag(1,2) or querytag(3,5,6) etc...:

function querytags() {     return "             select bookmarks.id, bookmarks.name, group_concat(tags.name) foundtags               tags               join bookmark_tag_relationship on tags.id = bookmark_tag_relationship.tag_id               join bookmarks on bookmarks.id = bookmark_tag_relationship.bookmark_id              ( tags.id in (".implode(',',func_get_args()).") )           group bookmarks.id             having count(distinct tags.id) = ".func_num_args()."     "; } 

i have liked return tags of each retrieved bookmarks (when bookmark has more searched tags)... have not been able!