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

Question

I've been struggling with the following case:

FIRST APPROACH:

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

For some of the tracks, table vts will contain two or more styles (the table only contains the trackID and styleID on separate rows. This causes the entire result to repeat. I've tried DISTINCT, and all sorts of GROUP BY, including both DISTINCT and GROUP BY. This does return one result per track but the second styleID is missing.

Here is the var_dump of this query without any 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)

Then I run another query to fetch all styles so I can populate the multiple select drop down. This is what it looks like with the foreach loop to create the dropdown and a screenshot – the two tracks, each having two styles, are shown as four tracks:

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

ALTERNATIVE APPROACH: I've also tried to accomplish the same using two queries:

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

Then, inside the foreach loop to get the results from the first query, the following query:

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

This alternative way produces the results I want, e.g.:

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)

But then I fail at displaying the results the I way I need them, which is:

In order to list all possible styles in the select dropdown, I need to run one more query (the WHERE condition above prevents all style names and IDs from displaying despite the RIGHT JOIN, I know):

SELECT * FROM vinyl_styles ORDER BY styleID ASC

And the only way to highlight the select options (styles) that I've found is to nest two foreach loops inside one another, which of course again leads to duplicate results:

$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 = "";

If I stick to the first one-query variant above, the entire row of inputs is repeated twice, so for these two tracks I get four rows.

I'm really out of ideas, please help.

UPDATE

Going with the fewer-queries approach suggested by @Ultimater I have now arrived at a situation where I have all the details I need, the only problem being that the styles list is repeated inside the styles dropdown for each style that a track has. If only one style or no styles have been added for a track, there is no problem. Posting my full code below:

$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 .= "";
}

The following screenshot shows the actual page section generated by the above code. Must be something obvious and easy for experts that I'm missing but I still can't see it.

Ultimater · Accepted Answer

You can use a GROUP_CONCAT to get your results down to two dimensions so you don't get duplicate albums merely to populate a styles dropdown. Instead you'd group concat the styles and make it pretty using the new line character.

Your query would wind up looking something like this:

SELECT
  trackID,
  vinylID,
  side,
  trackArtist,
  trackTitle,
  notes,
  GROUP_CONCAT(mKeyID SEPARATOR '
') as 'mKeyIDs',
  GROUP_CONCAT(mKey SEPARATOR '
') as 'mKeys',
  Camelot,
  GROUP_CONCAT(styleID SEPARATOR '
') AS 'styleIDs',
  GROUP_CONCAT(styleName SEPARATOR '
') 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

I am grouping here by track id because that appears to be the criteria you are using to determine whether something is a duplicate or not. From there, I'm using GROUP_CONCAT with the new line separator to show all your styles affected by this group by in the same cell. Your PHP could refer to this cell, and explode on the new line character to get what it needs for populating the dropdown.

Make sure this query works directly on the database first to see if you need to make adjustments to it.

JSON approach:

Alternatively, if your MySQL version supports JSON_ARRAYAGG, you could use it instead of GROUP_CONCAT to accomplish the functionally equivalent behavior albeit the much cleaner:

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

Then in your PHP you'd simply reference $row['styleIDs'] and $row['styleNames'] to generate the key=>value pair for each option used in your "Styles" dropdown.

For the musical "Keys" dropdown options, you'd reference $row['mKeyID'] and $row['mKeys'] to generate the key=>value pair for each option used in your "Keys" dropdown.

Update:

I set this up locally and see the problem the OP is running into. My query is fine. However when trying to display the dropdown, OP is running into a bug with his or her PHP looping logic. More specifically the following logic is faulty:

        foreach($styles as $style) {

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

Now that I understand what the OP is trying to do, let's just rewrite the entire mess. I have refactored the code. I have also tested this locally:

$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 .= "";
}

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

Answers (2)

JSON approach:

Related Questions