Need help for displaying movie database - Page 2

[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?