我应该将其更改为CONCAT吗？

Question

Table gs lists symbols (e.g. flags, national flowers). Each row is linked to a place (e.g. Arizona) by the value IDArea (e.g. us-az). Plants and animals include the values Symbol (= common name; e.g. wood duck) and Latin (= scientific name).

Table gw_geog has additional information about places, such as the full name (e.g. Arizona).

I'm not even sure if I need table gs2_latin or not. It simply helps standardize scientific names for symbols that have multiple common names.

Now imagine a symbol adopted by several states, such as the bison (Wyoming and Kansas). I would like my table to display this:

bison | Kansas, Wyoming

moose | Alaska, Maine

elk | Utah

I think I have several problems with my script. First, place values are being appended from one row to the next. So if the first row displays Maryland, and the second row should display Maine, it instead displays MarylandMaine. The third row displays MarylandMaineDelaware, and the last row displays the names of all fifty states.

I also haven't been able to figure out the right value to group by. I posted another question about this query earlier, and someone suggested MySQL's CONCAT function. However, I haven't figured out exactly how to plug that in. Plus, I accidentally closed the tab on my browser, and I can't even find that discussion now. (However, that discussion fixed my first problem.)

Anyway, here's my script:

$stm = $pdo->prepare("SELECT GG.N, GG.IDArea, GG.URL GURL, GG.Name GName,
GS.N, GS.IDArea SymArea, GS.URL, GS.IDSymbol, GS.Symbol, GS.Latin,
GS.Desig, GS.DesigGen, GS.DesigGroup, G4.Common, G4.Latin
FROM gw_geog AS GG
LEFT JOIN gs AS GS ON GS.IDArea = GG.IDArea
LEFT JOIN gs2_latin AS G4 ON G4.Common = GS.Symbol
WHERE GG.IDParent = 'usa' AND GS.DesigGen = :RefCat
GROUP BY GS.Symbol ORDER BY GS.Symbol");
$stm->execute(array(
'RefCat'=>$RefCat,
));

The code below is inside my while loop...

$PlaceGroup[] = $row['GName'];

// Farther down...

echo '
<tr>
 <td class="Symbol"><a href="$GZ_URL/life/'.$LatinL.'" title="'.$Symbol.'   ('.$Latin.')">'.$row['Symbol'].'</a> (<em>'.$Latin.'</em>)</td>
 <td class="Place"><a href="/world/'.$PlaceL.'" title="'.$Place.'">'.join( $PlaceGroup, ',' ).'</a></td>
</tr>
';

P.S. I may be displaying $PlaceGroup incorrectly, also.

Answer 1

Your output has two columns, so I'm going to simplify the query:

SELECT GS.Symbol, GROUP_CONCAT(GG.Name) as Names
FROM gw_geog GG LEFT JOIN
     gs GS
     ON GS.IDArea = GG.IDArea LEFT JOIN
     gs2_latin G4
     ON G4.Common = GS.Symbol
WHERE GG.IDParent = 'usa' AND GS.DesigGen = :RefCat
GROUP BY GS.Symbol
ORDER BY GS.Symbol;

You can add in additional columns. But, it doesn't make sense to add in columns about the place (without a group_concat()), because you would get an arbitrary value.