Select duplicate values in reference table for special treatment

The query below displays lists of national flowers, birds, etc.


SELECT GS.N, GS.IDArea SymArea, GS.URL, GS.PostURL, GS.Unique, GS.Title,
 GS.MyKind2, GS.[B][COLOR="#FF0000"]Symbol[/COLOR][/B], GS.Latin, GS.Desig, GS.DesigGen, GS.DesigGroup, GS.Date, G4.Common,    G4.Latin,
 GG.N, GG.IDArea, GG.Name GName, GG.Type, GG.IDParent, GG.IDParent2, GG.Parent, GG.Parent2, GG.IDReg
 FROM gs AS GS
 LEFT JOIN gs2_latin AS G4 ON G4.Common = GS.Symbol
 LEFT JOIN gw_geog AS GG ON GG.IDArea = GS.IDArea
 WHERE GS.DesigGen = :TaxURL AND GG.Type = 'nat'
 OR GS.DesigGen = :TaxURL AND GG.Type = 'dep'
 GROUP BY GS.Unique ORDER BY GS.N

The most important field value is probably Symbol, which is simply the a list of names of various symbols (e.g. bald eagle). I display it something like this…


while ($row = $stm->fetch())
{
$Symbol = str_replace('_', ' ', $row['Symbol']);
}

<td>'.$Symbol.''.$Star.'</td>

The variable $Star is supposed to display an image next to more popular symbols - those that represent two or more countries. But I’m not sure how to do that.

We’re essentially talking about duplicate values, and I’ve been playing with a variety of schemes but nothing works so far. The idea is to simply display an image after each instance of the word “Rose,” which represents several countries - and to do the same for other symbols that appear in the table more than once.

If I can learn to count (in PHP, that is), then I might even use different colored stars for symbols that represent two countries, three, etc. But I’m having trouble translating the array outputs to plain numbers.

Thanks.

This section seems a little odd to me in that the “table cell” is outside the query loop.

In order to get counts for any “Symbol” you will need the full query result to work with for counting. Here I’m building result set $data and array of just Symbols as $symbols, which you can then get counts for.

$data = array();
$symbols = array();
while ($row = $stm->fetch())
{
//Query Loop builds arrays
	$data[] = $row;
	$symbols[] = $row['Symbol'];
}

//Get counts for each symbol with symbol name as key
$counts = array_count_values($symbols);

//So this would be your display loop
foreach($data as $k => $row){
							 
	$cnt = $counts[$row['Symbol']];
	
	if($cnt > 3){
		$Star = "gold_star.gif";
	}elseif($cnt == 3){
		$Star = "silver_star.gif";
	}elseif($cnt == 2){
		$Star = "bronze_star.gif";
	}elseif($cnt < 2){
		$Star = "";
	}
	
	//All display built in this area using $row['key']
	$Symbol = str_replace('_', ' ', $row['Symbol']);
	echo "<td>'.$Symbol.''.$Star.'</td>";
}

Untested of course.

Thanks, Drummin. I think that’s it; there are a few bugs, but I probably just have to refine my script - it’s pretty messy. :wink: