Databases
StackOverflow2013
Postsdbo
PODisplay / Logic issue, Many-to-many, how to display?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODisplay / Logic issue, Many-to-many, how to display?
    text
    copied!<p>i'm having a real problem here, i'm using php and mysql i designed a database for a music website.:</p> <p><code>artists</code> - <code>artist_id</code>, <code>artist_name</code>;</p> <p><code>tracks</code> - <code>track_id</code>, <code>track_name</code>;</p> <p><code>ArtistTracks</code> - <code>track_id</code>, <code>artist_id</code> <code>// &lt; no more than one row for any combination.</code>;</p> <p>so i designed it and it's all good but now what if there a duet (to artists have the same track).</p> <p>i'm so stupid that i just thought that i will solve this when i will add a duet.</p> <p>so now i added a duet how do i display / select it.?</p> <p>for example to get the latest tracks i do this.</p> <pre><code> $sql = 'SELECT Tracks.track_id, Tracks.track_name, Artists.artist_name FROM ArtistTracks, Tracks, Artists WHERE ArtistTracks.track_id = Tracks.track_id AND ArtistTracks.artist_id = Artists.artist_id ORDER BY tracks.track_id DESC LIMIT 10'; </code></pre> <p>the result fair enough is like this:</p> <pre><code>Array ( [0] =&gt; Array ( [track_id] =&gt; 33 [track_name] =&gt; track duet. [artist_name] =&gt; artist b ) [1] =&gt; Array ( [track_id] =&gt; 33 [track_name] =&gt; track duet. [artist_name] =&gt; artist b ) </code></pre> <p>my question is, did i did a mistake with the design of the database.???</p> <p>and if not, how I'm suppose to display them do i need to loop over the whole array and check if the <code>track_id</code> match another <code>track_id</code> ??</p> <p>thanks in advance for any help :\</p> <p><strong>EDIT</strong></p> <p>i have benn trying literally the whole day to find a solution that will be perfect, including droping the whole database and trying mongoDB LOLs, </p> <p>i managed to make the tracks appear as the should <code>singer_one</code> and <code>singer_two</code> - <code>track_name</code></p> <p>the problem is that i had 6 templates for my whole website and now i have 12 (because there are two many conditionals)..</p> <p>i dont like this solution because now i need to update any change in the site twice (because of the conditionals for displaying the duets.)</p> <p>is my design of many-to-many wrong should i make another table for duets??</p> <p>thanks in advance.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload