php/mysql:将2个或多个外键附加到一个“查询”中？

Question

我对此的解决办法是在最底层。

我的问题是:我试图显示外键数据，但是因为有多个外键，所以我得到了每个外键的“重复”查询。

http://i.imgur.com/Gfqx497.png

正如您所看到的，我可以查询正确的数据，但我不知道如何将其他外键数据附加到相同的“一行输出”。

为了找出我的问题的答案，我已经潜伏了一段时间了，我的智慧已经结束了。我发现了很多线程，比如下面的两个链接，我相信人们都在问同样的问题，但是我似乎无法把我的解决方案用在我的情况下。根据我的理解，我需要为表使用别名，但是我尝试过对解决方案进行多种不同的解释，无法重新创建解决方案。

How do I merge two or more rows based on their foreign key

mysql query 2 foreign keys

--

我有两张桌子(“迷你和能力”)，其中一张有四个外键连接到另一个。

http://i.imgur.com/ctpFHur.png

这是我用于查询的php代码，它主要取自PHPandWebDevelopment4Edition( mysql，Thomson)，我购买它是为了让我开始使用PHP和MySQL。

$query = "SELECT minions.name, minions.summon, minions.attack,
          minions.health, minions.race, minions.rarity, 
          minions.ability1, minions.ability2, minions.ability3, 
          minions.ability4, minions.imagebig, 
          ability.ability 
          AS ability FROM minions 
          INNER JOIN ability on 
          minions.ability1 = ability.abilityid 
          OR minions.ability2=ability.abilityid"; 

            //Only trying for 2 foreign keys to try get it to work



            $result = $db->query($query);
            $num_results = $result->num_rows;
            echo "<p>Number of items found: ".$num_results."</p>";



            for ($i=0; $i <$num_results; $i++){
                $row = $result->fetch_assoc();
                //echo "<p><strong>".($i+1).". Name: ";
                echo "<p><strong>";
                echo htmlspecialchars(stripslashes($row['name']));
                echo "</strong><br />Summoning cost: ";
                echo stripslashes($row['summon']);
                echo "<br />Attack: ";
                echo stripslashes($row['attack']);
                echo "<br />Health: ";
                echo stripslashes($row['health']);
                echo "<br />Race: ";
                echo stripslashes($row['race']);
                echo "<br />Rarity: ";
                echo stripslashes($row['rarity']);                  
                //if (stripslashes($row['ability'] != NULL)){
                    echo "<br />Abilty: ";
                    echo stripslashes($row['ability']); 
                //}
                echo "<br />";
                $imageMinion = stripslashes($row['imagebig']);  

                // $iwidth = 25;
                // $iheight = 100;
                // echo '<img src="img/'.$imageMinion.'.png" style="width:'.$iwidth.'px;height:'.$iheight.'px;">';

                //echo "<br />";
                echo '<img src="img/'.$imageMinion.'.png">';
                echo "</p>";

有人能引导我把这个正确地显示出来吗？我试过遵循其他的解决方案，只是似乎无法得到别名命名的正确，如果我认为这是正确的解决方案。

========EDIT关于verbumSapienti===========的回答

我很难为情，无法找到你的工作答案。这就是代码的样子。

                $query = "SELECT minions.name, minions.summon, minions.attack, minions.health, 
                      minions.race, minions.rarity, minions.ability1, minions.ability2, 
                      minions.ability3, minions.ability4, minions.imagebig, 
                      ability.ability 
                      AS ability 
                      FROM minions 
                      INNER JOIN ability 
                      ON minions.ability1 = ability.abilityid 
                      OR minions.ability2 = ability.abilityid 
                      OR minions.ability3 = ability.abilityid 
                      OR minions.ability4 = ability.abilityid"; 

            $result = $db->query($query);
            $num_results = $result->num_rows;
            echo "<p>Number of items found: ".$num_results."</p>";

            for ($i=0; $i <$num_results; $i++){
                $row = $result->fetch_assoc();

                $abilities = array('ability1', 'ability2', 'ability3', 'ability4');
                foreach($abilities as $ability)
                {
                    $q = "SELECT $ability FROM minions WHERE name={$row['name']}";
                    $result = $db->query($q);
                    $row2 = $result->fetch_assoc();
                    $abilitiesArr[] = $row2[$ability];
                }

                echo "<p><strong>";
                echo htmlspecialchars(stripslashes($row['name']));
                echo "</strong><br />Summoning cost: ";
                echo stripslashes($row['summon']);
                echo "<br />Attack: ";
                echo stripslashes($row['attack']);
                echo "<br />Health: ";
                echo stripslashes($row['health']);
                echo "<br />Race: ";
                echo stripslashes($row['race']);
                echo "<br />Rarity: ";
                echo stripslashes($row['rarity']);      

                foreach($abilitiesArr as $ability)
                    {
                        $q = "SELECT $ability FROM ability";
                        $result = $db->query($q);
                        $row = $result->fetch_assoc();
                        echo "<br />Ability: $row";
                    }  


                /*if (stripslashes($row['ability'] != NULL)){
                    echo "<br />Abilty: ";
                    echo stripslashes($row['ability']); 
                }*/

                echo "<br />";
                $imageMinion = stripslashes($row['imagebig']);          
                echo '<img src="img/'.$imageMinion.'.png">';                    
                echo "</p>";
            }

我试着改变了一些事情，但没有取得任何成功。如前所述，我得到了以下错误：Fatal error: Call to a member function fetch_assoc() on a non-object in D:\Xampp\htdocs\ocduels\results.php on line 87

它是：$row2 = $result->fetch_assoc();

在：

                    $abilities = array('ability1', 'ability2', 'ability3', 'ability4');
                foreach($abilities as $ability)
                {
                    $q = "SELECT $ability FROM minions WHERE name={$row['name']}";
                    $result = $db->query($q);
                    $row2 = $result->fetch_assoc();
                    $abilitiesArr[] = $row2[$ability];
                }

：：我对此的解决方案：：--这似乎有效。我不认为它有效率，但它足以让我继续学习。谢谢你的回复。这允许我找到一个'Minion‘，只有一个实例的'Minion’，当有超过一个外键与数据。

                $query =   "SELECT 
                        m.name as m_name, 
                        m.summon as m_summon, 
                        m.attack as m_attack,
                        m.health as m_health,
                        m.race as m_race,
                        m.rarity as m_rarity,
                        m.ability1 as m_ability1, 
                        m.ability2 as m_ability2, 
                        aa.ability as a_ability, 
                        ab.ability as b_ability,
                        m.imagebig as m_imagebig
                        FROM minions m
                        LEFT JOIN ability aa 
                        ON m.ability1 = aa.abilityid
                        LEFT JOIN ability ab
                        ON m.ability2 = ab.abilityid";                        

            $result = $db->query($query);
            $num_results = $result->num_rows;
            echo "<p>Number of items found: ".$num_results."</p>";

            for ($i=0; $i <$num_results; $i++){
                $row = $result->fetch_assoc();

                echo "<p><strong>";
                echo htmlspecialchars(stripslashes($row['m_name']));
                echo "</strong><br />Summoning cost: ";
                echo stripslashes($row['m_summon']);
                echo "<br />Attack: ";
                echo stripslashes($row['m_attack']);
                echo "<br />Health: ";
                echo stripslashes($row['m_health']);
                echo "<br />Race: ";
                echo stripslashes($row['m_race']);
                echo "<br />Rarity: ";
                echo stripslashes($row['m_rarity']);        

                if (stripslashes($row['a_ability'] != NULL)){
                    echo "<br />Ability 1: ";
                    echo stripslashes($row['a_ability']);
                }

                if (stripslashes($row['b_ability'] != NULL)){
                    echo "<br />Ability 2: ";
                    echo stripslashes($row['b_ability']);                                   
                }

                echo "<br />";
                $imageMinion = stripslashes($row['m_imagebig']);        
                echo '<img src="img/'.$imageMinion.'.png">';                    
                echo "</p>";
            } 

Answer 1

尝试DISTINCT关键字来限制重复的值。

SELECT DISTINCT minions.name, minions.summon, minions.attack,
          minions.health, minions.race, minions.rarity, 
          minions.ability1, minions.ability2, minions.ability3, 
          minions.ability4, minions.imagebig, 
          ability.ability 
          AS ability FROM minions 
          INNER JOIN ability on 
          minions.ability1 = ability.abilityid 
          OR minions.ability2=ability.abilityid";

Answer 2

您可以尝试一个子查询，该子查询只输出包含在每个仆从属性中的每个能力ID的能力文本，可能类似于以下内容：

$abilities = array('ability1', 'ability2', 'ability3', 'ability4');
foreach($abilities as $ability)
{
    $q = "SELECT $ability FROM minions WHERE name={$row['name']}";
    $result = $db->query($q);
    $row2 = $result->fetch_assoc()
    $abilitiesArr[] = $row2[$ability];
}

然后替换

echo "<br />Abilty: ";
echo stripslashes($row['ability']);

使用

foreach($abilitiesArr as $ability)
{
    $q = "SELECT $ability FROM ability";
    $result = $db->query($q);
    $row = $result->fetch_assoc()
    echo "<br />Ability: $row";
}