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(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 '| ' . 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(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 '| ' . 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 '
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 '
Ratings Break Down By Letter
';
echo '
';
echo '';
echo '| Movie Starts With | ';
echo 'Rating | ';
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 '| ' . $letter . ' | ';
echo '';
for ($j=0; $j < $num_rows; $j++) {
echo mysql_result($ratings, $j, "verbal") . ': ' . mysql_result($ratings, $j, "total") . '
';
}
echo ' | ';
echo '
';
}
echo '
';
echo '
';
echo '
Movies Seen By Year
';
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 1
';
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);
?>
-
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). ↩
