Jump to content

Search returning duplicate results


Recommended Posts

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.

Link to comment
Share on other sites

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";

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.