I have two tables set up like this:
moviename varchar(80) NOT NULL,
genreid INT NOT NULL,
moviestatus varchar(10) NOT NULL,
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.