SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    153
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    need help with a count query

    Hi guys, just need help with a query..

    I have two tables

    genre (id, genre_name, genre_abbreviation) and usermovies (userid, movieid, status)

    status can either be 'unwatched' or 'watched'

    I am trying to do the following:

    PHP Code:
    $sql mysqli_query($link"SELECT COUNT(*) FROM usermovies WHERE status = 'watched'");
    $row mysqli_fetch_array($sql);
    if (!
    $row)
            {
                
    $error 'Error.';
                include 
    'error.php';
                exit();
            }
    $watched $row['COUNT(*)'];
    $sql mysqli_query($link"SELECT COUNT(*) FROM usermovies WHERE status = 'unwatched'");
    $row mysqli_fetch_array($sql);
    if (!
    $row)
            {
                
    $error 'Error.';
                include 
    'error.php';
                exit();
            }
    $unwatched $row['COUNT(*)']; 
    so that I can use the $watched and $unwatched variables in my display script.

    that works, but I need to add 'WHERE genre.id = x' for each of those queries so that my stats for each section are related to genre (my movies in the display page are sorted by genre) and I'm having trouble figuring out the syntax.. any tips?

    I'm sure I can also condense that code and use an array or something to get all the variables I need with one query? but I'm still very new to arrays

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I assume there's a 3rd table somewhere that links these two -- could you share the structure of that? You'll want to join the 3 tables together.

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    153
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    oh yes, duh, sorry. my bad.

    3rd table is movies (movie_id, movie_name, genre_id, movie_region, release_date)

    and my users table is users (id, username, email, password)

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT
      COUNT(*)
    FROM usermovies
    INNER JOIN movies ON usermovies.movieid = movies.movie_id
    WHERE
      usermovies.status = 'watched' 
      AND movies.genre_id = 3

  5. #5
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    153
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    Code:
    SELECT
      COUNT(*)
    FROM usermovies
    INNER JOIN movies ON usermovies.movieid = movies.movie_id
    WHERE
      usermovies.status = 'watched' 
      AND movies.genre_id = 3
    that works perfectly, but I just realized, I have to have it like this:

    Code:
    SELECT
      COUNT(*)
    FROM usermovies
    INNER JOIN movies ON usermovies.movieid = movies.movie_id
    WHERE
      usermovies.status = 'watched' 
      AND genre.genre_abbreviation = '$genre_abbreviation'
    that doesn't work though

    because I am (trying to) displaying a list of users movies with an array, sorted by genre, like this:

    Horror - watched: X, unwatched X
    movie
    movie
    movie

    Action - watched: x, unwatched X

    and I only have the $platform_abbreviation variable set before this SELECT COUNT(*) query, how can I implement that in the query you gave me?

  6. #6
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Join the genres table

    Code:
    SELECT
      COUNT(*)
    FROM usermovies
    INNER JOIN movies ON usermovies.movieid = movies.movie_id
    INNER JOIN genre ON movies.genre_id = genre.id
    WHERE
      usermovies.status = 'watched' 
      AND genre.genre_abbreviation = 'horror'

  7. #7
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    153
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    thank you! that works great and is exactly the query I need. and bonus: I think I understand how inner joins work now :P

    now I just have to figure out why my $genre_abbreviation variable is not working, that's a question for the php forum I guess.


Bookmarks

Posting Permissions

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