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.

3 thoughts on “Movie Review Database – Not Yet

  1. Once I figured out how to connect to the database at ASO with Web Form Generator, it actually worked pretty well as a starting point, generating pages for updating, adding, listing, and a detail page. It didn’t support a login and it wouldn’t create the database.

    But ASO has a control panel, phpMyAdmin which lets you create tables, fields, etc. (and import/export their contents).

    I also had to use the Remote MySQL control panel to add my IP address to give me the ability to login remotely.

    Jeb found a movie database called php4flicks that I could probably adapt as well, though it seems to be geared more towards a DVD inventory.

  2. After tons of time trying to adapt different login scripts, I finally got something to work with the pages created by Web Form Generator. I’m sure it is not all that safe, but it does allow a login for an administrator via password and then I have a number of pages that will only show up if you are logged in as the administrator. So many scripts are worried about higher security and logging in everyone (not just administrators).

    Web Form Generator is pretty basic. It read the database and set up forms, but it didn’t differentiate between different data types. For instance the reviews are stored in a TEXT field, but WFG used a regular one-line form field and limited it to 100 characters. That worked with a lot of other fields which are fairly short strings, but not that one. Also numbers and dates got text fields as well. So I had to clean some of that up.

    I have a site that works, but it is ugly. It also is doing something where it puts a slash in front of apostrophes when I paste in a review. I feel like I got a decent exposure to MySQL and PHP, at least enough to know I don’t really want to mess with it, but could if I had to.

Leave a Reply

Your email address will not be published. Required fields are marked *