How do I get rid of these duplicate rows?

[jeddiki]

Does anyone have any advice about how I
can get these duplicates rows out of my results array ?

If you look here:

Clickbank Super Analyzer

Then just hit the Analyse Clickbank Now button
you will see these all have double entries:

ID: AFFSUPER
ID: COHYPNOSIS
ID: MAVERICK66

I want to just show one of them (the first one)

This is the script that outputs that list:
(this is just one if out of about ten)

if($Db_recurr == "r"){            
       $sql = "SELECT * FROM clickbank WHERE (title LIKE '%$Db_search%' || descrip LIKE '%$Db_search%' )
    AND pop > '$Db_pop_min' AND pop < '$Db_pop_max' AND grav > '$Db_grav_min' AND grav < '$Db_grav_max' 
    AND percent > '$Db_sale_pc_min' AND recurr = 't' AND totearn > '$Db_earn_min' AND cat = '$Dpcat' 
    ORDER BY $Db_sort1 DESC, $Db_sort2 DESC ";
    } // end if
 
$result = mysql_query($sql)
    or die("could not execute SELECT Clickbank". mysql_error());  
$num = mysql_num_rows($result);
 $Rctr = 1;
 while($row = mysql_fetch_assoc($result)){
        extract($row);
    $extr = $totearn-$earn;                
    $disp1 = "<b>$Rctr) $cat</b><br>$title, <b>ID: $id</b><br>";
        $disp2 = "$descrip<br>";
    $disp3 = "<b>$/sale: $earn, +/mth: $rebill, Pop: $pop, Gravity: $grav, Comm%: $comm, Refers: $refer, Total Earn: $totearn</b>";
    echo "<div style=\"width: 800px; text-align: left;\">
        <span>$disp1</span>
    <span>$disp2</span>
    <span>$disp3</span>
    <br><br>
    </div>";
if($Rctr >= $Db_resp)
{ exit();        
} // end if
 $Rctr = $Rctr+1;     
} // end while        

Thanks for any help

[Dormilich]

some possibilities:
1) don’t loop over the result set.
2) limit your sql query (SELECT … LIMIT 1)

[jeddiki]

Thanks,

I am sure you meant well, but did you
take a look at the list that script produces ?
(it is only two clicks away)

I need to loop through the result so that I can
get all of the results, I just do not want to get
the duplicate ones.

Please take a look at the output via the
link, it shows exactly what I mean.

thanks

[Dormilich]

how does it come that the duplicates have different categories then? you may try SELECT DISTINCT* (SELECT DISTINCTROW) but I’m not really sure, whether it works for you.

* - at about one fifth of the page

[jeddiki]

Thanks again for your reply.

how does it come that the duplicates have different categories then?

This happens because clickbank allows product sellers to place their products into TWO categories if they want to. That is why everything is the same except the category field.

If someone just searchs within one category then there will be no duplicates in the output list, but if you search on ALL categories, you get a lot of duplicates.

What I want to do is give an option to exclude duplicates from the listing. In that case just the first occurance will get listed.

Hope that makes sense :-)

I looked at SELECT DISTINCT but I need to focus the distinctivenes on just the ID code because within the row there are many fields that are OK to be duplicated - the category field is an obvious one.

For that reason, I started looking at the output array - hoping that maybe I could use the UNIQUE filter somehow. But I can not get it working.

Any ideas ?

[Dormilich]

but I need to focus the distinctivenes on just the ID code because within the row there are many fields that are OK to be duplicated

then fetch only the ID (there you can use SELECT DISTINCT) and get the entries in another request* (at least that’s what I would do)

* - Prepared Statements come to mind

[MrMancunian]

I am sure you meant well, but did you
take a look at the list that script produces ?
(it is only two clicks away)
...
Please take a look at the output via the
link, it shows exactly what I mean.

Excuse me?! Dormilich makes two suggestions, as per your request and this is the way you answer him? I must say, I'm surprised that he even bothered to answer you after this post, something that earns him credits. I suggest that you show some respect for someone who is answering your post!

Steven

[Dormilich]

let’s put it this way, the description in the post didn’t match the situation exactly enough and I really didn’t use the link.

and I’ve read worse…

[MrMancunian]

So even if you didn't use the link, I still find his reply very rude.

[jeddiki]

Sorry to upset you MrMancunian,

I certainly never meant any disrespect, I very much appreciate the help that Dormilich gives and I always thank for input given. The answer from D did suggest that the output list hadn't been seen, that is all I meant.

-----------------------------------------------------------------------------------------

then fetch only the ID (there you can use SELECT DISTINCT) and get the entries in another request* (at least that’s what I would do)

Not quite sure how to do a doubled-up query !

This is one of my queries:

if($Db_recurr == "n"){            
$sql = "SELECT * FROM clickbank WHERE (title LIKE '%$Db_search%' || descrip LIKE '%$Db_search%' )
AND pop > '$Db_pop_min' AND pop < '$Db_pop_max' AND grav > '$Db_grav_min' AND grav < '$Db_grav_max' 
AND percent > '$Db_sale_pc_min' AND recurr = 'f' AND totearn > '$Db_earn_min' AND cat = '$Dpcat' 
ORDER BY $Db_sort1 DESC, $Db_sort2 DESC ";
} // end if

So how would I fit in:

$sql = "SELECT  DISTINCT id FROM clickbank WHERE ???

Any explanation much appreciated.

[dlite922]

You can also use GROUP BY.

To answer your last post, you used the * (all fields), so I don't know what the fields are in clickbank table, but you can group by the one that's duplicate... would this be "title"? if so,

put GROUP BY title at the end of your query.




Dan