我正在进行我的第一个PHP/MySQL工作,需要帮助。我有一张主表:
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;
该表单接收输入,其中包含来自这些查找表的值:
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;
我已经创建了一个视图:
&
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
这是我得到的输出:
----------------------------------------------------------------|
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) |
----------------------------------------------------------------|
首先,这个视图应该返回超过200条记录。其次,我需要知道如何创建一个查找或其他方法来将名称和类型转换回它们对应的文本值。某种程度上,评分值做得很好,但我无法正确获取名称或类型。我相信这与我连接表的方式有关,但我无法弄清楚哪里出了问题。请有人帮帮我。
好的,试图按照Phil的建议跟进,我已经从电影表中移除了演员和类型,并创建了电影类型和电影演员表。
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;
但是,当我尝试创建电影演员或电影流派时,我遇到了以下问题:
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 sec09: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
据我所知,这只会发生在数据类型不匹配时,但我认为我所有的数据类型都是INT,那么为什么会出现这个错误?