Search returning duplicate results

[HDFilmMaker2112]

Just finished a search script. However, when type two keywords it will return duplicate entries:

 

elseif(isset($_GET['search'])){
$search=$_POST['search'];
$search=str_replace(" ", ".", $search);
header("Location: ./index.php?q=".$search);
}
elseif(isset($_GET['q'])){
$search=$_GET['q'];
$keywords=explode(".",$search);
$sql10000="SELECT product_id FROM $tbl_name2 WHERE keyword LIKE '%" . implode("%' OR keyword LIKE '%",$keywords) . "%'";
$result10000=mysql_query($sql10000);
if(mysql_num_rows($result10000)==0){
$content='<div class="center">Search found no results.</div>';
}
else{
while($row10000=mysql_fetch_array($result10000)){
$product_id3=$row10000['product_id'];
$sql15000="SELECT * FROM $tbl_name WHERE product_id=".$product_id3;
$result15000=mysql_query($sql15000);
while($row15000=mysql_fetch_array($result15000)){
extract($row15000);
$content.=$product_name;
}
}
}
}

 

I have 3 products:

Test2 - microphone

Test3 - audio, microphone

Test123 - audio

 

When you search "audio" you get:

Test3, Test123

 

When you search "microphone" you get:

Test2, Test3

 

When you search "audio microphone" you get:

Test2, Test3, Test3, Test123

with Test3 being a duplicate.

 

Is there anyway to correct this? I tried SELECT DISTINCT * FROM, but there's no difference in the results returned, from what I have now.

[Psycho]

The problem is that you are running two distinct queries, one of which is a loop on the results of the first query. You apparently have multiple results from $tbl_name2 that are associated with the same record in $tbl_name. You should NEVER run queries in loops. In this case you should be doing a JOIN between the two tables along with a GROUP BY to only get the unique values.

 

EDIT: This should get you started

 

$likeValues = "$tbl_name2.keyword LIKE '%" . implode("%' OR $tbl_name2.keyword LIKE '%", $keywords) . "%'"

$query = "SELECT *
          FROM $tbl_name
          JOIN $tbl_name2 USING(product_id)
          WHERE $likeValues
          GROUP BY product_id";

[HDFilmMaker2112]

The problem is that you are running two distinct queries, one of which is a loop on the results of the first query. You apparently have multiple results from $tbl_name2 that are associated with the same record in $tbl_name. You should NEVER run queries in loops. In this case you should be doing a JOIN between the two tables along with a GROUP BY to only get the unique values.

 

EDIT: This should get you started

 

$likeValues = "$tbl_name2.keyword LIKE '%" . implode("%' OR $tbl_name2.keyword LIKE '%", $keywords) . "%'"

$query = "SELECT *
          FROM $tbl_name
          JOIN $tbl_name2 USING(product_id)
          WHERE $likeValues
          GROUP BY product_id";

 

Works perfectly, thanks a ton. Never used JOIN before, definitely helps to see in application that I'm actually working on to understand how it works.