cheeseus
cheeseus

Reputation: 473

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

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

Results from first approach – two tracks shown as four

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: one row per track, all styles shown in a multiple select

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

Styles repeated twice in select dropdown, the second style is highlighted too

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

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. styles repeated inside selector for each style

Upvotes: 0

Views: 729

Answers (2)

Ultimater
Ultimater

Reputation: 4738

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 '\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

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 .= "<option selected=\"selected\" value=\"".$style['styleID']."\">".$style['styleName']."</option>";
            }
            else {
                $stylesSelector .= "<option value=\"".$style['styleID']."\">".$style['styleName']."</option>";
            }
        }
    }

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

Upvotes: 1

DarkBee
DarkBee

Reputation: 15573

As I said in my comment, you have to breakdown the process in 2 steps.

  • First step is simply fetch the basic information of a track.
  • Second step is to fetch all the according styles of the track in a separate array

After you've stored the style seperatetly, you would now have an array like this,

[
    track_id_1 => [ style_id_1, style_id_2, ]
    track_id_2 => [ style_id_x, style_id_y, ]
]

Meaning if you know the track id and the style id you can test whether the style is attached to the current track with in_array:

<?php
     if (in_array($style_id, $styles_per_track[$track_id])) {
         echo 'Style '.$style_id.' is attached to track '.$track_id;
     }

A mock up of this solution would be the following.
(do note I did not test any of this code, it's just to give you an idea to separate the data)

    <?php
        // Step 1: Fetch all tracks w/out worrying about styles
        $tracks_stmt = $db->prepare('SELECT * FROM vinyl_tracks vt WHERE vinylID=:vinylID');
        $track_stmt->execute([':vinylID' => $vinylID, ]);

        $tracks = $track_stmt->fetchAll(\PDO::FETCH_ASSOC);

        // Step 2: Loop each track and store the styles accordingly
        $styles_per_track = [];
        $styles_per_track_stmt = $db->prepare('SELECT * FROM vinyl_tracks_styles vts WHERE trackId=:trackId')

        foreach($tracks as $track) {
            $vinyl_tracks_styles[$track['trackID']] = [];
            $styles_per_track_stmt->execute($track['trackID']);
            $temp = $styles_per_track_stmt->fetchAll(\PDO::FETCH_ASSOC);
            foreach($temp as $style_per_track) $vinyl_tracks_styles[$track['trackID']] = $style_per_track['styleID'];
        }

        //fetch styles
        /**
        ...
        ...
        ...
        **/
        foreach($tracks as $track) {
    ?>
            <select name="track[<?=$track['trackID'];?>" multiple>
    <?php
            foreach($styles as $style) {
    ?>
                <option value="<?= $style['styleID']; ?>" <?php if(in_array($style['styleID'], $styles_per_track[$track['trackID']])) { echo ' selected'; } ?>><?= $style['styleName']; ?></option>
    <?php
            }
    ?>
        </select>
    <?php
    }
    ?>

Upvotes: 0

Related Questions