Reputation: 473
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> – </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>";
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 = "<select multiple=\"multiple\" class=\"form-control form-control-sm stylesSelector\" name=\"styleID[".$trow['trackID']."][]\"><option> – </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>";
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> – </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.
Upvotes: 0
Views: 729
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.
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> – </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
Reputation: 15573
As I said in my comment, you have to breakdown the process in 2 steps.
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