Tuesday, November 25, 2014

How To Perform Multiple Tags Search Query using PHP, MYSQL & CodeIgniter?

I working on a tag based search. I have three tables tag(id,name), tagXmedia(id,tag_id,media_id), and media(id,...).
tagXmedia is the mapping table between the tag and media tables. This is a one to many relationship.
For instance I need to be able to search for an entry in the media table that is associated with both the "home" and "hawaii" tags.
SELECT media_id
FROM tagXmedia
WHERE tag_id IN (SELECT id FROM tag WHERE name IN ('home','hawaii'))
GROUP BY media_id
HAVING COUNT(tag_id) = 2;
If you wish to have it match more than just two tags, you can easily add them. Just remember to change the 2 in the HAVING clause.

Perform Multiple Tags Search using URL Parameters and Codeigniter
Within the query string, parameters (filters) are separated by ampersands (&), and multiple values per parameter are separated by commas. For example:
http://localhost/example/sheet?tags=value1,value2&param2=value

Here is the Codeigniter active-record query for performing multiple tags using comma separated values. Table structure are explained above.

  $this->db->select('tag.name, media.*');
  $this->db->join('tagXmedia', 'media.id = tagXmedia.media_id', 'left');
  $this->db->join('tags', 'tags.id = tagXmedia.tag_id', 'left');

  $tags = explode(',',$tags);
  $a_tag  = array_unique($tags);
  $tag_count = count($a_tag);
     
  $this->db->where_in('tag.name', $a_tag);     
  $count = "COUNT(tagXmedia.tag_id) = $tag_count ";
  $this->db->group_by('media.id');      
  $this->db->having($count);

  $query = $this->db->get('media');

Ref: http://stackoverflow.com/a/8762620

No comments:

Post a Comment