MySQL/PHP:合并来自多个表的结果，如脚注

Question

我从这个论坛中学到了很多，并提前感谢。基本上，我尝试为多个表的数据库查询结果做“脚注”。我的表格有几种生物材料的“参考书目”，但我不能以一种更具可读性的方式来巩固结果。我认为我需要使用多维数组，但我认为一定有一种更优雅的方法。php代码中的MySQL部分是：

    $queryFromAgentBW = "SELECT DISTINCT reports.ID, reports.link, agent_names.ID, agent_names.Name, agent.BW_Actor_List, agent.Common_Name, agent.Reference, actor_list.ID

                         FROM agent_names, agent

                         JOIN actor_list ON(agent.BW_Actor_List = actor_list.ID) 

                         JOIN reports ON(agent.Reference = reports.ID)

                         WHERE  agent_names.ID = agent.Agent_Name AND BW_Actor_List = '".mysql_real_escape_string($a)."'";


 $resultFromAgentBW = mysql_query($queryFromAgentBW);

      //check result;  show error for debugging
      if (!$resultFromAgentBW)
      {
   $message = 'Invalid query:'.mysql_error()."\n";
   $message .= 'Whole query:'.$queryFromAgentBW;
  die($message);
      } 
     while ($rowBW = mysql_fetch_assoc($resultFromAgentBW))  
     {

//  Need to get all this in an array and then print out later so agents 
          are listed only once with  all of the corresponding reference numbers
$bwArray[] = $rowBW;


              }

php“漂亮打印”部分的代码是：

    foreach ($bwArray as $bw) 
    {     
echo "Name: {$bw['Name']}<br />"
. "Ref: {$bw['Reference']}<br />"
. "Link: {$bw['link']}<br /><br />";
     }

现在的结果是：

    Name: Abrin toxin
    Ref: 1
    Link: C:\wamp\www\References\Abrin\AbrinandRicin_Patocka.pdf

    Name: Abrin toxin
    Ref: 6
    Link: C:\wamp\www\References\Abrin\TheEmergencyResponseSafetyandHealthDatabase_           Biotoxin_ ABRIN.pdf

    Name: Adenovirus
    Ref: 9
    Link: C:\wamp\www\References\Adenovirus\Adenovirus (Serotypes 40 & 41)_PHAC .pdf


   Name: Adenovirus
   Ref: 13
   Link: C:\wamp\www\References\Adenovirus\AdenovirusSerotype31InfectioninaNewbornGirlandReviewoftheLiterature.pdf

但理想情况下应该是：

    Abrin Toxin   [1, 6]
    Adenovirus    [9, 13]

其中数字是现在显示为文本的href链接(PDF文档参考)。感谢您的帮助或指导，在这种情况下什么是最好的！

Answer 1

您应该将group_concat函数和group by子句添加到查询中，并使其在mysql中工作。

SELECT group_concat(agent.Reference SEPARATOR ','), agent_names.ID, agent_names.Name

                     FROM agent_names, agent

                     JOIN actor_list ON(agent.BW_Actor_List = actor_list.ID) 

                     JOIN reports ON(agent.Reference = reports.ID)

                     WHERE  agent_names.ID = agent.Agent_Name AND BW_Actor_List =  '".mysql_real_escape_string($a)."'
GROUP BY agent_names.ID

Answer 2

您只需要将结果正确地聚合到一个数组中。将最后两个循环更改为：

while ($rowBW = mysql_fetch_assoc($resultFromAgentBW)) {
    $bwArray[$rowBW['Name']][] = $rowBW;
}
foreach ($bwArray as $name => $refs) {
    echo 'Name: ' . $name . ' [';
    for ($i = 0; $i < count($refs); $i++) {
        echo ($i > 0 ? ' ' : '') . '<a href="' . $ref['link'] . '">' . $ref['Reference'] . '</a>';
    }
    echo ']<br />';
}

为了更好的可读性，我省略了htmlspecialchars()中的数据转义。