Movie Review Database – Not Yet

After getting the discussion board set up (so far no one has signed up or posted anything; not surprising given the small amount of traffic to that website) using php and MySQL, I thought it might be time to convert my movie reviews over to a MySQL database with pages served like this blog and the discussion area.

It’s a big undertaking. For one thing, you have to learn MySQL and php, which I didn’t have to do to set up the discussion board (everything was done through wizards and installers). I searched for something similar that I could just use, but didn’t find anything. I did find a book whose examples involve a movie database and thought about buying. I found a decent online tutorial and started thinking about how I would set things up.

Currently all of my movie reviews are just static html pages. I put them in folders by the year the movie was released. I have an archive showing all of the movies by year and then title. I also have a frame-based navigation system with a drop-down list showing the year and then a second drop-down list showing that year’s movies. Lastly, at the end of each year I will rank all of the movies I saw for that year. This means that when I write a new movie review I have to update the drop-down list for that year, the archive, and (if it isn’t the current year) the end of year review. Including the movie review itself, that is four files that need to be updated each time I want to post a review.

Also, the archive by year is okay, but what if you don’t know what year a movie came out? Then you pretty much have to do a search on that page since I’m up to 270 movies over a period of ten years. So it would be nice to have a list of all movies alphabetically. And it might be nice to have another list by the grades I assigned, so someone could see my favorites right off the bat. Another thing is that my year-end review pages don’t link to the movies.

Some of this stuff lends itself to databases very well. A list by movie title is a cinch, or even by the grade or year. Some things don’t: For my year-end reviews I sort out which of the many B+ movies are better than other B+ movies. Plus, sometimes when I’m looking back I might put a B+ above an A-. In HTML this is very easy, because I’m using an ordered list. So the HTML is actually generating the ranking numbers 1, 2, 3, 4, etc. I just put the movies in the order I want and it deals with the rank. That makes it really easy to add a movie or change the placement of the movie. So that would be very hard to work out with a database.

Although I’m sure it would be easy enough to deal with, another issue is in linking with other movies. While writing the review I would have to look up the id number or filename of the movie review I am referencing. That’s something that doesn’t work particularly well on this blog either. When I changed from storing blog entries by id number to title, I had to manually change all of my cross-reference links in my blog entries.

Another thing to be considered is whether to have the database build a website (similar to how Movable Type works, where each page is an actual page of html stored somewhere, updated only when you rebuild the site) or serve it up straight from the database. I don’t know a good answer to that except that the former might help when cross-referencing movies from within a review, since I would have an actual filename to link to (in fact, I was thinking the filename could be a field in the database). At work I had a Microsoft Access database of consultants and we didn’t have a way of putting that on our web server dynamically. So I came up with a macro that would generate a web page for each company, and generate an index of all companies, that could be run with a click of button. Then you just had to move all of those files over to the web server. (Eventually we would hire computer consultants to make all of this work dynamically through web pages: What took me a few days took them many months and hundreds of thousands of dollars to do, plus a new service contract any time we want to make a change. Privatization.). I thought I could do the same with the movie reviews and do away with learning the MySQL and php stuff by putting everything in MS Access and using VB scripts. But then I would have to rebuild and upload everything that had changed to the server every time I added a movie (this is what I wound up doing).

But maybe the biggest thing with a MySQL database is that you have to build pages to interact with it. I would have to have a form that allows me to add movies. Maybe another one to edit movies (could be the same as the add, or at least would be very similar). Since this is on the web, I would want a way to log in so that no one else could edit the movies. Then I would have to write up the alphabetical archive, archive by year, archive by grade, etc. Once I did it, it would update automatically, but there is a lot of overhead at the beginning. Then you need some way of exporting all of the information so that you can back it up or move it to another MySQL server.

As I started writing up some HTML forms and php by adapting code that was in the online tutorial, I realized that much of this was just a matter of changing field names and everybody who does a simple database would be doing essentially the same thing, but with different field names and types. That in itself could probably be done with php if someone had written a php/MySQL website wizard. I found Web Form Generator, a java application, that might do the trick, but haven’t tried it yet.

The other option (I’m sure Jeb has been waiting for) is to do a blog of just movie reviews. A blog might not do everything, but it would do a lot of what I’m describing without much or any programming. Each post would be a movie review, with the movie title as its subject. I could categorize each post based on the year the movie was released and/or the grade I give it. Actually, I would probably make the release date of the movie be the date the entry was posted and the category could be the grade.

For now I think I am going to continue doing this the old-fashioned way and writing up html pages.