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

I've learned a lot from this forum, and thanks in advance. Basically, I'm trying to do "footnotes" for results from a database query of multiple tables. My table has the "bibliographic reference" for each of several bio materials, but I'm not able to consolidate the results in a way that is more readable. I think I need to use multidimensional arrays, but I think there must be a more elegant way. MySQL portion in php code is:

    $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);
      } 
  开发者_JAVA百科   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;


              }

And the php "pretty printing" portion of code is:

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

The result is now:

    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

but ideally it would be:

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

where the numbers are href links shown as text now (PDF document reference). Thanks for any help or guidance on what would be best in this case!

You should add group_concat function and group by clause to your query and make all work in 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

You just need to properly aggregate your results in an array. Change last two loops to:

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 />';
}

I omitted data escaping with htmlspecialchars() for better readability.