Movie Review Database

I had thought about converting my movie reviews, currently a bunch of HTML web pages, to a database using MySQL and php. Then I thought I could just use Microsoft Access which I’m pretty familiar with to create HTML pages. I had done something similar at work where we had a database of companies and I was able to create an index page and then separate pages for each company. Then we would move the pages generated to our website. That site has since been replaced by a dynamic web-based application under a (very expensive) contract.

Right now I have an archive of every movie review by the year the movie was released. I am also using frames to navigate by using two frames: one with a drop down list of years and another frame with a list of movies for that year. To make it easy, I just have a separate HTML file for each year that consists of a drop-down list and is loaded when a year is selected. One other thing I do at the end of each year is generate a ranked list of all the movies I saw that year. That’s easy in HTML, but the tricky part is that I break ties and sometimes rank independently of whatever grade I originally gave the movie (so I might have a B- ahead of a B movie once I think about it). On that ranking page I give a short summary of my review for each movie. Using an ordered list, it is easy for me to insert a movie, since I am not coming up with the ranking numbers, but letting the HTML do it based on where the movie falls in the list. The HTML assigns the numbers, not me. This makes it really easy to add a movie to the list or change places of a movie.

To deal with that in the Access database I went through and gave each movie a ranking score. So that I would have room in the future to add movies, I multiplied the rank by 10. Therefore the top ranked movie gets a 10, the second a 20, and so on. If I see a fantastic movie in that year later on, I can give it a 5 so that it will be ranked at the top.

I wanted a couple of additional ways to list movies. One would be an alphabetical list of all movies I have ever reviewed, instead of them being divided by year like they are now. The problem with this is movies that start with “A,” “An,” or “The.” In my query I created a new field based on the title of the movie but with some If formulas that would strip off the initial articles as appropriate. I had not done this on the project at work and certain companies that started with “The” would just appear under “T”. When the contractors re-created the database, they duplicated this bug. As with my old database I set up a list of letter links at the top of the page that would navigate to any letter heading in the list. Each letter represented has its own heading. The only problem with this is that I don’t have a movie starting with “X” so the X at the top doesn’t link to anything. Also movies that start with a number filter to the top and each number gets its own heading. So “8 Mile” is listed under the heading “8” all by itself.

Another neat thing I could do with the new system was have Top 10 lists for each year. I had this page generated at the same time as I generate the Year in Review pages for each individual year but the Top 10 page would skip all but the first 10 movies in a year. Access has no problem writing to two different files at once, so I am writing to the page with Top 10’s at the same time I am generating (and opening and closing) the Year in Review pages for each year.

One other thing I wanted was a list of all the movies separated not by year, but by the grade I originally gave the movie. That way all the A’s would be together, the A-‘s, etc. This was a little tricky because alphabetically, A comes before A- and A+, but it would be bad to have that be the order. Instead I made a query that would take the ASCII value of the leftmost letter of a grade, multiply it by 10, and then add 3 if the rightmost letter is a + and add 9 if it is a -. Otherwise it adds 6 (which is good because for a single-letter grade like “B” the rightmost character is “B”). For instance the ASCII code for B is 660, so a B+ gets a code of 6603, a B is 6606, and a B- is 6609. These are all higher than the A’s which are based on 650. Once again, while reading through the sorted list, a header is generated for each different grade so that the A’s are separated from the A-‘s. At the top of the page I wanted to generate a row of the different grades for easy navigation (for years or letters it was easy to set up a loop from 1998 to 2007 or A to Z that would generate each link). Rather than a loop, I just wrote a line of code for each letter grade, but it was easy to copy and paste the other grades after A.

Writing the code in Access is very tedious which made me realize that php has an advantage in being able to use templates. With php, you basically write up a web page, reference the database, and have it insert data wherever you want into your web page. But with what I’m doing, I actually generate every piece of HTML by using PRINT functions in my VB code. So each piece of HTML is coded in. I’m not sure how Movable Type works, but it has templates and when you click “Build” it generates static HTML pages for the whole blog. I think that takes a lot of the load off of the database server since the database isn’t used to generate pages for each visitor, the pages are already made in advance by the time a visitor drops by.

I still don’t have everything quite like I want, but I did go ahead and transfer some of the different lists to my movie review website. 254 reviews!