Movies Seen Statistics

Some possibly interesting statistics about the movies I’ve seen since 2001.

Top 15 Actors Seen'; echo ''; echo ''; echo ''; echo ''; echo ''; $n=1; $actors_seen = mysql_query("SELECT actor, COUNT(movie_id) AS num_movies FROM actors GROUP BY actor HAVING num_movies > 1 ORDER BY num_movies DESC, actor LIMIT 15", $moviesdb); $num_rows = mysql_numrows($actors_seen); for ($i=0; $i < $num_rows; $i++) { $n++; $movies = mysql_query("SELECT name, release_year FROM movies, actors WHERE (movies.id = movie_id) AND (actor = '" . mysql_result($actors_seen, $i, "actor") . "') ORDER BY sort_name", $moviesdb); $num_movies = mysql_numrows($movies); echo ''; echo ''; echo ''; echo ''; } echo '
ActorNumber of Movies Seen In'; echo '
(click to see movies)
' . mysql_result($actors_seen, $i, "actor") . ''; echo ''; echo ''; echo '
'; echo '

 

'; echo '

Top 15 Directors Seen

'; echo ''; echo ''; echo ''; echo ''; echo ''; $directors_seen = mysql_query("SELECT director, COUNT(movies.name) AS num_movies FROM directors INNER JOIN movies ON directors.movie_id = movies.id GROUP BY director HAVING num_movies > 1 ORDER BY num_movies DESC, director LIMIT 15", $moviesdb); $num_rows = mysql_numrows($directors_seen); for ($i=0; $i < $num_rows; $i++) { $n++; $movies = mysql_query("SELECT name, release_year FROM movies, directors WHERE (movies.id = movie_id) AND (director = '" . mysql_result($directors_seen, $i, "director") . "') ORDER BY sort_name", $moviesdb); $num_movies = mysql_numrows($movies); echo ''; echo ''; echo ''; echo ''; } echo '
DirectorNumber of Movies Seen
' . mysql_result($directors_seen, $i, "director") . ''; echo ''; echo ''; echo '
'; echo '

 

'; echo '

Ratings Break Down

'; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; $total_movies_seen = mysql_query("SELECT COUNT(id) AS total FROM seen_dates", $moviesdb); $total_movies_seen = mysql_result($total_movies_seen, 0, "total"); //$ratings = mysql_query("SELECT ratings.number, COUNT(seen_dates.id) AS total FROM seen_dates, ratings WHERE (seen_dates.rating_id = ratings.id) GROUP BY number ORDER BY number", $moviesdb); $ratings = mysql_query("SELECT ratings.verbal, COUNT(seen_dates.id) AS total FROM seen_dates INNER JOIN ratings ON seen_dates.rating_id = ratings.id GROUP BY verbal ORDER BY number DESC", $moviesdb); while ($item = mysql_fetch_array($ratings)) { echo ''; //echo ''; echo ''; echo ''; echo ''; echo ''; } echo '
RatingNumber of MoviesPercentage
' . $item["number"] . '
' . $item["verbal"] . '
' . $item["total"] . '
' . number_format(($item["total"]/$total_movies_seen)*100, 1) . '%
'; echo '

 

'; echo '

Ratings Break Down By Letter

'; echo ''; echo ''; echo ''; echo ''; echo ''; for ($i=0; $i <= 25; $i++) { // lowercase //$char = 97 + $i; // UPPERCASE $char = 65 + $i; $letter = chr($char); $mysql = "SELECT ratings.verbal, COUNT(ratings.verbal) AS total FROM movies INNER JOIN (SELECT movie_id, max(date_seen) as max_date FROM seen_dates GROUP BY movie_id) as dummy ON movies.id = dummy.movie_id INNER JOIN seen_dates ON dummy.movie_id = seen_dates.movie_id AND dummy.max_date = seen_dates.date_seen INNER JOIN ratings ON seen_dates.rating_id = ratings.id WHERE movies.sort_name LIKE '" . $letter . "%%' GROUP BY verbal ORDER BY ratings.number"; $ratings = mysql_query($mysql); $num_rows = mysql_numrows($ratings); echo ''; echo ''; echo ''; echo ''; } echo '
Movie Starts WithRating
' . $letter . ''; for ($j=0; $j < $num_rows; $j++) { echo mysql_result($ratings, $j, "verbal") . ': ' . mysql_result($ratings, $j, "total") . '
'; } echo '
'; echo '

 

'; echo '

Movies Seen By Year

'; echo ''; echo ''; echo ''; echo ''; echo ''; $counter = 0; $total = 0; //$movies_seen_by_year = mysql_query("SELECT DATE_FORMAT(date_seen, '%Y') AS Year, COUNT(*) AS Total FROM seen_dates WHERE DATE_FORMAT(date_seen, '%Y') <> '0000' GROUP BY Year ORDER BY Year", $moviesdb); $movies_seen_by_year = mysql_query("SELECT DATE_FORMAT(date_seen, '%Y') AS Year, COUNT(*) AS Total FROM movies INNER JOIN seen_dates ON movies.id = seen_dates.movie_id WHERE DATE_FORMAT(date_seen, '%Y') <> '0000' GROUP BY Year ORDER BY Year", $moviesdb); while ($year = mysql_fetch_array($movies_seen_by_year)) { if ($year["Year"] != date("Y")) { $counter += 1; $total += $year["Total"]; } echo ''; echo ''; echo ''; echo ''; } echo ''; echo ''; echo ''; echo ''; echo '
YearNumber of Movies Seen
' . $year["Year"] . '
' . $year["Total"] . '
Average Per Year
(not including current year)
' . number_format($total/$counter, 0) . '
'; echo '

 

'; echo '

Top 15 Most Repeatedly Viewed Movies

'; echo ''; echo ''; echo ''; echo ''; echo ''; $repeatedly_seen = mysql_query("SELECT COUNT(seen_dates.id) AS total, movies.name FROM movies INNER JOIN seen_dates ON movies.id = seen_dates.movie_id GROUP BY movies.name ORDER BY total DESC, sort_name LIMIT 15", $moviesdb); while ($item = mysql_fetch_array($repeatedly_seen)) { echo ''; echo ''; echo ''; echo ''; } echo '
MovieNumber of Times Seen
' . $item["name"] . '
' . $item["total"] . '
'; echo '

 

'; echo '

Locations Seen Break Down 1

'; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; $total_movies_seen = mysql_query("SELECT COUNT(id) AS total FROM seen_dates", $moviesdb); $total_movies_seen = mysql_result($total_movies_seen, 0, "total"); $locations_seen = mysql_query("SELECT location_seen_rollup, COUNT(seen_dates.id) AS total FROM seen_dates INNER JOIN location_seen ON seen_dates.location_seen_id = location_seen.id GROUP BY location_seen_rollup ORDER BY total DESC", $moviesdb); while ($item = mysql_fetch_array($locations_seen)) { if ($item["location_seen_rollup"] <> 'n/a') { echo ''; echo ''; echo ''; echo ''; echo ''; } } echo '
Location SeenNumber of MoviesPercentage
' . $item["location_seen_rollup"] . '
' . $item["total"] . '
' . number_format(($item["total"]/$total_movies_seen)*100, 1) . '%
'; echo '

 

'; echo '

Top 15 Most Movies Seen on a Single Day

'; echo ''; echo ''; echo ''; echo ''; echo ''; $dates = mysql_query("SELECT date_seen, COUNT(movie_id) AS NumOccurrences FROM seen_dates WHERE (date_seen != '1900-01-01') AND (date_seen != '0000-00-00') GROUP BY date_seen HAVING (NumOccurrences > 1 ) ORDER BY NumOccurrences DESC LIMIT 15", $moviesdb); $num_rows = mysql_numrows($dates); for ($i=0; $i < $num_rows; $i++) { $n++; $movies = mysql_query("SELECT name, release_year FROM movies INNER JOIN seen_dates ON movies.id = movie_id WHERE date_seen = '" . mysql_result($dates, $i, "date_seen") . "' ORDER BY sort_name", $moviesdb); $num_movies = mysql_numrows($movies); echo ''; echo ''; echo ''; echo ''; } echo '
DateNumber of Movies Seen
(click to see movies)
'; echo '
' . mysql_result($dates, $i, "date_seen") . '
'; echo ''; echo ''; echo '
'; mysql_close($moviesdb); ?>

 


  1. The numbers here are a little skewed, especially movies seen in theaters. For the first few years of this database, I didn't keep track of each viewing separately; each viewing replaced the previous viewing. Therefore, if I saw a movie in the theater and subsequently watched it on HBO, the theater entry was lost, which is why there are so few theater entries.

    Additionally, DVDs have drastically altered the way I watch movies. Since viewing a movie on my home theater setup is not unlike a theater experience, and the cost of going to the theater keeps going up, my threshold of what movies I go to see in a theater has changed over the years. These days, I generally see movies in the theater when the movie going experience will be amazing in the theater that cannot be recreated at home, such as Spider-Man 3 or Pirates of the Caribbean, or if it's part of a date (which usually means a chick flick).