Pagination

Discussion in 'MySQL' started by rereze, Mar 10, 2013.

  1. #1
    Hello, can anyone help me with pagination?
    How it must work: I need to count movies where one of the actor have acted in this movie.

    i have 3 tables:
    INSERT INTO `movies` (`id`, `title`, `year`) VALUES
    (1, 'The Lorax', 2012),
    (2, 'The test', 2013);
     
    INSERT INTO `actors` (`actor_id`, `actor_name`) VALUES
    (1, 'john ugu'),
    (2, 'emily fun');
     
    INSERT INTO `movieactors` (`movie_id`, `actor_id`) VALUES
    (1, 1),
    (2, 1),
    (1, 2); 
    Code (markup):
    $page_query    = mysql_query("SELECT m.id, 
                    GROUP_CONCAT(DISTINCT a.actor_name ORDER BY a.actor_name) AS actors 
                    FROM movies m 
                    INNER JOIN movieactors mg ON (mg.actor_id = m.id)
                    INNER JOIN actors g ON (g.actor_id = mg.genre_id)
                    INNER JOIN
                    (
                    SELECT actor_name FROM movieactors
                    WHERE actor_name = '$actor_name'
                    ) as act ON ma.actor_name = act.actor_name
                    WHERE a.actor_name <> '$actor_name'
                    GROUP BY m.id 
                    ORDER BY COUNT(DISTINCT a.actor_name) DESC, m.id");
        $pages            = ceil(mysql_result($page_query, 0) / $MovieListPerPage);
        $page            = (isSet($_GET['page']) AND (int)$_GET['page'] > 0 ) ? (int)$_GET['page'] : 1;
        $start            = ($page - 1) * $MovieListPerPage;
    Code (markup):

     
    rereze, Mar 10, 2013 IP
  2. gandalf117

    gandalf117 Active Member

    Messages:
    111
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #2
    I don't understand your question. You need the count of movies where an actor has acted or where a specific actor has acted?
     
    gandalf117, Mar 25, 2013 IP