hey thanks

[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

[vizz]

Thanks

It's all working.

I think all problems are solved, but not sure. Still working on project.

BTW

Thanks

[ackley]

This is a really good read for me. Must agree that you are one of the coolest blogger I ever saw. Thanks for posting this useful information. This was just what I was on looking for. I'll come back to this blog for sure!

[vizz]

@diafol,

I'm using your code to get data, if Genere is selected your code works well, but if there is no Genere selected then how to listout all movies?

if (empty($id)) 
    {
        $query="SELECT m.*, 

        GROUP_CONCAT(DISTINCT CONCAT(a.actor_name, ' | ', ma.character_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";
    }
else
    {
        $query=""SELECT m.*, 

        GROUP_CONCAT(DISTINCT CONCAT(a.actor_name, ' | ', ma.character_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);

$act = array();
$allactors = explode(",", $data['allactors']);
foreach($allactors as $ac) $act[] = explode("|", $ac);
$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>';


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"$alldirectors<br />";
extract($data); echo"$allgenres<br />";
extract($data); echo"$allwriters<br />";
echo"<hr />";

extract($data); echo"<img src=img/$image width=250/><br />";

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 />";
echo"<hr />";
echo $table;

[acion]

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

[acion]

This is my first time i visit here. Really this is a new idiot for me. You have described very well. I like this item very much. Thanks for sharing. Good enough, I will order the package and will mark it in the note.

[diafol]

If genre is set / not set, then do this:

if($gen_id){
    $where_clause = " WHERE genre_id = $gen_id ";
}else{
    $where = '';
}

 $query="SELECT m.*, 
    GROUP_CONCAT(DISTINCT CONCAT(a.actor_name, ' | ', ma.character_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
    GROUP BY m.movies_id";

You can use this idea on all bits of the query:

    "SELECT m.*, 
    $allactors
    $alldirectors
    $allgenres
    $allwriters
    FROM movies AS m 
    $actorjoin 
    $directorjoin 
    $genrejoin
    $writerjoin 
    $where
    GROUP BY m.movies_id";

So you don't have to build a unique query for every search combination. You'd put this into a function with various parameters:

function getData($criteria){
    ...
}

[vizz]

@diafol

Thanks
Thanks for helping again & again.

[vizz]

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

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

for actors, I want to link each actor to actordetails.php but echo"Actors:-$allactors"; this gives contineous results so I cant link each actor separately.

How to add link to each actor? How to add space after each name?

[vizz]

I made this but not working properly

for($i = 0; $i < count($allactors); $i++){
    echo "Actors:- $allactors[$i], &nbsp;";
}

[diafol]

use foreach:

$allactors = explode(',', $data['allactors']);
foreach($allactors as $actor){
    echo "Actors:- $actor,  ";
}

Why the comma?