Some possibly interesting statistics about the movies I’ve seen since 2001.
Top 15 Actors Seen';
echo '
';
echo '';
echo '| Actor | ';
echo 'Number of Movies Seen In';
echo ' (click to see movies) | ';
echo '
';
$n=1;
$actors_seen = mysql_query("SELECT actor, COUNT(actors.movie_id) AS num_movies FROM actors, movies, seen_dates WHERE (actors.movie_id = movies.id) AND (movies.id = seen_dates.movie_id) AND (seen_dates.date_seen between '2016-01-01' and '2016-12-31') GROUP BY actor HAVING num_movies >= 6 ORDER BY num_movies DESC, actor", $moviesdb);
$num_rows = mysql_numrows($actors_seen);
for ($i=0; $i < $num_rows; $i++) {
$n++;
$movies = mysql_query("SELECT movies.name, movies.id, movies.release_year FROM movies, actors, seen_dates WHERE (movies.id = actors.movie_id) AND (actor = '" . mysql_result($actors_seen, $i, "actor") . "') AND (movies.id = seen_dates.movie_id) AND (seen_dates.date_seen between '2016-01-01' and '2016-12-31') GROUP BY movies.name", $moviesdb);
$num_movies = mysql_numrows($movies);
echo '';
echo '| ' . mysql_result($actors_seen, $i, "actor") . ' | ';
echo '';
echo '';
echo '';
echo ' ';
for ($j=0; $j < $num_movies; $j++) {
echo "- " . mysql_result($movies, $j, "name") . " (" . mysql_result($movies, $j, "release_year") . ")
";
}
echo ' ';
echo ' | ';
echo '
';
}
echo '
';
echo '
';
echo '
Top 15 Directors Seen
';
echo '
';
echo '';
echo '| Director | ';
echo 'Number of Movies Seen | ';
echo '
';
$directors_seen = mysql_query("SELECT director, COUNT(directors.movie_id) AS num_movies FROM directors, movies, seen_dates WHERE (directors.movie_id = movies.id) AND (movies.id = seen_dates.movie_id) AND (seen_dates.date_seen between '2016-01-01' and '2016-12-31') GROUP BY director HAVING num_movies >= 3 ORDER BY num_movies DESC, director", $moviesdb);
$num_rows = mysql_numrows($directors_seen);
for ($i=0; $i < $num_rows; $i++) {
$n++;
$movies = mysql_query("SELECT movies.name, movies.id, movies.release_year FROM movies, directors, seen_dates WHERE (movies.id = directors.movie_id) AND (director = '" . mysql_result($directors_seen, $i, "director") . "') AND (movies.id = seen_dates.movie_id) AND (seen_dates.date_seen between '2016-01-01' and '2016-12-31') GROUP BY movies.name", $moviesdb);
$num_movies = mysql_numrows($movies);
echo '';
echo '| ' . mysql_result($directors_seen, $i, "director") . ' | ';
echo '';
echo '';
echo '';
echo ' ';
for ($j=0; $j < $num_movies; $j++) {
echo "- " . mysql_result($movies, $j, "name") . " (" . mysql_result($movies, $j, "release_year") . ")
";
}
echo ' ';
echo ' | ';
echo '
';
}
echo '
';
echo '
';
echo '
Top 15 Writers Seen
';
echo '
';
echo '';
echo '| Director | ';
echo 'Number of Movies Seen | ';
echo '
';
$writers_seen = mysql_query("SELECT writer, COUNT(writers.movie_id) AS num_movies FROM writers, movies, seen_dates WHERE (writers.movie_id = movies.id) AND (movies.id = seen_dates.movie_id) AND (seen_dates.date_seen between '2016-01-01' and '2016-12-31') GROUP BY writer HAVING num_movies >= 4 ORDER BY num_movies DESC, writer", $moviesdb);
$num_rows = mysql_numrows($writers_seen);
for ($i=0; $i < $num_rows; $i++) {
$n++;
$movies = mysql_query("SELECT movies.name, movies.id, movies.release_year FROM movies, writers, seen_dates WHERE (movies.id = writers.movie_id) AND (writer = '" . mysql_result($writers_seen, $i, "writer") . "') AND (movies.id = seen_dates.movie_id) AND (seen_dates.date_seen between '2016-01-01' and '2016-12-31') GROUP BY movies.name", $moviesdb);
$num_movies = mysql_numrows($movies);
echo '';
echo '| ' . mysql_result($writers_seen, $i, "writer") . ' | ';
echo '';
echo '';
echo '';
echo ' ';
for ($j=0; $j < $num_movies; $j++) {
echo "- " . mysql_result($movies, $j, "name") . " (" . mysql_result($movies, $j, "release_year") . ")
";
}
echo ' ';
echo ' | ';
echo '
';
}
echo '
';
echo '
';
echo '
Ratings Break Down
';
echo '
';
echo '';
echo '| Rating | ';
echo 'Number of Movies | ';
echo 'Percentage | ';
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 '' . $item["number"] . ' | ';
echo '' . $item["verbal"] . ' | ';
echo '' . $item["total"] . ' | ';
echo '' . number_format(($item["total"]/$total_movies_seen)*100, 1) . '% | ';
echo '
';
}
echo '
';
echo '
';
echo '
Movies Seen By Year 1
';
echo '
';
echo '';
echo '| Year | ';
echo 'Number of Movies Seen | ';
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 '' . $year["Year"] . ' | ';
echo '' . $year["Total"] . ' | ';
echo '
';
}
echo '';
echo 'Average Per Year
(not including current year) | ';
echo '' . number_format($total/$counter, 0) . ' | ';
echo '
';
echo '
';
echo '
';
echo '
Top 15 Most Repeatedly Viewed Movies
';
echo '
';
echo '';
echo '| Movie | ';
echo 'Number of Times Seen | ';
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 '| ' . $item["name"] . ' | ';
echo '' . $item["total"] . ' | ';
echo '
';
}
echo '
';
echo '
';
echo '
Locations Seen Break Down 2
';
echo '
';
echo '';
echo '| Location Seen | ';
echo 'Number of Movies | ';
echo 'Percentage | ';
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 '| ' . $item["location_seen_rollup"] . ' | ';
echo '' . $item["total"] . ' | ';
echo '' . number_format(($item["total"]/$total_movies_seen)*100, 1) . '% | ';
echo '
';
}
}
echo '
';
echo '
';
echo '
Top 15 Most Movies Seen on a Single Day
';
echo '
';
echo '';
echo '| Date | ';
echo 'Number of Movies Seen (click to see movies) ';
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 '' . mysql_result($dates, $i, "date_seen") . ' | ';
echo '';
echo '';
echo '';
echo ' ';
for ($j=0; $j < $num_movies; $j++) {
echo "- " . mysql_result($movies, $j, "name") . " (" . mysql_result($movies, $j, "release_year") . ")
";
}
echo ' ';
echo ' | ';
echo '
';
}
echo '
';
mysql_close($moviesdb);
?>
- 2002 is way over the average because I was out of work for six months and there is absolutely nothing on daytime TV. Not really sure why so few in 2006. ↩
-
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). ↩
