php - MySQL/PHP : consolidate results such as footnotes from multiple tables

Question

我从这个论坛学到了很多东西，提前致谢。基本上，我试图为多个表的数据库查询的结果做“脚注”。我的表格具有几种生物 Material 中每种生物 Material 的“引用书目”，但我无法以更具可读性的方式合并结果。我认为我需要使用多维数组，但我认为必须有一种更优雅的方式。 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“ pretty-print ”部分是:

    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