mysql - 如何将数组列表分配给特定数据？

Question

我想从表 author_settings 的数据库列 setting_name 中列出隶属关系，不重复。这是我的数据库:

author_id | setting_name | setting_value
     1       affiliation   King College
     1       biography     Oncology Department
     2       affiliation   Queen Hospital
     2       biography     National Research Center
     3       affiliation   King College

然后，在列表之后，我想根据表authors中的author_id来设置它:

author_id | author_name 
   1           Mary
   2           Hasan
   3           Dexter

首先，我希望所有隶属关系列表如下所示:

<affID> 1 </affID>
<affName> King College </affName>

<affID> 2 </affID>
<affName> Queen Hospital </affName>

<affID> 3 </affID>
<affName> National Research Center </affName>

然后对于作者来说，隶属关系将根据作者分配，如下所示:

<authorID> 1 </authorID>
<name> Mary </name>
<affilitationID> 1 </affiliationID>

 <authorID> 2 </authorID>
    <name> Hasan </name>
    <affilitationID> 2 </affiliationID>

<authorID> 3 </authorID>
<name> Dexter </name>
<affiliationID> 1 </affiliationID>

这是我列出隶属关系的代码:

$affl=mysqli_query($con,"SELECT DISTINCT author_settings.setting_value, author_settings.setting_name,
                         authors.article_id
                         FROM author_settings
                         INNER JOIN authors ON authors.author_id=author_settings.author_id
                         HAVING author_settings.setting_name='affiliation'")
                         or die(mysql_error());

$affid=1;
while($affl2=mysqli_fetch_array($affl))
{
    $xml.='<affID>'.$affid.'</affID>';
    $xml.='<affName>'.affl2['setting_value'].'</affName>

    $affid++;
}
echo $xml;

我尝试使用SELECT DISTINCT 列出不重复的从属关系，但对于第二部分，我不知道如何将其相应地分配给作者。请帮忙。谢谢:)

Answer 1

解决此问题的一种方法是创建一个临时表，其中包含从属关系值及其索引。为了实现这一点，查询将是:

DROP TABLE IF EXISTS affiliations;

CREATE temporary TABLE affiliations (
  aff_id int(11) NOT NULL AUTO_INCREMENT,
  aff_name VARCHAR(45),
  PRIMARY KEY (aff_id) 
) ;

INSERT INTO affiliations (aff_name) SELECT DISTINCT setting_value FROM author_settings WHERE setting_name='affiliation';

-- Provide results for the list of affiliations
SELECT * FROM affiliations;

-- Provide results for the list of author, with affiliation index
SELECT auth_a.author_id, auth_a.author_name, aff_id
FROM authors AS auth_a JOIN author_settings AS auth_s ON auth_a.author_id = auth_s.author_id AND auth_s.setting_name = 'affiliation' 
JOIN affiliations AS aff ON auth_s.setting_value = aff.aff_name;