Doesn't show movies for me...
PHP Code:
$querynew = sprintf("SELECT m.id, m.title, m.year GROUP_CONCAT(g.genre) AS genres
FROM movies m
INNER JOIN moviegenres mg ON (mg.movie_id = m.id)
INNER JOIN genres g ON (g.id = mc.genre_id)
GROUP BY m.id
ORDER BY m.id
DESC LIMIT 0,4");
$resultnew = mysql_query($querynew);
$TMPL_old = $TMPL; $TMPL = array();
$template = new template('movielist/newmovies');
$new = '';
while ($row = mysql_fetch_assoc($resultnew)) {
$new .= $template->make();
}
Maybe something wrong with my tables?
Movies
PHP Code:
CREATE TABLE IF NOT EXISTS `movies` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`title` VARCHAR(128) NOT NULL,
`year` YEAR NOT NULL,
`premiere` DATE NOT NULL default '0000-00-00',
`imdb` VARCHAR(10) NOT NULL,
`added` TIMESTAMP NOT NULL default CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Genres
PHP Code:
CREATE TABLE IF NOT EXISTS `genres` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`genre` VARCHAR(256) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `genres` (`id`, `genre`) VALUES
(1, 'comedy'),
(2, 'fantasy'),
(3, 'horror');
Genres relationship
PHP Code:
CREATE TABLE IF NOT EXISTS `moviegenres` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`movie_id` INT(10) UNSIGNED NOT NULL,
`genre_id` INT(10) UNSIGNED NOT NULL,
FOREIGN KEY (`movie_id`) REFERENCES `movies`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`genre_id`) REFERENCES `genres`(`id`) ON DELETE RESTRICT
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `moviegenres` (`id`, `movie_id`, `genre_id`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3);