Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The best way I can think of is to have a <em>many-to-many relationship</em> between genres and movies (meaning a movie can have multiple genre and one genre can appear on many movies).</p> <p>This involves adding two more tables, one to define the genres, and one to associate them with movies:</p> <pre><code>genres --------- id | name </code></pre> <p>Here's the slightly tricky part:</p> <pre><code>genres-movies ------------------- movie_id | genre_id </code></pre> <p>Where <code>movie_id</code> is an actual ID from the <code>movies</code> table, and <code>genre_id</code> is an actual ID from the <code>genres</code> table.</p> <p>For instance, genre with the ID of 1 is <code>Science Fiction</code>, and the movie with the ID of 42 is <code>Hitchhiker's Guide to the Galaxy</code> (see what I did there?), this means that if I have the entry <code>42 | 1</code> at the <code>genres-movies</code>, will mean that Hitchiker's Guide to the Galaxy is a Science Fiction. Note that neither <code>movie_id</code> nor <code>genre_id</code> are unique, both can appear multiple times, so the following is possible:</p> <pre><code>movie_id | genre_id ---------+--------- 1 | 2 1 | 3 2 | 2 3 | 1 </code></pre> <p>To find all the genres of a movie:</p> <pre><code>SELECT `genre_id` FROM `genres-movies` WHERE `movie_id` = &lt;ID&gt;; </code></pre> <hr> <p>After all the background, to your question. With that approach, you need multiple queries, remove all genres from a movie, then each to add the ones you selected. You have to do that because checkboxes that were not selected are not sent to the server (pseudo code)</p> <pre><code>DELETE FROM `genres-movies` WHERE `movie_id` = &lt;ID&gt;; --Remove all genres foreach ($genres_from_checkboxes as $genre) { INSERT INTO `genres-movies` (`movie_id`, `genre_id`) VALUES (:movie_id, :genre_id) } </code></pre> <p><strong>NOTE!</strong> Always sanitize (or better yet <em>prepare!!!</em>) queries with data that came from the user!</p> <p>Phew! That was long, hope you learned a thing or two :)</p>
    singulars
    1. This table or related slice is empty.
    1. POPHP form to edit database entries
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USMadara Uchiha
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POPHP form to edit database entries
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThanks for the answer. I was already doing what you said regarding the many-to-many relationship, but thanks for the confirmation that I did it right. In the mapping table I also have a combo column that is a concatenation of the two ids; and this IS unique (for the most part).\n>remove all genres from a movie, then each to add the ones you selected.\n\nI was thinking about doing the update this was, but wasn't sure it was the best solution. But I'm becoming convinced that it is the most elegant solution.
      singulars
      1. PO
      1. USdan08
    2. CO@dan08: You don't need that third column, you can have a [**UNIQUE KEY for both columns**](http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql) (Then the combination of them is unique :P). Also, you don't want to start messing with UPDATEs and what to update, what to remove and what to insert. It will all go a lot smoother if you just remove everything, and re-add it all. It's also not that big a deal, it's only what, 5-10 insertions tops?
      singulars
      1. PO
      1. USMadara Uchiha
    3. COOk, so I have this same kind of situation with other fields, like actors, and collections. It would just be best to clear all mappings and re-add them? Also, what I'm doing is very small scale and performance and speed is no issue. But at a larger scale is this still the way it would be done?
      singulars
      1. PO
      1. USdan08
 

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