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

Databases
1

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:

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

Please note this hasn’t been tested:

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
3

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

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

the problem is here –

 FROM genres
INNER JOIN genres
  ON movies.genreid = genres.id
6

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! :slight_smile:

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

@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.

[COLOR=#993333][B]SELECT[/B][/COLOR]
      movies[COLOR=#66cc66].[/COLOR]moviename
    [COLOR=#66cc66],[/COLOR] movies[COLOR=#66cc66].[/COLOR]moviestatus
    [COLOR=#66cc66],[/COLOR] genres[COLOR=#66cc66].[/COLOR]genre_name
    [COLOR=#66cc66],[/COLOR] movie_watch_count[COLOR=#66cc66].[/COLOR]genre_watched [COLOR=#993333][B]AS[/B][/COLOR] times_watched
[COLOR=#993333][B]FROM[/B][/COLOR]
    movies
        [COLOR=#993333][B]INNER[/B][/COLOR] [COLOR=#993333][B]JOIN[/B][/COLOR] genres [COLOR=#993333][B]ON[/B][/COLOR] movies[COLOR=#66cc66].[/COLOR]genreid[COLOR=#66cc66]=[/COLOR]genres[COLOR=#66cc66].[/COLOR]id
    [COLOR=#993333][B]INNER[/B][/COLOR] [COLOR=#993333][B]JOIN[/B][/COLOR]
        [COLOR=#66cc66]([/COLOR]
            [COLOR=#993333][B]SELECT[/B][/COLOR]
                  [COLOR=#993333][B]COUNT[/B][/COLOR][COLOR=#66cc66]([/COLOR]genre_name[COLOR=#66cc66])[/COLOR] [COLOR=#993333][B]AS[/B][/COLOR] genre_watched
                [COLOR=#66cc66],[/COLOR] genres[COLOR=#66cc66].[/COLOR]id [COLOR=#993333][B]AS[/B][/COLOR] count_id
            [COLOR=#993333][B]FROM[/B][/COLOR]
                genres
                [B][COLOR=#993333][B]INNER[/B][/COLOR] [COLOR=#993333][B]JOIN[/B][/COLOR] movies [COLOR=#993333][B]ON[/B][/COLOR] movies[COLOR=#66cc66].[/COLOR]genreid [COLOR=#66cc66]=[/COLOR] genres[COLOR=#66cc66].[/COLOR]id[/B] #the change is this line
            [COLOR=#993333][B]WHERE[/B][/COLOR]
                movies[COLOR=#66cc66].[/COLOR]moviestatus [COLOR=#66cc66]=[/COLOR] [COLOR=#ff0000]'watched'[/COLOR]
        [COLOR=#66cc66])[/COLOR] [COLOR=#993333][B]AS[/B][/COLOR] movie_watch_count
    [COLOR=#993333][B]ON[/B][/COLOR] genres[COLOR=#66cc66].[/COLOR]id [COLOR=#66cc66]=[/COLOR] movie_watch_count[COLOR=#66cc66].[/COLOR]count_id        
[COLOR=#993333][B]WHERE[/B][/COLOR]
    userid[COLOR=#66cc66]=[/COLOR] [COLOR=#ff0000]' " . intval($userid) . " '[/COLOR]
[COLOR=#993333][B]ORDER[/B][/COLOR] [COLOR=#993333][B]BY[/B][/COLOR]
      times_watched
    [COLOR=#66cc66],[/COLOR] $variable1
    [COLOR=#66cc66],[/COLOR] $variable2

As for the query, change it to the above, I’ve marked the line that has been changed

8

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.