title: Lookup table in MySQL
body: <p>I'm doing my first work in PHP/MySQL &amp; I need help. I have one master table:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE `m4l_movies` (
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      `Title` varchar(250) NOT NULL,
      `Rating` int(11) NOT NULL,
      `Genre` varchar(250) NOT NULL,
      `Actors` varchar(250) NOT NULL,
      `UserID` int(11) NOT NULL DEFAULT '1',
      PRIMARY KEY (`ID`)
  ) ENGINE=MyISAM AUTO_INCREMENT=115 DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>that receives input for a form which has values from a these lookup tables:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE `m4l_actors` (
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      `Actor` varchar(255) NOT NULL,
      PRIMARY KEY (`ID`)
  ) ENGINE=InnoDB AUTO_INCREMENT=232 DEFAULT CHARSET=utf8;
  
  CREATE TABLE `m4l_genre` (
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      `Genre` varchar(255) NOT NULL,
      PRIMARY KEY (`ID`)
  ) ENGINE=InnoDB AUTO_INCREMENT=202 DEFAULT CHARSET=utf8;
  
  CREATE TABLE `m4l_movierating` (
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      `Movie_Rating` varchar(250) NOT NULL,
      PRIMARY KEY (`ID`)
  ) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>&amp; I've created a View: </p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT m4l_movies.ID AS ID,
    m4l_movies.Title AS Title,
    m4l_movierating.Movie_Rating AS Rating,
    m4l_movies.Actors AS Actors,
    m4l_movies.Genre AS Genre
  FROM m4l_movies
    JOIN m4l_movierating ON m4l_movierating.ID = m4l_movies.Rating
    INNER JOIN m4l_genre ON m4l_movies.Genre = m4l_genre.ID
    INNER JOIN m4l_actors ON m4l_movies.Actors = m4l_actors.ID
  ORDER BY m4l_movies.Title
  </code></pre>
  
  <p>Here is the out put I get:</p>
  
  <pre class="lang-none prettyprint-override"><code>----------------------------------------------------------------|
    ID      Title       Rating    Actor         Genre             |
    10      Summer      G         (10,15,25)    (45,115,123)      |
     1      About You   G-1       (63,163,405)  (3,16,51)         |
     5      Dog Years   P         (45,65,95)    (98,163,357)      |
  ----------------------------------------------------------------|
  </code></pre>
  
  <p>Firstly this view should return more than 200 records. Secondly, I need to know how to either create a <em>lookup</em> or some other method to convert the NAME &amp; GENRE back to their corresponding text values. Some how the RATING value is doing it right but I can't get NAME  or GENRE to do it correctly. I'm sure it has to do with either the way I've joined the tables but I can't figure out where I'm going wrong. Will someone PLEASE help me.</p>
  
  <p>Ok trying to follow along with suggesting made by Phil I've removed actors, genre from movies tale &amp; created movies_genre &amp; movies_actors</p>
  
  <pre><code>DROP TABLE IF EXISTS `m4l_movies`;
  CREATE TABLE `m4l_movies` (
    `ID` int(11) NOT NULL auto_increment,
    `Title` varchar(250) NOT NULL,
    `Year` float NOT NULL,
    `Review` varchar(250) NOT NULL,
    `Rating` int(11) NOT NULL,
    `Image` varchar(250) NOT NULL,
    `Storyline` longtext NOT NULL,
    `Director` varchar(250) NOT NULL,
    `UserID` int(11) NOT NULL default '1',
    PRIMARY KEY  (`ID`)
  ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  
  
   CREATE TABLE `m4l_movie_actor` (
      movie_id INT(11),
      actor_id INT(11),
      PRIMARY KEY (movie_id, actor_id),
      FOREIGN KEY (movie_id) REFERENCES m4l_movies (ID),
      FOREIGN KEY (actor_id) REFERENCES m4l_actors (ID)
   );
  
  
  CREATE TABLE `m4l_movie_genre` (
      movie_id INT(11),
      genre_id INT(11),
      PRIMARY KEY (movie_id, genre_id),
      FOREIGN KEY (movie_id) REFERENCES m4l_movies (ID),
      FOREIGN KEY (genre_id) REFERENCES m4l_genre (ID)
  );
  
  
  DROP TABLE IF EXISTS m4l_genre;
  CREATE TABLE m4l_genre (
    ID int(11) NOT NULL auto_increment,
    Genre varchar(250) NOT NULL,
    PRIMARY KEY  (ID)
  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  
  
  DROP TABLE IF EXISTS m4l_actors;
  CREATE TABLE m4l_actors (
    ID int(11) NOT NULL auto_increment,
    Actor varchar(255) NOT NULL,
    PRIMARY KEY  (ID)
  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>but when I try to create the movie_actor OR movie_genre I get</p>
  
  <blockquote>
    <p>09:29:45<br>
    CREATE TABLE `m4l_movie_actor` (movie_id INT(11), actor_id INT(11),<br>
    PRIMARY KEY (movie_id, actor_id),<br>
    FOREIGN KEY (movie_id) REFERENCES m4l_movies (ID),<br>
     FOREIGN KEY (actor_id) REFERENCES m4l_actors (ID) )<br>
    Error Code: 1215. Cannot add foreign key constraint<br>
    0.000 sec</p>
    
    <p>09:40:56 CREATE TABLE <code>m4l_movie_genre</code> (movie_id INT(11),genre_id INT(11),<br>
    PRIMARY KEY (movie_id, genre_id),<br>
    FOREIGN KEY (movie_id) REFERENCES m4l_movies (ID),<br>
    FOREIGN KEY (genre_id) REFERENCES m4l_genre (ID) )<br>
    Error Code: 1215. Cannot add foreign key constraint<br>
    0.016 sec</p>
  </blockquote>
  
  <p>From what I can gather this only occurs when you have data types mismatch, but I THINK I have all INT data types so why am I getting this error?</p>