Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 27

Thread: pagination

  1. #1
    New Coder
    Join Date
    Feb 2013
    Posts
    32
    Thanks
    10
    Thanked 0 Times in 0 Posts

    pagination

    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:
    PHP Code:
    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  
    (11),  
    (
    21),  
    (
    12); 
    PHP Code:
        $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_query0) / $MovieListPerPage);
        
    $page             = (isSet($_GET['page']) AND (int)$_GET['page'] > ) ? (int)$_GET['page'] : 1;
        
    $start             = ($page 1) * $MovieListPerPage
    Last edited by destas; 03-10-2013 at 02:44 PM.
    Sorry for my poor english.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    Please show us what RESULT you want from the query, using the data you have shown.

    I see you want
    Code:
    'The Lorax', 'emily fun, john ugu'
    'The Test', 'john ugu'
    from the first part, but I am not sure what you want from the second part.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Feb 2013
    Posts
    32
    Thanks
    10
    Thanked 0 Times in 0 Posts
    I will give you an example:

    I click on actor name john ugu (http://www.../john ugu) and on this page i need to get/count all movies where actor john ugu was acted.
    Sorry for my poor english.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    That is trivial:
    Code:
    SELECT COUNT(*) 
    FROM movieactors, actors
    WHERE movieactors.actor_id = actors.actor_id
      AND actors.actor_name = 'john ugu'
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Feb 2013
    Posts
    32
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Can you explain for me why when i use WHERE a.actor_name = 'john ugu' all movies from list have only one actor (john ugu)? How to get movies where john ugu was acted and all genres/actors?

    PHP Code:
    SELECT m.idm.titlem.year,
    GROUP_CONCAT(DISTINCT g.genre_name SEPARATOR ', ') AS genres
    GROUP_CONCAT(DISTINCT a.actor_name SEPARATOR ', ') AS actors 
    FROM movies m 
    INNER JOIN moviegenres mg ON 
    (mg.movie_id m.id
    INNER JOIN genres g ON (g.genre_id mg.genre_id
    INNER JOIN movieactors ma ON (ma.movie_id m.id
    INNER JOIN actors a ON (a.actor_id ma.actor_id
    WHERE a.actor_name '%s' 
    LIMIT %d, %d
    Sorry for my poor english.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    You asked that question before and I thought I told you.

    When you use WHERE a.actor_name = 'John Ugu' then *OF COURSE* you can not get any records where a.actor_name is ANYTHING ELSE.

    I *THINK* what you are after is this:
    Code:
    SELECT m.id, m.title, m.year, 
    GROUP_CONCAT(DISTINCT g.genre_name SEPARATOR ', ') AS genres,  
    GROUP_CONCAT(DISTINCT a.actor_name SEPARATOR ', ') AS actors  
    FROM movies m  
    INNER JOIN moviegenres mg ON (mg.movie_id = m.id)  
    INNER JOIN genres g ON (g.genre_id = mg.genre_id)  
    INNER JOIN movieactors ma ON (ma.movie_id = m.id)  
    INNER JOIN actors a ON (a.actor_id = ma.actor_id)  
    INNER JOIN ( 
        movieactors AS ma2 INNER JOIN actors AS a2
        ON ma2.actor_id = a2.actor_id
        AND a2.actor_name = 'John Ugu' ) AS X
    ON X.movie_id = m.id
    LIMIT %d, %d
    You see that? You must have a *SEPARATE QUERY* (or at least a separate *part* to the query) that gets you all the movie id's where 'John Ugu' acted and *THEN* you can do your main query.

    And alternative form of that which might be easier to understand:
    Code:
    SELECT m.id, m.title, m.year, 
    GROUP_CONCAT(DISTINCT g.genre_name SEPARATOR ', ') AS genres,  
    GROUP_CONCAT(DISTINCT a.actor_name SEPARATOR ', ') AS actors  
    FROM movies m  
    INNER JOIN moviegenres mg ON (mg.movie_id = m.id)  
    INNER JOIN genres g ON (g.genre_id = mg.genre_id)  
    INNER JOIN movieactors ma ON (ma.movie_id = m.id)  
    INNER JOIN actors a ON (a.actor_id = ma.actor_id)  
    WHERE m.id IN (
        SELECT ma2.movie_id
        FROM movieactors AS ma2 INNER JOIN actors AS a2
        ON ma2.actor_id = a2.actor_id
        AND a2.actor_name = 'John Ugu' ) 
    LIMIT %d, %d
    but *probably* the first form will execute faster in MySQL. (SQL Server would see the two as equivalent.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New Coder
    Join Date
    Feb 2013
    Posts
    32
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Doesn't work with this query.
    PHP Code:
    SELECT m.idm.titlem.year
    GROUP_CONCAT(DISTINCT g.genre_name SEPARATOR ', ') AS genres,  
    GROUP_CONCAT(DISTINCT a.actor_name SEPARATOR ', ') AS actors  
    FROM movies m  
    INNER JOIN moviegenres mg ON 
    (mg.movie_id m.id)  
    INNER JOIN genres g ON (g.genre_id mg.genre_id)  
    INNER JOIN movieactors ma ON (ma.movie_id m.id)  
    INNER JOIN actors a ON (a.actor_id ma.actor_id)  
    INNER JOIN 
        
    movieactors AS ma2 INNER JOIN actors AS a2
        ON ma2
    .actor_id a2.actor_id
        
    AND a2.actor_name 'John Ugu' ) AS X
    ON X
    .movie_id m.id
    LIMIT 
    %d, %

    With this query show only one movie.. But i'm not sure that problem is in query.
    PHP Code:
    SELECT m.idm.titlem.year
    GROUP_CONCAT(DISTINCT g.genre_name SEPARATOR ', ') AS genres,  
    GROUP_CONCAT(DISTINCT a.actor_name SEPARATOR ', ') AS actors  
    FROM movies m  
    INNER JOIN moviegenres mg ON 
    (mg.movie_id m.id)  
    INNER JOIN genres g ON (g.genre_id mg.genre_id)  
    INNER JOIN movieactors ma ON (ma.movie_id m.id)  
    INNER JOIN actors a ON (a.actor_id ma.actor_id)  
    WHERE m.id IN (
        
    SELECT ma2.movie_id
        FROM movieactors 
    AS ma2 INNER JOIN actors AS a2
        ON ma2
    .actor_id a2.actor_id
        
    AND a2.actor_name 'John Ugu' 
    LIMIT %d, %

    My previous DB was in one table
    PHP Code:
    (`id`, `title`, `year`, `genres`, `actors`)
    (
    1'The Lorax'2012'comedy, fantasy''john ugu, emily fun' ),
    (
    2'The test'2013'horror''rembo'); 
    For pagination i use
    PHP Code:
    SELECT COUNT(actorsfrom `moviedbWHERE `actorsLIKE '$actor_name' 
    For movie list of current actor (john ugu)
    PHP Code:
    SELECT FROM moviedb WHERE actors LIKE '%s' LIMIT %d, %d
    Sorry for my poor english.

  • #8
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Quote Originally Posted by destas;1319903

    [B
    My previous DB was in one table[/B]
    PHP Code:
    (`id`, `title`, `year`, `genres`, `actors`)
    (
    1'The Lorax'2012'comedy, fantasy''john ugu, emily fun' ),
    (
    2'The test'2013'horror''rembo'); 
    For pagination i use
    PHP Code:
    SELECT COUNT(actorsfrom `moviedbWHERE `actorsLIKE '$actor_name' 
    For movie list of current actor (john ugu)
    PHP Code:
    SELECT FROM moviedb WHERE actors LIKE '%s' LIMIT %d, %d
    What does this have with anything ? It is depreceated, not used, as I see ?

    Google for SQL_CALC_FOUND_ROWS, that is used to get total number of records regardles of limit.
    Last edited by BubikolRamios; 03-12-2013 at 06:47 PM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    Okay...I actually create a set of DB tables to test this. Here's a dump of all my tables:
    Code:
    mysql> select * from movies;
    +----+--------------------+------+------------+------+---------------------+
    | id | title              | year | premiere   | imdb | added               |
    +----+--------------------+------+------------+------+---------------------+
    |  1 | kink kong          | 1939 | 0000-00-00 | xyz2 | 2013-02-17 16:00:52 |
    |  2 | 2001 space odyssey | 1967 | 0000-00-00 | abc3 | 2013-02-17 16:01:51 |
    |  3 | zombies            | 2012 | 0000-00-00 | jjj8 | 2013-02-17 16:01:51 |
    +----+--------------------+------+------------+------+---------------------+
    3 rows in set (0.00 sec)
    
    mysql> select * from actors;
    +----------+----------------+
    | actor_id | actor          |
    +----------+----------------+
    |        1 | Alan Arkin     |
    |        2 | Bela Lugosi    |
    |        3 | Carmen Miranda |
    |        4 | Don Knotts     |
    +----------+----------------+
    4 rows in set (0.00 sec)
    
    mysql> select * from genres;
    +----+---------+
    | id | genre   |
    +----+---------+
    |  1 | comedy  |
    |  2 | fantasy |
    |  3 | horror  |
    +----+---------+
    3 rows in set (0.00 sec)
    
    mysql> select * from movieactors;
    +----------+----------+
    | movie_id | actor_id |
    +----------+----------+
    |        1 |        1 |
    |        1 |        4 |
    |        2 |        2 |
    |        3 |        2 |
    |        3 |        3 |
    |        3 |        4 |
    +----------+----------+
    6 rows in set (0.00 sec)
    
    mysql> select * from moviegenres;
    +----------+----------+
    | movie_id | genre_id |
    +----------+----------+
    |        1 |        1 |
    |        1 |        2 |
    |        2 |        3 |
    |        3 |        3 |
    +----------+----------+
    4 rows in set (0.00 sec)
    And then I used this query:
    Code:
    SELECT m.id, m.title, m.year, 
    GROUP_CONCAT(DISTINCT g.genre SEPARATOR ', ') AS genres,  
    GROUP_CONCAT(DISTINCT a.actor SEPARATOR ', ') AS actors  
    FROM movies m  
    INNER JOIN moviegenres mg ON (mg.movie_id = m.id)  
    INNER JOIN genres g ON (g.id = mg.genre_id)  
    INNER JOIN movieactors ma ON (ma.movie_id = m.id)  
    INNER JOIN actors a ON (a.actor_id = ma.actor_id)  
    WHERE m.id IN (
        SELECT ma2.movie_id
        FROM movieactors AS ma2 INNER JOIN actors AS a2
        ON ma2.actor_id = a2.actor_id
        AND a2.actor = 'bela lugosi')
    GROUP BY m.id, m.title, m.year;
    And got these results:
    Code:
    +----+--------------------+------+--------+-----------------------------------------+
    | id | title              | year | genres | actors                                  |
    +----+--------------------+------+--------+-----------------------------------------+
    |  2 | 2001 space odyssey | 1967 | horror | Bela Lugosi                             |
    |  3 | zombies            | 2012 | horror | Don Knotts, Carmen Miranda, Bela Lugosi |
    +----+--------------------+------+--------+-----------------------------------------+
    Which are correct.

    So I think the only thing wrong with that code is you are missing the GROUP BY! That's why you only got 1 movie.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    destas (03-15-2013)

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    In other words, use this:
    Code:
    SELECT m.id, m.title, m.year, 
    GROUP_CONCAT(DISTINCT g.genre_name SEPARATOR ', ') AS genres,  
    GROUP_CONCAT(DISTINCT a.actor_name SEPARATOR ', ') AS actors  
    FROM movies m  
    INNER JOIN moviegenres mg ON (mg.movie_id = m.id)  
    INNER JOIN genres g ON (g.genre_id = mg.genre_id)  
    INNER JOIN movieactors ma ON (ma.movie_id = m.id)  
    INNER JOIN actors a ON (a.actor_id = ma.actor_id)  
    WHERE m.id IN (
        SELECT ma2.movie_id
        FROM movieactors AS ma2 INNER JOIN actors AS a2
        ON ma2.actor_id = a2.actor_id
        AND a2.actor_name = 'John Ugu' ) 
    GROUP BY m.id, m.title, m.year
    LIMIT %d, %d
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    destas (03-15-2013)

  • #11
    New Coder
    Join Date
    Feb 2013
    Posts
    32
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Forget about it Ok i have a last question.

    Trying to make search. How to get FROM 3 tables ?
    PHP Code:
    "SELECT * FROM `movies` WHERE `title` = '%s' 
    PHP Code:
    OR `actor_name` = '%s' OR `genre_name` = '%s' LIMIT %d, %d;
    Sorry for my poor english.

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    Just make the query even uglier.
    Code:
    SELECT m.id, m.title, m.year, 
    GROUP_CONCAT(DISTINCT g.genre_name SEPARATOR ', ') AS genres,  
    GROUP_CONCAT(DISTINCT a.actor_name SEPARATOR ', ') AS actors  
    FROM movies m  
    INNER JOIN moviegenres mg ON (mg.movie_id = m.id)  
    INNER JOIN genres g ON (g.genre_id = mg.genre_id)  
    INNER JOIN movieactors ma ON (ma.movie_id = m.id)  
    INNER JOIN actors a ON (a.actor_id = ma.actor_id)  
    WHERE m.id IN (
        SELECT ma2.movie_id
        FROM movieactors AS ma2 INNER JOIN actors AS a2
        ON ma2.actor_id = a2.actor_id
        AND a2.actor_name = '%s' ) 
    OR m.id IN (
        SELECT ma2.movie_id
        FROM moviegenres AS mg2 INNER JOIN genres AS g2
        ON mg2.genre_id = g2.gener_id
        AND g2.genre_name = '%s' ) 
    GROUP BY m.id, m.title, m.year
    LIMIT %d, %d
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    destas (03-15-2013)

  • #13
    New Coder
    Join Date
    Feb 2013
    Posts
    32
    Thanks
    10
    Thanked 0 Times in 0 Posts
    tried to make pagination for search.

    From:
    PHP Code:
    SELECT COUNT(*) 
    FROM movieactorsactors
    WHERE movieactors
    .actor_id actors.actor_id
    AND actors.actor_name '$actor_name' 
    To:
    PHP Code:
    SELECT COUNT(*) 
    FROM moviesmovieactorsactorsmoviegenresgenres
    WHERE movies
    .title '$movie_name'
    OR movieactors.actor_id actors.actor_id
    AND actors.actor_name '$actor_name'
    OR moviegenres.genre_id genres.genre_id
    AND genres.genre_name '$genre_name' 
    And i get some error
    Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 1251362 bytes)
    Last edited by destas; 03-15-2013 at 12:57 PM.
    Sorry for my poor english.

  • #14
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    restart php editor, that should help.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #15
    New Coder
    Join Date
    Feb 2013
    Posts
    32
    Thanks
    10
    Thanked 0 Times in 0 Posts
    same...
    Sorry for my poor english.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •