Databases
StackOverflow2013
Postsdbo
POMySQL displaying results from a many-to-many relationship
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL displaying results from a many-to-many relationship
    primarykey
    data
    text
    <p>I have set up a many-to-many relationship db with 3 tables:</p> <pre><code>CREATE TABLES Films ( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), Title VARCHAR(255)), CREATE TABLE Moods ( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), mood_name VARCHAR(255)) CREATE TABLE Films_Moods ( film_id INT NOT NULL, mood_id INT NOT NULL, PRIMARY KEY (film_id, mood_id), FOREIGN KEY (film_id) REFERENCES Films(id) ON UPDATE CASCADE, FOREIGN KEY (mood_id) REFERENCES Moods(id) ON UPDATE CASCADE)"; </code></pre> <p>So there might be a film with 2 or more moods. Let them be <code>$mood1</code> and <code>$mood2</code>.</p> <p>I want to display the selected film in a table as a single row, for example in the following way:</p> <pre><code>&lt;table&gt; &lt;tr&gt; &lt;th&gt;Title&lt;/th&gt; &lt;th&gt;Mood(s)&lt;/th&gt; &lt;/tr&gt; </code></pre> <p>and the PHP code:</p> <pre><code>while($row = mysqli_fetch_array($result)) { echo "&lt;tr&gt;"; echo "&lt;td&gt;" . $row['Title'] . "&lt;/td&gt;"; echo "&lt;td&gt;" . $row['Moods'] . "&lt;/td&gt;"; echo "&lt;/tr&gt;"; } </code></pre> <p>The question is: how to obtain <code>$row['Moods']</code> that would somehow combine two rows:<br> film_1 moodName_1<br> film_1 moodName_2</p> <p>Or is there an alternative approach to what I am trying to achieve?</p> <p><strong>EDIT</strong></p> <p>Actually Im using tables Genres and Ambiences similarly to Moods and Im trying to do a search with specific Genres and Ambiences:</p> <pre><code>"SELECT *,GROUP_CONCAT(ambienceName SEPARATOR ' ') AS ambiences FROM Films AS f INNER JOIN Films_Genres AS fg ON f.id = fg.film_id INNER JOIN Genres AS g ON g.id = fg.genre_id INNER JOIN Films_Ambiences as fa ON f.id = fa.film_id INNER JOIN Ambiences AS a ON a.id = fa.ambience_id WHERE g.Name LIKE '$genre' AND (a.ambienceName LIKE '$ambience1' OR a.ambienceName LIKE '$ambience2')" </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSharkz
    1. USSharkz
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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