perform operation on SQL results with same reference

Question

I'm building a kinda search engine thing.

My query breaks apart a query string and then SELECTs for each word from the string all of the reference ids associated with that word.

I have a method of giving a relevance score to each id for each query word. However I am looking for a way of keeping track if my query returns the id multiple times. Could anyone point me in the right directions. I am using PHP and mySQL??

Answer 1

Well, you could use PHP or SQL to achieve this:

a code example (no error checking, assuming you're using mysql api directly - which you shouldn't):

<?php
$sql = 'SELECT ...';
$result = mysql_query($sql);
$ids = array();
while ($row = mysql_fetch_object($result)) {
    $id = $row->id;
    if (array_key_exists($id, $ids)) {
        print 'id '. $id . ' exists';
        continue;
    }
    $ids[$id] = true;
}
?>

using SQL you could try a HAVING statement, like so:

SELECT * FROM whatevs HAVING COUNT(id) > 1

Since I don't know your table layout it's hard to write actually working code/SQL.