Need help for displaying movie database

[vizz]

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');

[diafol]

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.

[vizz]

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?

[diafol]

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.

[vizz]

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

[diafol]

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).

[vizz]

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?

[vizz]

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

[vizz]

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

[diafol]

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

[vizz]

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"];
}

[diafol]

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.

[vizz]

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"];




    }
?>  

[diafol]

Please implement my query

Why?

[vizz]

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

[diafol]

Did my GROUP CONCAT DISTINCT query not work for you?

[vizz]

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

[diafol]

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.

[vizz]

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

[diafol]

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.

[diafol]

OK. This works for me with the mysql tables you posted:

SELECT 
    m.title, 
    GROUP_CONCAT(DISTINCT a.actor_name) AS allactors,
    GROUP_CONCAT(DISTINCT d.director_name) AS alldirectors,
    GROUP_CONCAT(DISTINCT g.genre) AS allgenres,
    GROUP_CONCAT(DISTINCT w.writer_name) AS allwriters
FROM movies AS m 
    LEFT JOIN movie_actor AS ma ON m.movies_id = ma.movie_id 
    LEFT JOIN actors AS a ON ma.actor_id = a.id 
    LEFT JOIN movie_directors AS md ON m.movies_id = md.movie_id 
    LEFT JOIN directors AS d ON md.director_id = d.id 
    LEFT JOIN movie_genre AS mg ON m.movies_id = mg.movie_id 
    LEFT JOIN genres AS g ON mg.genre_id = g.g_id 
    LEFT JOIN movie_writers AS mw ON m.movies_id = mw.movie_id 
    LEFT JOIN writers AS w ON mw.writer_id = w.id 
GROUP BY m.movies_id

You then have this content for the allactors field (Expendables 2):

'Sylvester Stallone,Jason Statham,Jean-Claude Van Damme,Jet Li,Dolph Lundgren,Chuck Norris,Bruce Willis,Arnold Schwarzenegger,Terry Crews,Nan Yu'

So in your php, you can do this:

$allactors = array();
$alldirectors = array();
$allgenres = array();
$allwriters = array();
$data = mysql_fetch_array($r);
    if($data['allactors']) $allactors = explode(',' $data['allactors']);
    if($data['alldirectors']) $directors = explode(',' $data['alldirectors']);
    if($data['allgenres']) $genres = explode(',' $data['allgenres']);
    if($data['allwriters']) $writers = explode(',' $data['allwriters']);
    ... other fields...
}

That'll give you arrays - so easier to format the output, although if you just need a comma delimited list, then you don't need to bother to explode.

[diafol]

I came across this: http://imdbapi.org/

An API for imdb. It's not an official imdb api and only for personal use, but you may find it useful for extracting data and inserting into your own db. However, I'm not sure about the legalities of such a move. It's really fast too - have a look at some of the examples.

I know that loads of site scrape data from IMDB. THey must get a bit miffed. :(

ALso (I think) official API: http://stackoverflow.com/questions/1966503/does-imdb-provide-an-api

[vizz]

Thanks
API is helpful, but still I'm working on query.

Your last query gives Title of movie only.

Can you please help me with code???

[diafol]

Run my query in a phpmyadmin SQL window - see what it gives you.

I imported the DB from your text file, created my query and it returned what I expected. I don't know what more I can tell you.

[vizz]

Thanks

Your query worked in phpmyadmin. But when I tried it on php page it gave empty page.

How to get values inside while($row = mysql_fetch_assoc($result)) { } Using php?

$query="SELECT 
m.title, 
GROUP_CONCAT(DISTINCT a.actor_name) AS allactors,
GROUP_CONCAT(DISTINCT d.director_name) AS alldirectors,
GROUP_CONCAT(DISTINCT g.genre) AS allgenres,
GROUP_CONCAT(DISTINCT w.writer_name) AS allwriters
FROM movies AS m 
LEFT JOIN movie_actor AS ma ON m.movies_id = ma.movie_id 
LEFT JOIN actors AS a ON ma.actor_id = a.id 
LEFT JOIN movie_directors AS md ON m.movies_id = md.movie_id 
LEFT JOIN directors AS d ON md.director_id = d.id 
LEFT JOIN movie_genre AS mg ON m.movies_id = mg.movie_id 
LEFT JOIN genres AS g ON mg.genre_id = g.g_id 
LEFT JOIN movie_writers AS mw ON m.movies_id = mw.movie_id 
LEFT JOIN writers AS w ON mw.writer_id = w.id 
GROUP BY m.movies_id 
";

$result = mysql_query($query);

Having problem with following,

echo"$title<br />";
echo"$runningtime<br />";
echo"$year<br />";
echo"$description<br /><br />";
echo"$storyline<br />";
echo"$image<br />";

//actors table
echo"$actor_name<br />";

//directors table   
echo"$director_name<br />";  

//genres table
echo"$genre<br />";

//movie_actor table
echo"$movie_id<br />";
echo"$actor_id<br />";
echo"$character_name<br />";

//movie_directors table     
echo"$movie_id<br />";
echo"$director_id<br />";

//movie_genre table
echo"$movie_id<br />";
echo"$genre_id<br />";

//movie_writers table
echo"$movie_id<br />";
echo"$writer_id<br />";

//writers table
echo"$writer_name<br /><br /><hr />";

Also not working with WHERE mg.genre_id=$id

[diafol]

I can't imagine any of thiose will display as you need

echo $row['title'];

etc, unless you've used extract() ?

I don't udnerstand: echo"$actor_id<br />";

actor_id isn't in the SQL so how can you retrieve it?

[vizz]

hey thanks

It worked fine.

Sorry, again, I'm adding my problem.

1. I have little altered your query to get all info, still I can't get character_name (from table movie_actor).
   How to print character name next to the actor name respectively? ("Cast overview" like on IMDB)

2. For condition, WHERE mg.genre_id=$id, it's working well but if there are no records for certain category or genere then it prints notice: Undefined variables

3. Array of allgenres is giving only one record

New Working Query

$id=$_REQUEST['id'];

$query="SELECT m.*, 

GROUP_CONCAT(DISTINCT a.actor_name) AS allactors,
GROUP_CONCAT(DISTINCT d.director_name) AS alldirectors,
GROUP_CONCAT(DISTINCT g.genre) AS allgenres,
GROUP_CONCAT(DISTINCT w.writer_name) AS allwriters

FROM movies AS m 

LEFT JOIN movie_actor AS ma ON m.movies_id = ma.movie_id 
LEFT JOIN actors AS a ON ma.actor_id = a.id 
LEFT JOIN movie_directors AS md ON m.movies_id = md.movie_id 
LEFT JOIN directors AS d ON md.director_id = d.id 
LEFT JOIN movie_genre AS mg ON m.movies_id = mg.movie_id 
LEFT JOIN genres AS g ON mg.genre_id = g.g_id 
LEFT JOIN movie_writers AS mw ON m.movies_id = mw.movie_id 
LEFT JOIN writers AS w ON mw.writer_id = w.id 

WHERE mg.genre_id=$id

GROUP BY m.movies_id   
";

$result = mysql_query($query);

$allactors = array();
$alldirectors = array();
$allgenres = array();
$allwriters = array();

$data = mysql_fetch_assoc($result);



if($data['allactors']) $allactors = explode(',', $data['allactors']);
if($data['alldirectors']) $directors = explode(',', $data['alldirectors']);
if($data['allgenres']) $genres = explode(',', $data['allgenres']);
if($data['allwriters']) $writers = explode(',' ,$data['allwriters']);

extract($data); echo"$allactors<br />";
extract($data); echo"$alldirectors<br />";
extract($data); echo"$allgenres<br />";
extract($data); echo"$allwriters<br />";
echo"<hr />";
extract($data); echo"$title<br />";
extract($data); echo"$runningtime<br />";
extract($data); echo"$year<br />";
extract($data); echo"$description<br />";
extract($data); echo"$storyline<br />";
extract($data); echo"$image<br />";
echo"<hr />";

extract($data); echo"$character_name<br />";

[diafol]

GROUP_CONCAT(DISTINCT CONCAT(a.actor_name, '|', ma.character_name)),

Will give this output:

Sylvester Stallone|Barney Ross,Jason Statham|Lee Christmas,Jean-Claude Van Damme|Vilain,Jet Li|Yin Yang,Dolph Lundgren|Gunnar Jensen,Chuck Norris|Booker,Bruce Willis|Church,Arnold Schwarzenegger|Trench,Terry Crews|Hale Caesar,Nan Yu|Maggie

So you can explode on ',' to get the actor-character pairs (into an array) and then you can explode on '|' in order to format the data to your liking.

[vizz]

Thanks

(one more question (may be Last One))

I tried my best but I can't get actors and their characters in separate variables so that I can put Actor Name in one column and Character Name in second Column

<td>Actor Name</td>
<td>Character Name</td>

I tried to use this unserialize();, but not worked

I want to display actors and their characters in table format.

[diafol]

... GROUP_CONCAT(DISTINCT CONCAT(a.actor_name, '|', ma.character_name)) AS actchar...

Example (assuming mysql) and single film

$data = mysql_fetch_assoc($result);
$act = array();
$actchars = explode(",", $data['actchar']);
foreach($actchars as $ac) $act[] = explode("|", $ac);

...when you need it... OR you can do it with the above

$table = '<table><thead><tr><th>Actor</th><th>Character</th></tr></thead><tbody>';
foreach($act as $a) $table .= '<tr><td>' . $a[0] . '</td><td>' . $a[1] . '</td></tr>';
$table .= '</tbody></table>';

..then echo out in the right place:

echo $table;

NOT TESTED and assuming output from DB like the following:

Sylvester Stallone|Barney Ross,Jason Statham|Lee Christmas,Jean-Claude Van Damme|Vilain,Jet Li|Yin Yang,Dolph Lundgren|Gunnar Jensen,Chuck Norris|Booker,Bruce Willis|Church,Arnold Schwarzenegger|Trench,Terry Crews|Hale Caesar,Nan Yu|Maggie