tag searching with many to many fields in php

Question

i'm trying to do a search engine for a briefing system.

I'm using a many to many relationship style setup with 3 databases representing the relationships.

comms_briefings is where the data is stored for the briefings search_tags is where the tags are stored briefings_tags is where the relationship is formed

i have this code already which ALMOST perfect however, for some reason it returns the same row in the database multiple times if a tag appears more than once in the briefing.

eg if user searchs for

$ "complx enquiries"

the system will return any row that has the tag complx and enquiries twice as there are two matching tags. I want the results to be ordered desc with the row that gets the most results first and only to appear once.

any ideas? :)

Answer 1

Something like this should do:

function searchTags($tags) {

    $tags = explode(' ', $tags);
    $tagIds = array();

    foreach($tags as $key) {
     $sql = mysql_query("SELECT `id` FROM `search_tags` WHERE `tag` = '".$key."'");
     while($r=mysql_fetch_object($sql))
      $tagIds[] = $r->id;
    }

    $query = mysql_query("SELECT comms_briefings.*, COUNT(*) AS num
     FROM comms_briefings, briefings_tags
     WHERE briefings_tags.tag_id IN ('" . implode("','", $tagIds) ."') AND briefings_tags.briefing_id = comms_briefings.id
            GROUP BY comms_briefines.id
     ORDER BY num DESC, `created` DESC"); 

    while($r = mysql_fetch_object($query)) {

     $summation = preg_replace('^(.*)\n(.*)^', '$1\\n$2', $r->summation);
     $summation = preg_replace('/[\r]+/', '', $summation);

     echo '<li class="message unread" id="msg-'.$r->id.'">';
     echo '<h3><a href="">'.$r->title.'</a></h3>';
     echo '<ul class="details">
       <li><span>Importance:</span> '.$r->importance.'</li>
       <li><span>Date:</span> '.$r->created.'</li>
       <li><span>Summary:</span> '.$summation.'</li>
       </ul>
       <div class="clr"></div>
       </li>';
    }
}

All the tagIds are pulled into an array first, then you do a single query on the briefings, grouping by id.