CODEWITHSUNDEEP
phpmysqlsql
gyaani_guy
gyaani_guy

Reputation: 3209

removing avoiding duplicates from resultset

I have this query: 

$relevantwords = {"one" , "two" , "three" } ;

foreach ($relevantwords as $word)
     {
        $query .=  "SELECT * FROM delhi_items WHERE heading like '%$word%' AND id!={$entry_row['id']} AND visible=1 UNION ALL "  ; 
     }
$query = implode( " " , explode(" " , $query , -3) ) ;                                                

      $query .= " ORDER BY time_stamp DESC LIMIT 0, 20 " ;
     $result_set = mysql_query($query, $connection);

This causes several duplicates in my resultset. Is there a way to detect and remove these duplicates from the resultset ? I know I should probably try to avoid the duplicates in the first place, but I am unable to figure that out.

Also I tried distinct keyword, it didn't work (because its a loop, the same entry is fetched again and again).

Laslty I am kind of an amateur so please tell me if I am doing something fundamentally uncool with such a long sql query in a for loop.

Thanks

Upvotes: 1

Views: 296

Answers (3)

MarkR
MarkR

Reputation: 63586

This is not the right way to do this query; don't use UNION ALL with several queries.

Just use one query and use an OR between the relevant WHERE clause parts. It'll select each row just once, regardless of how many bits it matches.

Upvotes: 2

Sean Adkinson
Sean Adkinson

Reputation: 8615

Use DISTINCT:

SELECT DISTINCT * FROM ...

UPDATE:

Actually DISTINCT doesn't work since the un-duping would happen before the records are merged with each other in the UNION ALL.

To do what you are trying to do, you want the SELECT DISTINCT * to happen outside the union-ing of all the records.

Do the selecting and union-ing inside a derived table:

SELECT DISTINCT * FROM
    (SELECT * FROM TABLE WHERE ...
    UNION ALL
    SELECT * FROM TABLE WHERE ...
    ) t
ORDER BY ...

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

I would try to have one SELECT and no UNION and DISTINCT. It will probably be a faster query:

$relevantwords = {"one" , "two" , "three" } ;

$querycondition = "" ;
foreach ($relevantwords as $word)
     {
        $querycondition .= " heading LIKE '%$word%' OR"
     }
$querycondition = substr($querycondition ,0 ,strlen($querycondition)-2 ) ;

$query = " SELECT * "
       . " FROM delhi_items "
       . " WHERE ( "
       .           $querycondition 
       . "       ) "
       . "   AND id!={$entry_row['id']} "
       . "   AND visible=1 "
       . " ORDER BY time_stamp DESC "
       . " LIMIT 0, 20 " ; 
$result_set = mysql_query($query, $connection);

Upvotes: 1

Related Questions