SitePoint Sponsor

User Tag List

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

    Possible to order by count somehow? Need help with a mysql query.

    Hi.

    I have two tables set up like this:

    movies (
    moviename varchar(80) NOT NULL,
    genreid INT NOT NULL,
    moviestatus varchar(10) NOT NULL,
    )

    genres (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    genre_name varchar(255) DEFAULT NULL,
    )

    I have a query I am trying to solve, it is currently like this:

    Code MySQL:
    SELECT movies.moviename, movies.moviestatus, genres.genre_name FROM movies INNER JOIN genres ON movies.genreid=genres.id AND userid= ' " . intval($userid) . " ' ORDER BY $variable1, $variable2

    $variable1 and $variable2 are dependent upon the users settings. The query works fine and displays the users list of movies: they get sorted into a list of genre_name categories, ascending or descending etc depending upon variable1/2.

    variable1 sorts which order the genres are displayed. variable2 sorts which order the movies under each genre are displayed, based on the 'moviestatus' database entry, it has "watched" and "unwatched" etc as options.

    But I am trying to add an extra user option. I want each genre_name sorted by which has the most amount of 'watched' (moviestatus) 'moviename' to least.

    I am at a complete loss as to how to go about that, after guessing/reading a bit over the past couple of days. How would I need to change my current query to get that result? Can I just do a different type of ORDER BY so as to keep using my variable1/2 setup, or do I need to change more of the query than that?

    Thanks for any tips.

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,014
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Please note this hasn't been tested:

    Code SQL:
    SELECT
          movies.moviename
        , movies.moviestatus
        , genres.genre_name
        , movie_watch_count.genre_watched AS times_watched
    FROM
        movies
            INNER JOIN genres ON movies.genreid=genres.id
        INNER JOIN
            (
                SELECT
                      COUNT(genre_name) AS genre_watched
                    , genres.id AS count_id
                FROM
                    genres
                    INNER JOIN genres ON movies.genreid = genres.id
                WHERE
                    movies.moviestatus = 'watched'
            ) AS movie_watch_count
        ON genres.id = movie_watch_count.count_id        
    WHERE
        userid= ' " . intval($userid) . " '
    ORDER BY
          times_watched
        , $variable1
        , $variable2
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    140
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you so much, I can tell I'm in over my head. But I tested that out just as a mysql query, not in my php code, and it is giving me the error "Not unique table/alias: 'genres'.

    Going to re-type to make sure I got it right as well.

  4. #4
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    140
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Re-typed it and triple checked and am still getting this error:

    ERROR 1066 (42000): Not unique table/alias: 'genres'.

    Any ideas as to why that might be occurring?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    the problem is here --
    Code:
     FROM genres
    INNER JOIN genres 
      ON movies.genreid = genres.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    140
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Just wanted to say thanks for pointing me in the right direction guys. I am conceding defeat on this one, it's just a bit over my head with how much I know at this point, and I don't mind admitting that. I barely understand my original query!

    But I am keeping this thread bookmarked for when I learn more, and I will definitely come back to it as it is a feature I would like to add.

    Seriously thank you though, I sincerely appreciate the help.

  7. #7
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,014
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    @oknow ; when you next near your local libaray or bookstore, have a flick through a book called "Simply SQL" (http://www.sitepoint.com/store/simply-sql/), it's a good book for learning SQL.

    Code:
    SELECT
          movies.moviename
        , movies.moviestatus
        , genres.genre_name
        , movie_watch_count.genre_watched AS times_watched
    FROM
        movies
            INNER JOIN genres ON movies.genreid=genres.id
        INNER JOIN
            (
                SELECT
                      COUNT(genre_name) AS genre_watched
                    , genres.id AS count_id
                FROM
                    genres
                    INNER JOIN movies ON movies.genreid = genres.id #the change is this line
                WHERE
                    movies.moviestatus = 'watched'
            ) AS movie_watch_count
        ON genres.id = movie_watch_count.count_id        
    WHERE
        userid= ' " . intval($userid) . " '
    ORDER BY
          times_watched
        , $variable1
        , $variable2
    As for the query, change it to the above, I've marked the line that has been changed
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  8. #8
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    140
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    @oknow ; when you next near your local libaray or bookstore, have a flick through a book called "Simply SQL" (http://www.sitepoint.com/store/simply-sql/), it's a good book for learning SQL.

    As for the query, change it to the above, I've marked the line that has been changed
    Late response but thanks for the tip, I will just go ahead and buy it soon. Never had a bad sitepoint book!

    And thanks for the query update there, I will try it out.


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
  •