1,105,208 Community Members

Need help for displaying movie database

Member Avatar
vizz
Posting Pro in Training
427 posts since Dec 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 9 [?]
 
0
 

I have created movie database. I have list of Genres,

<ul>
    <li><a href="movielist.php?id=<?php echo"$id";?>"><?php echo"$genre";?></a></li>
</ul>

In table movies, movie have multiple geners. How to fetch same movie if multiple geners available
(e.g. The Expendables 2 appear in Action | Adventure | Thriller these three geners). How to display movie in each genere?

Actors, Directors, Writers are stored in separate tables. how to fetch them while displaying movie details? Their id's are stored in movies table.

How to display info stored in movies table ?

Table structure for table actor

CREATE TABLE IF NOT EXISTS actor (
id bigint(12) NOT NULL AUTO_INCREMENT,
name varchar(500) NOT NULL,
surname varchar(500) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

Dumping data for table actor

INSERT INTO actor (id, name, surname) VALUES
(1, 'Sylvester', 'Stallone'),
(2, 'Jason', 'Statham'),
(3, 'Jean-Claude ', 'Van Damme'),
(4, 'Jet', 'Li'),
(5, 'Dolph', 'Lundgren'),
(6, 'Chuck', 'Norris'),
(7, 'Bruce', 'Willis'),
(8, 'Arnold', 'Schwarzenegger'),
(9, 'Terry', 'Crews'),
(10, 'Nan', 'Yu');

Table structure for table director

CREATE TABLE IF NOT EXISTS director (
id bigint(12) NOT NULL AUTO_INCREMENT,
name varchar(500) NOT NULL,
surname varchar(500) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

Dumping data for table director

INSERT INTO director (id, name, surname) VALUES
(1, 'Simon', 'West');

Table structure for table genres

CREATE TABLE IF NOT EXISTS genres (
id bigint(12) NOT NULL AUTO_INCREMENT,
genre varchar(500) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=23 ;

Dumping data for table genres

INSERT INTO genres (id, genre) VALUES
(1, 'Action'),
(2, 'Adventure'),
(3, 'Animation'),
(4, 'Biography'),
(5, 'Comedy'),
(6, 'Crime'),
(7, 'Documentary'),
(8, 'Drama'),
(9, 'Family'),
(10, 'Fantasy'),
(11, 'History'),
(12, 'Horror'),
(13, 'Music'),
(14, 'Musical'),
(15, 'Mystery'),
(16, 'TV Show '),
(17, 'Romance'),
(18, 'Sci-Fi'),
(19, 'Talk-Show'),
(20, 'Thriller'),
(21, 'War'),
(22, 'Western');

Table structure for table writers

CREATE TABLE IF NOT EXISTS writers (
id bigint(20) NOT NULL AUTO_INCREMENT,
name varchar(500) NOT NULL,
surname varchar(500) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

Dumping data for table writers

INSERT INTO writers (id, name, surname) VALUES
(1, 'Richard', 'Wenk'),
(2, 'Sylvester', 'Stallone'),
(3, 'Ken', 'Kaufman'),
(4, 'David', 'Agosto'),
(5, 'Dave', 'Callaham');

Table structure for table movies

CREATE TABLE IF NOT EXISTS movies (
id bigint(12) NOT NULL AUTO_INCREMENT,
title varchar(500) NOT NULL,
runningtime varchar(10) NOT NULL,
genre varchar(125) NOT NULL,
year date NOT NULL,
director varchar(125) NOT NULL,
writers varchar(125) NOT NULL,
actors varchar(125) NOT NULL,
description text NOT NULL,
storyline longtext NOT NULL,
image varchar(250) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

Dumping data for table movies

INSERT INTO movies (id, title, runningtime, genre, year, director, writers, actors, description, storyline, image) VALUES
(1, 'The Expendables 2', '103', '1,2,20', '2012-08-17', '1', '1,2,3,4,5', '1,2,3,4,5,6,7,8,9,10', 'Mr. Church reunites the Expendables for what should be an easy paycheck, but when one of their men is murdered on the job, their quest for revenge puts them deep in enemy territory and up against an unexpected threat.', 'Barney Ross is approached by CIA man Church, who wants him and his guns for hire to go to the former Soviet Union to retrieve something that was on a plane that crashed. Church doesn''t tell him what he is getting. And Church sends a woman, Maggie with him to make sure he gets it. They find the plane and get the thing but some men take one of Barney''s people hostage and the leader tells him to give him what they got or he''ll kill his hostage. The give it to him but he kills his hostage anyway. Barney asks Maggie what was so important about that thing. She says that it showed the location of a Russian plutonium storage mine. Barney decides to track the man down and deal with him. They track them down and discover that the man they seek is Vilain who leads a group known as The Sangs and that they have taken all the men from the surrounding villages to work the mine.', 'TheExpendables2.jpg');

Member Avatar
diafol
Where are my eyes?
12,940 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,845 [?]
Skill Endorsements: 90 [?]
Moderator
Featured
Sponsor
 
0
 

I'd say that your movie table was setup badly. You shouldn't have directors, writers, genres etc in your table if they can be multiple values. When you store them as you have it's difficult to do a search on them. I'd set up separate tables:

movie_writer

mw_id (PK/autoincrement) - optional
movie_id
writer_id

movie_genre

mg_id (PK/autoincrement) - optional
movie_id
genre_id

Then you can search easily. ALternatively you could have a big table:

movie_attr

ma_id
movie_id
type_id
value_id

where type_id referes to the type of value, e.g. 0 =director , 1=actor, 2=genre... (they'd be stored in a separate table). I don't like this idea as much.

Member Avatar
vizz
Posting Pro in Training
427 posts since Dec 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 9 [?]
 
0
 

Thanks

can you please tell me about all tables?

actor, director, writers, genres, movies are these tables good or should I add more tables movie_writer, movie_genre, movie_attr

Can you help me to store andd retrieve data?

Member Avatar
diafol
Where are my eyes?
12,940 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,845 [?]
Skill Endorsements: 90 [?]
Moderator
Featured
Sponsor
 
0
 

OK, well the fields holding multiple values should have their own movie_* tables, that way you can search effectively:

The original tables for actor, director etc are fine.
The movies table, as I mentioned should lose the multiple values fields and then you set up separate 'link' tables for them (as I outlines in my first post).

Say you want to get all the movies with 'Tom Cruise':

Your actors table may give you the actor_id, you then query this:

SELECT m.title, ma.character_name FROM movie_actor AS ma INNER JOIN actors AS a ON ma.actor_id = a.actor_id INNER JOIN movies AS m ON ma.movie_id = m.movie_id WHERE a.actor_name = 'Tom Cruise'

SO for tables such as:

movie_actor

movie_id
actor_id
character_name

movies

movie_id
title

actors

actor_id
actor_name

You could expand this further with other fields obviously:

actors

actor_id
actor_name
imdb_link

movies

movie_id
title
...(your other fields)...
imdb_link
rotten_tomatoes_link

This is a very simplified version, but your subject is more complicated. For example, you may wish to have an 'alternative_titles' table. If you just had a 'flattened' movies table, adding fields could present a problem, but for this, all you'd need to do would be to set up the table:

movies_alternative_titles

mx_id (optional)
movie_id
alt_title

You could have multiple entries for the same movie.

Member Avatar
vizz
Posting Pro in Training
427 posts since Dec 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 9 [?]
 
0
 

Thanks

So, I have to insert movies and actors into movie_actor to get information about actors playing role into specific movie?(May be to listout actors into that movie?)

And what about multiple geners?

movie_genre is this to store movie names and their multiple geners? as I said about the movie The Expendables 2 and it's multiple generes Action | Adventure | Thriller

Member Avatar
diafol
Where are my eyes?
12,940 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,845 [?]
Skill Endorsements: 90 [?]
Moderator
Featured
Sponsor
 
0
 

Yep that's similar:

movie_genre

movie_id
genre_id

MOVIE TABLE
movie_id  title
1         Expendables 2

GENRE TABLE
genre_id  genre
1         Action
2         Adventure
3         Triller

MOVIE_GENRE TABLE
movie_id  genre_id
1         1 
1         2
1         3

WHen you insert a new movie:

INSERT INTO `movie` SET `title` = 'Expendables 2'

get the last_id from this via PHP, ($id) then:

INSERT INTO `movie_genre` (`movie_id`, `genre_id`)  VALUES(($id, $g1),($id, $g2),($id, $g3))

You'd build this query via a loop if genres were an array. I'll use mysql_* for now as I'm not sure what you're using - but you should be using mysqli_* or PDO.

$r = mysql_query("INSERT INTO `movie` SET `title` = 'Expendables 2'");
$id = mysql_insert_id();
$val = array();
foreach($genres as $genre){
   $val[] = "($id, $genre)";
}
$valstring = implode(',',$val);
$r = mysql_query("INSERT INTO `movie_genre` (`movie_id`, `genre_id`)  VALUES($valstring)");

Something like that (not tested).

Member Avatar
vizz
Posting Pro in Training
427 posts since Dec 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 9 [?]
 
0
 

Thanks

I'm using mysql. Can you check my table structure? need little help to fetch results from all tables with join or left join.

$id = mysql_insert_id(); // This helped better to insert records. Thanks 

Final tables are,

actors

id
name

directors

id
name

writers

id
name

genres

id
name

movies

id
title
runningtime
year
description
storyline
image

movie_actor

id
movie_id
actor_id
character_name

movie_genre

id
movie_id
genre_id

movie_writers

id
movie_id
writer_id

Is this okay or anything can be modified?

Member Avatar
vizz
Posting Pro in Training
427 posts since Dec 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 9 [?]
 
0
 

I have this query, $query="SELECT movies.id, movies.title, movies.runningtime, movies.year, movies.description, movies.storyline, movies.image FROM movies LEFT JOIN movie_genre ON movies.id=movie_genre.movie_id WHERE genre_id=$id";

But this is not enough to collect all data from all tables.

Need help

Member Avatar
vizz
Posting Pro in Training
427 posts since Dec 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 9 [?]
 
0
 

one more query

$query=            
"SELECT movies.id, movies.title, movies.runningtime, movies.year, movies.description, movies.storyline, movies.image        

FROM movies         

LEFT JOIN movie_genre ON movies.id=movie_genre.movie_id WHERE genre_id=$id      

LEFT JOIN genres ON genres.id = movie_genre.genre_id ";

How to select other information like,- genres, actors, directors, writers

Member Avatar
diafol
Where are my eyes?
12,940 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,845 [?]
Skill Endorsements: 90 [?]
Moderator
Featured
Sponsor
 
0
 

SELECT movies.id, movies.title, movies.runningtime, movies.year, movies.description, movies.storyline, movies.image, genre.name FROM movies ...

You can use GROUP_CONCAT(actors.actor_name) and GROUP BY movies.movie_id to get single rows for each movie with a comma separated list of actors. THis will give you a similar output to the original table you were using.

E.g.

SELECT movies.title, GROUP_CONCAT(genres.name) AS groupgenres FROM movies LEFT JOIN movie_genre ON movies.movie_id = movie_genre.movie_id INNER JOIN genres ON genres.genre_id = movie_genre.genre_id GROUP BY movies.movie_id
Member Avatar
vizz
Posting Pro in Training
427 posts since Dec 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 9 [?]
 
0
 

Thanks for reply

But, Query is not working. I have posted my all tables above. can you please help me to write complete query to get all info from database?

Following is my working query providing just movies table info,

$id=$_REQUEST['id'];

$query="SELECT movies.id, movies.title, movies.runningtime, movies.year, movies.description, movies.storyline, movies.image 

FROM movies

LEFT JOIN movie_genre ON movies.id=movie_genre.movie_id WHERE genre_id=$id ";

How to get other Information? Also how to get all info in while loop?

This is info from 'movies' Table

while($row = mysql_fetch_assoc($result)) 
{ 
    $id=$row["id"];
    $title=$row["title"];
    $runningtime=$row["runningtime"];
    $year=$row["year"];
    $description=$row["description"];
    $storyline=$row["storyline"];
    $image=$row["image"];
}
Member Avatar
diafol
Where are my eyes?
12,940 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,845 [?]
Skill Endorsements: 90 [?]
Moderator
Featured
Sponsor
 
1
 
SELECT m.title, GROUP_CONCAT(DISTINCT g.name), GROUP_CONCAT(DISTINCT a.actor_name)FROM movies AS m 
    LEFT JOIN movie_genre AS mg ON m.movie_id = mg.movie_id 
    LEFT JOIN genres AS g ON mg.genre_id = g.genre_id 
    LEFT JOIN movie_actor AS ma ON m.movie_id = ma.movie_id 
    LEFT JOIN actors AS a ON ma.actor_id = a.actor_id 
GROUP BY m.movie_id

That's an example of linking actors and genres tables. The DISTINCT is improtant or you end up with a cartesian product.

Member Avatar
vizz
Posting Pro in Training
427 posts since Dec 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 9 [?]
 
0
 

Please implement my query

index.php

<?php 

    $query="SELECT * FROM genres";
    $result = mysql_query($query);
    while($row = mysql_fetch_assoc($result)) 
    { 
        $id=$row["g_id"];
        $genre=$row["genre"];

?>          

        <ul>
            <li><a href="movies.php?id=<?php echo"$id";?>"><?php echo"$genre";?></a></li>
        </ul>

<?php 
        }
?>

movies.php

<?php 
    include("config.php");


    $id=$_REQUEST['id'];

    $query="SELECT a.*,b.*,c.*,d.*,e.*,f.*,g.*,h.*,i.*  

    FROM

    movies as a, 
    actors as b,
    directors as c,
    genres as d,

    movie_actor as e, 
    movie_directors as f, 
    movie_genre as g,
    movie_writers as h, 
    writers as i 

    WHERE 

    g.genre_id=$id";

    $result = mysql_query($query);

    while($row = mysql_fetch_assoc($result)) 
    { 
        //movies table
        $id=$row["movies_id"];
        $title=$row["title"];
        $runningtime=$row["runningtime"];
        $year=$row["year"];
        $description=$row["description"];
        $storyline=$row["storyline"];
        $image=$row["image"];

        //actors table
        $a_id=$row["id"];   
        $actor_name=$row["actor_name"]; 

        //directors table   
        $d_id=$row["id"];
        $director_name=$row["director_name"];       

        //genres table
        $g_id=$row["g_id"];
        $genre=$row["genre"];

        //movie_actor table
        $movie_actor_id=$row["id"];
        $movie_id=$row["movie_id"];
        $actor_id=$row["actor_id"];
        $character_name=$row["character_name"];

        //movie_directors table     
        $movie_directors_id=$row["id"];
        $movie_id=$row["movie_id"];
        $director_id=$row["director_id"];

        //movie_genre table
        $movie_genre_id=$row["id"];
        $movie_id=$row["movie_id"];
        $genre_id=$row["genre_id"];

        //movie_writers table
        $movie_writers_id=$row["id"];
        $movie_id=$row["movie_id"];
        $writer_id=$row["writer_id"];

        //writers table
        $writers_id=$row["id"];
        $writer_name=$row["writer_name"];




    }
?>  
Member Avatar
diafol
Where are my eyes?
12,940 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,845 [?]
Skill Endorsements: 90 [?]
Moderator
Featured
Sponsor
 
0
 

Please implement my query

Why?

Member Avatar
vizz
Posting Pro in Training
427 posts since Dec 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 9 [?]
 
0
 

Thanks for reply

it is not working well either it goes to infinite or it give only one actor or one genere or only one writer even having multiple records.

Actors are multiple
Generes are multiple (may be)
Writers are multiple (may be)

little confused about join

How to join all tables and get all data? My query is not working perfectly. So need little help to implement query on movies.php.

I want to display records something like IMDB, not exactly but nearly same.

Please check with attached databse, you will understand the problem.
Database is attached

Attachments movie_database.txt (6.71KB)
Member Avatar
diafol
Where are my eyes?
12,940 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,845 [?]
Skill Endorsements: 90 [?]
Moderator
Featured
Sponsor
 
0
 

Did my GROUP CONCAT DISTINCT query not work for you?

Member Avatar
vizz
Posting Pro in Training
427 posts since Dec 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 9 [?]
 
0
 

didn't worked.
So I wrote new one. Which is also not working properly.

Member Avatar
diafol
Where are my eyes?
12,940 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,845 [?]
Skill Endorsements: 90 [?]
Moderator
Featured
Sponsor
 
0
 

didn't worked.

Which bit of it didn't work? What was the mysql error? Chances are there was a typo in one of the field or table names asthis works perfectly for me.

Member Avatar
vizz
Posting Pro in Training
427 posts since Dec 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 9 [?]
 
0
 

field names was the little problem. I altered field names, but problem occures here, while($row = mysql_fetch_assoc($result)) {} mysql_fetch_assoc() expects parameter 1 to be resource, boolean given

I have attached my simple code and my final database movie_database.txt as attachment

Please Kindly check with that. You are only one person helping me right now, thats why I'm asking again and again to you. Sorry for that and please check my db and kindly alter my query or write new query for me

thanks

Member Avatar
diafol
Where are my eyes?
12,940 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,845 [?]
Skill Endorsements: 90 [?]
Moderator
Featured
Sponsor
 
0
 

thats why I'm asking again and again to you

No problem, if I wasn't here to help, I wouldn't post. :)

I don't usually download stuff. I may have a look later on.

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article