Lookup table in MySQL -

i'm doing first work in php/mysql & need help. have 1 master table:

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; 

that receives input form has values these lookup tables:

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; 

& i've created view:

select m4l_movies.id id,   m4l_movies.title title,   m4l_movierating.movie_rating rating,   m4l_movies.actors actors,   m4l_movies.genre genre 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 m4l_movies.title 

here out put get:

----------------------------------------------------------------|   id      title       rating    actor         genre             |   10      summer      g         (10,15,25)    (45,115,123)      |    1        g-1       (63,163,405)  (3,16,51)         |    5      dog years   p         (45,65,95)    (98,163,357)      | ----------------------------------------------------------------| 

firstly view should return more 200 records. secondly, need know how either create lookup or other method convert name & genre corresponding text values. how rating value doing right can't name or genre correctly. i'm sure has either way i've joined tables can't figure out i'm going wrong. please me.

ok trying follow along suggesting made phil i've removed actors, genre movies tale & created movies_genre & movies_actors

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; 

but when try create movie_actor or movie_genre get

09:29:45
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) )
error code: 1215. cannot add foreign key constraint 0.000 sec

09:40:56 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) )
error code: 1215. cannot add foreign key constraint 0.016 sec

from can gather occurs when have data types mismatch, think have int data types why getting error??

you appear have made classic blunder of storing relational data in un-relatable way.

you should using junction tables instead of comma separated values. example...

create table `m4l_movies` (     `id` int(11) not null auto_increment,     `title` varchar(250) not null,     `rating` int(11) not null,     -- removed actors , genre     `userid` int(11) not null default '1',     primary key (`id`) );  create table `m4l_movie_actor` (     movie_id int(11) not null,     actor_id int(11) not null,     primary key (movie_id, actor_id),     foreign key (movie_id) references m4l_movies (id),     foreign key (actor_id) references m4l_actors (id) );  -- repeat genres  

you can join on junction table , on actors / genres tables, eg

select ... m4l_actors.actor ... m4l_movies inner join m4l_movie_actor on m4l_movies.id = m4l_movie_actor.movie_id inner join m4l_actors on m4l_movie_actor.actor_id = m4l_actors.id 

if want result in comma separated list, group_concat()