Inserting things in GROUP_CONCAT

I’m not sure if this belongs in the PHP or MySQL forum, but I’ll try here first.

Here’s a working query:


$stm = $pdo->prepare("SELECT PLAN.Latin Latin2, PLAN.Common, PLAN.Rank, PLAN.GroupTax, PLAN.Desig, PLAN.Family, PLAN.Order1, GS.Symbol, GS.Latin, GG.URL AS GURL, GG.Name AS Name, GROUP_CONCAT(GG.Name ORDER BY GG.Name ASC SEPARATOR ', ') AS Names, GG.IDParent
FROM gs_planimals PLAN
LEFT JOIN gs GS ON GS.Latin = PLAN.Latin
LEFT JOIN gw_geog GG ON GG.IDArea = GS.IDArea
WHERE PLAN.Desig = :RefCat AND Rank != '55'
GROUP BY PLAN.Common
ORDER BY PLAN.N, GG.Name");
 $stm->execute(array(
 'RefCat'=>$RefCat,
 ));

I want to modify it so that the place names it displays are hyperlinked. Here’s my code:


SELECT PLAN.Latin Latin2, PLAN.Common, PLAN.Rank, PLAN.GroupTax, PLAN.Desig, PLAN.Family, PLAN.Order1, GS.Symbol, GS.Latin, GG.URL AS GURL, GROUP_CONCAT(CONCAT('<a href="', GG.URL, '" title="', GG.Name, '">', GG.Name, '</a>')) as Names, GG.Name, '</a>')) as LISTOFLINKS, GG.IDParent
FROM gs_planimals PLAN
LEFT JOIN gs GS ON GS.Latin = PLAN.Latin
LEFT JOIN gw_geog GG ON GG.IDArea = GS.IDArea
WHERE PLAN.Desig = :RefCat AND Rank != '55'
GROUP BY PLAN.Common
ORDER BY PLAN.N, GG.Name");
 $stm->execute(array(
 'RefCat'=>$RefCat,
 ));

But I get this error message: Parse error: syntax error, unexpected ‘’, GG.URL, ‘’ (T_CONSTANT_ENCAPSED_STRING)

I’ve been playing with the quotes and commas, but I can’t figure out the magic combination. Can anyone see my mistake? Thanks.

P.S. I put back slashes in front of each of the four double quotes, and that seems to fix the original syntax error. But it then displays another syntax error. The problem is apparently somewhere in here…


')) as LISTOFLINKS

I’ve been playing with the quotes and commas, but I wondered if there’s a better way of writing it to begin with.

maybe writing SQL with indentation clears it up:

$sql = <<<SQL
SELECT 
    PLAN.Latin Latin2, 
    PLAN.Common, 
    PLAN.Rank, 
    PLAN.GroupTax, 
    PLAN.Desig, 
    PLAN.Family, 
    PLAN.Order1, 
    GS.Symbol, 
    GS.Latin, 
    GG.URL AS GURL, 
    GROUP_CONCAT(
        CONCAT('<a href="', GG.URL, '" title="', GG.Name, '">', GG.Name, '</a>')
    ) as Names, 
    GG.Name, 
    '</a>')) as LISTOFLINKS, 
    GG.IDParent
FROM 
    gs_planimals PLAN
LEFT JOIN 
    gs GS 
    ON 
        GS.Latin = PLAN.Latin
LEFT JOIN 
    gw_geog GG 
    ON 
        GG.IDArea = GS.IDArea
WHERE 
        PLAN.Desig = :RefCat 
    AND Rank != '55'
GROUP BY 
    PLAN.Common
ORDER BY 
    PLAN.N, GG.Name
SQL;

PS. in HEREDOC you don’t need to escape quotes

You should write your query out neater. That way, you’d be able to find your errors a lot easier:


SELECT PLAN.Latin Latin2
     , PLAN.Common
     , PLAN.Rank
     , PLAN.GroupTax
     , PLAN.Desig
     , PLAN.Family
     , PLAN.Order1
     , GS.Symbol
     , GS.Latin
     , GG.URL AS GURL
     , GROUP_CONCAT(
              CONCAT('<a href=\\"'
                   , GG.URL
                   , '\\" title=\\"'
                   , GG.Name
                   , '\\">'
                   , GG.Name
                   , '</a>'
                   )
      ) as Names
     , GG.Name
     , '</a>')) as LISTOFLINKS
     , GG.IDParent
FROM gs_planimals PLAN
LEFT JOIN gs GS ON GS.Latin = PLAN.Latin
LEFT JOIN gw_geog GG ON GG.IDArea = GS.IDArea
WHERE PLAN.Desig = :RefCat AND Rank != '55'
GROUP BY PLAN.Common
ORDER BY PLAN.N, GG.Name");
 $stm->execute(array(
 'RefCat'=>$RefCat,
 ));

As you can see from the rewritten version of your query, the erroneous line is with the following:


 , '</a>')) as LISTOFLINKS

Where you have two closing parentheses that aren’t necessary.

EDIT: It took me so long to cleanup the query on my phone Dormilich beat me to it :stuck_out_tongue:

Thanks!