Help with sql / php
Posted: Thu Oct 04, 2007 1:39 pm
Now I finnaly understood how should look database structure for that kind of think.
Basicly it's movies database, where in the mane table I keep movie title and some other misc stuff.
What I don't understand is how to list a movies and display theyr multy genres/actors and so on not making hundret queries...
If anyone will try to help me on this, pleas give me an example on also how to edit/add/delete them.
Thank you and here is what I theoricly created over phpMyAdmin, this should be the structure how it should look.
Basicly it's movies database, where in the mane table I keep movie title and some other misc stuff.
What I don't understand is how to list a movies and display theyr multy genres/actors and so on not making hundret queries...
If anyone will try to help me on this, pleas give me an example on also how to edit/add/delete them.
Thank you and here is what I theoricly created over phpMyAdmin, this should be the structure how it should look.
Code: Select all
-- phpMyAdmin SQL Dump
-- version 2.11.0
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 04, 2007 at 09:32 PM
-- Server version: 5.0.45
-- PHP Version: 5.2.4
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: `movies`
--
-- --------------------------------------------------------
--
-- Table structure for table `actors`
--
CREATE TABLE `actors` (
`actor_id` tinyint(11) NOT NULL auto_increment,
`actor_fullname` varchar(255) NOT NULL,
`descriotion` text NOT NULL,
PRIMARY KEY (`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
--
-- Dumping data for table `actors`
--
INSERT INTO `actors` (`actor_id`, `actor_fullname`, `descriotion`) VALUES
(1, 'James Bond', 'Agent 007');
-- --------------------------------------------------------
--
-- Table structure for table `actors_relations`
--
CREATE TABLE `actors_relations` (
`movie_id` tinyint(11) NOT NULL,
`actor_id` tinyint(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `actors_relations`
--
INSERT INTO `actors_relations` (`movie_id`, `actor_id`) VALUES
(1, 1);
-- --------------------------------------------------------
--
-- Table structure for table `directors`
--
CREATE TABLE `directors` (
`director_id` tinyint(11) NOT NULL auto_increment,
`director_fullname` varchar(255) NOT NULL,
`description` text NOT NULL,
PRIMARY KEY (`director_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
--
-- Dumping data for table `directors`
--
INSERT INTO `directors` (`director_id`, `director_fullname`, `description`) VALUES
(1, 'Tom Thomson', 'An good movies director...');
-- --------------------------------------------------------
--
-- Table structure for table `directors_relations`
--
CREATE TABLE `directors_relations` (
`movie_id` tinyint(11) NOT NULL,
`director_id` tinyint(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `directors_relations`
--
INSERT INTO `directors_relations` (`movie_id`, `director_id`) VALUES
(1, 1);
-- --------------------------------------------------------
--
-- Table structure for table `genres`
--
CREATE TABLE `genres` (
`genre_id` tinyint(11) NOT NULL auto_increment,
`genres` varchar(255) NOT NULL,
PRIMARY KEY (`genre_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `genres`
--
INSERT INTO `genres` (`genre_id`, `genres`) VALUES
(1, 'comedy'),
(2, 'horror');
-- --------------------------------------------------------
--
-- Table structure for table `genres_relations`
--
CREATE TABLE `genres_relations` (
`movie_id` tinyint(11) NOT NULL,
`genre_id` tinyint(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `genres_relations`
--
INSERT INTO `genres_relations` (`movie_id`, `genre_id`) VALUES
(1, 1);
-- --------------------------------------------------------
--
-- Table structure for table `movies`
--
CREATE TABLE `movies` (
`movie_id` tinyint(11) NOT NULL auto_increment,
`movie_title` varchar(255) NOT NULL,
`tagline` varchar(255) NOT NULL,
`runtime` tinyint(11) NOT NULL,
PRIMARY KEY (`movie_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
--
-- Dumping data for table `movies`
--
INSERT INTO `movies` (`movie_id`, `movie_title`, `tagline`, `runtime`) VALUES
(1, 'Some movie', 'Very nice movie...', 122);