php - MySQL + PHP : Avoid duplicate result due to two results from joined table

Question

我一直在努力解决以下情况:

第一种方法:

SELECT * FROM vinyl_tracks vt
INNER JOIN vinyl_keys vk ON vt.mKeyID = vk.mKeyID
INNER JOIN vinyl_tracks_styles vts ON vt.trackID = vts.trackID
INNER JOIN vinyl_styles vs ON vts.styleID = vs.styleID
WHERE vinylID = :vinylID

对于某些轨道，表vts将包含两种或多种样式(该表仅包含位于不同行的trackID和styleID 。这会导致整个结果重复。我尝试过 DISTINCT 和各种 GROUP BY，包括 DISTINCT 和 GROUP BY。这确实会为每个轨道返回一个结果，但缺少第二个 styleID。

这是此查询的 var_dump，没有任何 GROUP BY:

array (size=4)
0 => 
  array (size=10)
    'trackID' => string '868' (length=3)
    'vinylID' => string '249' (length=3)
    'side' => string 'A' (length=1)
    'trackArtist' => string '' (length=0)
    'trackTitle' => string 'Waiting (John Creamer & Stephane K Remix)' (length=41)
    'notes' => string '130 bpm' (length=7)
    'mKeyID' => string '1' (length=1)
    'mKey' => string 'A major' (length=7)
    'Camelot' => string '11B' (length=3)
    'styleID' => string '8' (length=1)
1 => 
  array (size=10)
    'trackID' => string '868' (length=3)
    'vinylID' => string '249' (length=3)
    'side' => string 'A' (length=1)
    'trackArtist' => string '' (length=0)
    'trackTitle' => string 'Waiting (John Creamer & Stephane K Remix)' (length=41)
    'notes' => string '130 bpm' (length=7)
    'mKeyID' => string '1' (length=1)
    'mKey' => string 'A major' (length=7)
    'Camelot' => string '11B' (length=3)
    'styleID' => string '17' (length=2)
2 => 
  array (size=10)
    'trackID' => string '869' (length=3)
    'vinylID' => string '249' (length=3)
    'side' => string 'B' (length=1)
    'trackArtist' => string '' (length=0)
    'trackTitle' => string 'Waiting (Jay Welsh (Black Ice) Remix)' (length=37)
    'notes' => string '135 bpm' (length=7)
    'mKeyID' => string '1' (length=1)
    'mKey' => string 'A major' (length=7)
    'Camelot' => string '11B' (length=3)
    'styleID' => string '17' (length=2)
3 => 
  array (size=10)
    'trackID' => string '869' (length=3)
    'vinylID' => string '249' (length=3)
    'side' => string 'B' (length=1)
    'trackArtist' => string '' (length=0)
    'trackTitle' => string 'Waiting (Jay Welsh (Black Ice) Remix)' (length=37)
    'notes' => string '135 bpm' (length=7)
    'mKeyID' => string '1' (length=1)
    'mKey' => string 'A major' (length=7)
    'Camelot' => string '11B' (length=3)
    'styleID' => string '8' (length=1)

然后我运行另一个查询来获取所有样式，以便我可以填充多个选择下拉列表。这就是使用 foreach 循环创建下拉列表和屏幕截图的样子 - 两个轨道，每个轨道都有两种样式，显示为四个轨道:

$allStylesQuery = $db->prepare("SELECT * FROM vinyl_styles ORDER BY styleID ASC");
$allStylesQuery->execute();
$styles = $allStylesQuery->fetchAll(PDO::FETCH_ASSOC);
$stylesSelector = "<select multiple=\"multiple\" class=\"form-control form-control-sm stylesSelector\" name=\"styleID[".$trow['trackID']."][]\"><option> &ndash; </option>";
foreach($styles as $style) {
    if(isset($trow['styleID']) && ($trow['styleID'] == $style['styleID'])) {
        $stylesSelector .= "<option selected=\"selected\" value=\"".$style['styleID']."\">".$style['styleName']."</option>";
    }
    else {
        $stylesSelector .= "<option value=\"".$style['styleID']."\">".$style['styleName']."</option>";
    }
}
$stylesSelector .= "</select>";

替代方法:我还尝试使用两个查询来完成相同的任务:

SELECT * FROM vinyl_tracks vt
INNER JOIN vinyl_keys vk ON vt.mKeyID = vk.mKeyID
WHERE vinylID = :vinylID

然后，在 foreach 循环内获取第一个查询的结果，以下查询:

SELECT * FROM vinyl_tracks_styles vts
RIGHT JOIN vinyl_styles vs ON vts.styleID = vs.styleID
WHERE vts.trackID = :trackID

这种替代方法可以产生我想要的结果，例如:

array (size=2)
  0 => 
    array (size=3)
    'trackID' => string '868' (length=3)
    'styleID' => string '8' (length=1)
    'styleName' => string 'progressive house' (length=17)
 1 => 
   array (size=3)
    'trackID' => string '868' (length=3)
    'styleID' => string '17' (length=2)
    'styleName' => string 'progressive trance' (length=18)

但是我无法按照我需要的方式显示结果，即:

为了在选择下拉列表中列出所有可能的样式，我需要再运行一个查询(上面的 WHERE 条件会阻止显示所有样式名称和 ID，尽管 RIGHT JOIN，我知道):

SELECT * FROM vinyl_styles ORDER BY styleID ASC

突出显示我发现的选择选项(样式)的唯一方法是将两个 foreach 循环嵌套在另一个循环中，这当然会再次导致重复的结果:

$trackStylesQuery = $db->prepare("SELECT * FROM vinyl_tracks_styles vts
                        RIGHT JOIN vinyl_styles vs ON vts.styleID = vs.styleID
                        WHERE vts.trackID = :trackID");
$trackStylesQuery->bindParam(':trackID', $trow['trackID'], PDO::PARAM_INT);
$trackStylesQuery->execute();
$trackStyles = $trackStylesQuery->fetchAll(PDO::FETCH_ASSOC);
//var_dump($trackStyles);

$allStylesQuery = $db->prepare("SELECT * FROM vinyl_styles ORDER BY styleID ASC");
$allStylesQuery->execute();
$styles = $allStylesQuery->fetchAll(PDO::FETCH_ASSOC);
$stylesSelector = "<select multiple=\"multiple\" class=\"form-control form-control-sm stylesSelector\" name=\"styleID[".$trow['trackID']."][]\"><option> &ndash; </option>";
foreach($styles as $style) {
    foreach($trackStyles as $trackStyle) {
        if(isset($trackStyle['styleID']) && ($trackStyle['styleID'] == $style['styleID'])) {
            $stylesSelector .= "<option selected=\"selected\" value=\"".$style['styleID']."\">".$style['styleName']."</option>";
        }
        else {
            $stylesSelector .= "<option value=\"".$style['styleID']."\">".$style['styleName']."</option>";
        }
    }
}
$stylesSelector .= "</select>";

如果我坚持上面的第一个单查询变体，整行输入将重复两次，因此对于这两个轨道，我得到四行。

我真的没有主意，请帮忙。

更新

采用@Ultimater建议的更少查询方法，我现在已经拥有了所需的所有详细信息，唯一的问题是样式列表在样式下拉列表中为跟踪的每种样式重复有。如果轨道仅添加一种样式或未添加任何样式，则没有问题。下面发布我的完整代码:

$tracksQuery = $db->prepare("SELECT vt.trackID, vt.vinylID, vt.side, vt.trackArtist, vt.trackTitle, vt.notes, vt.mKeyID, vk.mKey, vk.Camelot, 
                            GROUP_CONCAT(vts.styleID SEPARATOR ',') AS 'styleIDs',
                            GROUP_CONCAT(vs.styleName SEPARATOR ',') AS 'styleNames'
                            FROM vinyl_tracks vt
                            INNER JOIN vinyl_keys vk ON vt.mKeyID = vk.mKeyID
                            LEFT JOIN vinyl_tracks_styles vts ON vt.trackID = vts.trackID
                            LEFT JOIN vinyl_styles vs ON vts.styleID = vs.styleID
                            WHERE vt.vinylID = :vinylID
                            GROUP BY vt.trackID");
$tracksQuery->bindParam(':vinylID', $vinylID);
$tracksQuery->execute();
$tracks = $tracksQuery->fetchAll(\PDO::FETCH_ASSOC);
//var_dump($tracks);

// fetch all styles to use in the dropdown
$allStylesQuery = $db->prepare("SELECT * FROM vinyl_styles ORDER BY styleID ASC");
$allStylesQuery->execute();
$styles = $allStylesQuery->fetchAll(PDO::FETCH_ASSOC);

// build a dropdown menu for each of the tracks
// which contains all styles and where the current track's styles are selected
$stylesSelector = "";

foreach($tracks as $track) {

    // start building the selector
    $stylesSelector .= "<select multiple=\"multiple\" class=\"form-control form-control-sm\" name=\"styleID[".$track['trackID']."][]\"><option> &ndash; </option>";

            // check if any styles have been added for this track
    if(!empty($track['styleIDs'])) {

        // check if StyleIDs and StyleNames results contain a comma
        // which means more than one style has been added for that track
        if((strpos($track['styleIDs'], ',')) && (strpos($track['styleNames'], ','))) {

            // separate trackStyleIDs and trackStyleNames
            $trackStyleIDs = explode(',', $track['styleIDs']);
            $trackStyleNames = explode(',', $track['styleNames']);

            // now combine them in one array where styleID is the key and styleName is the value
            $styleIDs_and_Names = array_combine($trackStyleIDs, $trackStyleNames);

            // for each of the styleID => styleName pairs in the array
            // check against all available styles if selected
            foreach($styleIDs_and_Names as $styleID => $styleName) {

                // iterate over all available styles
                foreach($styles as $style) {

                    // if there's a match, add "selected" to the option
                    if($styleID == $style['styleID']) {
                        $stylesSelector .= "<option selected=\"selected\" value=\"".$style['styleID']."\">".$style['styleName']."</option>";
                    }
                    else {
                        $stylesSelector .= "<option value=\"".$style['styleID']."\">".$style['styleName']."</option>";
                    }
                }
            }
        }
        // only one style has been added for this track
        else {
            // iterate over all available styles
            foreach($styles as $style) {

                // if there's a match, add "selected" to the option
                if($track['styleIDs'] == $style['styleID']) {
                    $stylesSelector .= "<option selected=\"selected\" value=\"".$style['styleID']."\">".$style['styleName']."</option>";
                }
                else {
                    $stylesSelector .= "<option value=\"".$style['styleID']."\">".$style['styleName']."</option>";
                }
            }
        }
    }

    // if no styles have been added for this track
    else {
        // iterate all available styles
        foreach($styles as $style) {
            $stylesSelector .= "<option value=\"".$style['styleID']."\">".$style['styleName']."</option>";
        }
    }
    $stylesSelector .= "</select>";
}

下面的屏幕截图显示了上述代码生成的实际页面部分。对于我所缺少的专家来说，一定是显而易见且容易的事情，但我仍然看不到它。

Answer 1

您可以使用 GROUP_CONCAT将结果缩小到二维，这样您就不会仅仅为了填充样式下拉列表而获得重复的相册。相反，您可以将样式组合起来并使用换行符使其变得漂亮。

您的查询最终看起来像这样:

SELECT
  trackID,
  vinylID,
  side,
  trackArtist,
  trackTitle,
  notes,
  GROUP_CONCAT(mKeyID SEPARATOR '\n') as 'mKeyIDs',
  GROUP_CONCAT(mKey SEPARATOR '\n') as 'mKeys',
  Camelot,
  GROUP_CONCAT(styleID SEPARATOR '\n') AS 'styleIDs',
  GROUP_CONCAT(styleName SEPARATOR '\n') AS 'styleNames'
FROM
  vinyl_tracks vt
INNER JOIN
  vinyl_keys vk ON vt.mKeyID = vk.mKeyID
INNER JOIN
  vinyl_tracks_styles vts ON vt.trackID = vts.trackID
INNER JOIN
  vinyl_styles vs ON vts.styleID = vs.styleID
GROUP BY
  vt.trackID
WHERE
  vinylID = :vinylID

我在这里按轨道 ID 进行分组，因为这似乎是您用来确定某些内容是否重复的标准。从那里，我使用 GROUP_CONCAT 和新的行分隔符来在同一单元格中显示受该组影响的所有样式。您的 PHP 可以引用此单元格，并在新行字符上展开以获取填充下拉列表所需的内容。

首先确保此查询直接在数据库上运行，看看是否需要对其进行调整。

JSON 方法:

或者，如果您的 MySQL 版本支持 JSON_ARRAYAGG ，您可以使用它代替 GROUP_CONCAT 来完成功能等效的行为，尽管更简洁:

SELECT
  trackID,
  vinylID,
  side,
  trackArtist,
  trackTitle,
  notes,
  JSON_ARRAYAGG(mKeyID) AS 'mKeyIDs',
  JSON_ARRAYAGG(mKey) AS 'mKeys',
  Camelot,
  JSON_ARRAYAGG(styleID) AS 'styleIDs',
  JSON_ARRAYAGG(styleName) AS 'styleNames'
FROM
  vinyl_tracks vt
INNER JOIN
  vinyl_keys vk ON vt.mKeyID = vk.mKeyID
INNER JOIN
  vinyl_tracks_styles vts ON vt.trackID = vts.trackID
INNER JOIN
  vinyl_styles vs ON vts.styleID = vs.styleID
GROUP BY
  vt.trackID
WHERE
  vinylID = :vinylID

然后在 PHP 中，您只需引用 $row['styleIDs'] 和 $row['styleNames'] 即可生成 key=>value 对“样式”下拉列表中使用的每个选项。

对于音乐“Keys”下拉选项，您可以引用 $row['mKeyID'] 和 $row['mKeys'] 来生成 key= >“键”下拉列表中使用的每个选项的值对。

更新:

我在本地进行了设置，并查看了 OP 遇到的问题。我的查询没问题。然而，当尝试显示下拉菜单时，OP 的 PHP 循环逻辑遇到了错误。更具体地说，以下逻辑是错误的:

        foreach($styles as $style) {

            // if there's a match, add "selected" to the option
            if($track['styleIDs'] == $style['styleID']) {
                $stylesSelector .= "<option selected=\"selected\" value=\"".$style['styleID']."\">".$style['styleName']."</option>";
            }
            else {
                $stylesSelector .= "<option value=\"".$style['styleID']."\">".$style['styleName']."</option>";
            }
        }
    }

现在我明白了 OP 想要做什么，让我们重写整个困惑的内容。我已经重构了代码。我也在本地测试过:

$tracksQuery = $db->prepare("SELECT vt.trackID, vt.vinylID, vt.side, vt.trackArtist, vt.trackTitle, vt.notes, vt.mKeyID, vk.mKey, vk.Camelot, 
                            GROUP_CONCAT(vts.styleID SEPARATOR ',') AS 'styleIDs'
                            FROM vinyl_tracks vt
                            INNER JOIN vinyl_keys vk ON vt.mKeyID = vk.mKeyID
                            LEFT JOIN vinyl_tracks_styles vts ON vt.trackID = vts.trackID
                            WHERE vt.vinylID = :vinylID
                            GROUP BY vt.trackID");
$tracksQuery->bindParam(':vinylID', $vinylID);
$tracksQuery->execute();
$tracks = $tracksQuery->fetchAll(\PDO::FETCH_ASSOC);

$allStylesQuery = $db->prepare("SELECT * FROM vinyl_styles ORDER BY styleID ASC");
$allStylesQuery->execute();
$styles = $allStylesQuery->fetchAll(PDO::FETCH_ASSOC);


$stylesSelector = "";

foreach($tracks as $track)
{

    $stylesSelector .= "<select multiple=\"multiple\" class=\"form-control form-control-sm\" name=\"styleID[".$track['trackID']."][]\"><option> &ndash; </option>";
    $trackStyleIDs = explode(',', $track['styleIDs']);
    foreach($styles as $style)
    {
        $optionValue = $style['styleID'];
        $optionText = $style['styleName'];
        $optionSelected = in_array($optionValue, $trackStyleIDs) ? ' selected="selected"' : '';
        $stylesSelector .= sprintf('<option value="%s"%s>%s</option>', $optionValue,$optionSelected,$optionText);
    }

    $stylesSelector .= "</select>";
}