Help me choose an upgrade path for my vintage site and get my info to a database?

June 21, 2012 6:02 PM

I'm looking for some guidance in my quest to enter a moderate amount of information into a database, and then choose an appropriate front-end to display that data with a small array of filters and search options. The project is not complicated; I mostly want to avoid wasting time learning things the hard way (in contrast to my usual style).

Short background info: about 15 years ago, I began to accumulate a large number of live recordings of Robert Hunter, which is a fairly niche hobby, even in the world of Deadheads. I ended up creating a simple website to list all his known tour dates and setlists, along with recording info and other miscellaneous notes/details. Although I haven't made any significant updates for over a decade, I'm now sitting on a ton of new and corrected information, and I'd like to bring this little hobby site into the 21st century.

My first question is, what's the best way to go about getting this information into a database? I am actually a web designer with some experience using MySQL, so my issue isn't really conceptual. But it turns out I don't really know how to populate my database before I create my site. Should I just enter my data in Microsoft Access, and then convert the Access DB to a more universal format? Or is there a good front-end tool that would help me get it into a MySQL-ready file from the start?

The second, related question is, what would be the best/easiest platform to build this site on? A couple of years ago, I built a Drupal site from scratch - I set up a spare box with Drupal, PHP and MySQL, and tinkered around until I had a working, reasonably tricked out CMS site. But that could well be overkill for the simpler site I have in mind for this project. Basically, I want to display any or all of the dates in my archive, with options to filter by date, venue, recordings, &c.; as well as the ability to search for shows where specific songs were performed. And that's about it - no news section, no forums, no calendars, basically nothing but the shows.

I've never used WordPress, and I would love to add that to my toolbox - would it be appropriate in this case? Should I stick with Drupal, since a second project on a newer version would also enhance my resume? Or am I totally overthinking this, and I should be thinking about creating a simple, custom PHP pages to do the search & display? In the end I don't actually need any CMS features, but if it's not a lot of extra work, I think the practice would be valuable. I just don't want to waste time building a palace when I just need a bunk house.

posted by Banky_Edwards to computers & internet (10 answers total)

There are a bunch of front ends for MySQL and it's easy to import CSV files as tables in your database. You can even do it from teh command line.

As far as how to do it? If this were me, this would be a ruby on rails project. Based on your description of your abilities and the database, this is literally something you could do in a weekend. Just go through any Ruby on Rails tutorial. It's pretty much designed to do these kinds of things.

I'm simplifying a bit, but once you get your database created, assuming you follow Rails's naming scheme and it's been organized sanely, it's just a matter of editing a few text files and generating a scaffold, and you've got your data entry and display pages done -- that should take less than an hour. Then it's just a matter of adding an admin interface and fixing your html templates and you're done.
posted by empath at 6:16 PM on June 21, 2012

How many recordings are there? If it's just a couple hundred or less I'd be inclined to go with MIT's Exhibit javascript application which would store the data as a flat JSON text file. There are instructions and tools in the documentation for generating the data file from an Excel or Google spreadsheet. Plus, since it would all be flat files you wouldn't have to worry about maintaining and upgrading, etc., of a database.
posted by XMLicious at 6:17 PM on June 21, 2012 [1 favorite]

Here's a tutorial for creating a blog, but you can adapt those instructions to handle pretty much anything, once you get the hang of it.
posted by empath at 6:17 PM on June 21, 2012

You absolutely could use Wordpress for this... but you will probably need to do a fair bit of custom work. There are probably more appropriate projects to use it on. However, if you want to learn how to use it, why not? There's a vibrant ecosystem out there with tons of resources.
posted by Magnakai at 6:57 PM on June 21, 2012

This is the sort of problem I used Rails to solve, too: I built an app that helps me browse a 500GB music collection. I started with a simple Rails schema, then wrote a little file crawler in Ruby to populate my database with title, artist, album, bitrate, track number and file path. I've got the whole project sitting here, so if you're interested feel free to drop me a MeMail and I'll either get the (rough, old-for-rails) code up onto github or make it otherwise available.

I got into Rails in the first place because paying money for a commercial database application like Access or Filemaker seemed pointless, and Rails' scaffolding made it easy to get something that worked right away, but I could add functionality to over time.

If you've already got PHP, Ruby will be pretty simple to pick up. If you've already got SQL, you can either enjoy taking a break from it with ActiveRecord (the Rails database library) or just use SQL within ActiveRecord.

If you're interested in burnishing your Drupal résumé and have decent PHP chops, I'll take this opportunity to point out the Migrate module, which allows you to take an existing non-Drupal table (or tables) and map the fields within to Drupal CCK or content fields.
posted by mph at 7:07 PM on June 21, 2012

Please post a link to it in your profile when you finish. I really appreciate all that Robert Hunter did for and with the band. Garcia was so well complimented by Hunter.
posted by JohnnyGunn at 7:46 PM on June 21, 2012

Wordpress, because you get so much functionality for free. Especially if you used one of the many free plugins.

The tricky part is, as you've identified, importing all the data. You could simply use the built-in content types (blogs posts or pages) but you could also create new ones that are tailor made for your data. So if you have dozens of pages about concerts of charities and the pages are fairly uniform, you could create a concert or charity custom type. There are great wordpress plugins that makes it fairly straightforward to create custom content types.

Then there's creating a theme that presents all the data. You could purchase a theme and tweak it for your needs. You could also use a so called starter theme to create your own theme from scratch.

If Wordpress seems interesting but a bit overwhelming, feel free to mefi mail me your questions.
posted by Foci for Analysis at 9:37 PM on June 21, 2012

it's been a while for me but does PHPMyAdmin allow you to import data? (it does - just checked) Get the data into excel whatever - export it as CSV or tab Delimited files the import into your pre defined table structure with phpMyAdmin

I have not had experience with Drupal or Wordpress
posted by mattoxic at 11:38 PM on June 21, 2012

Thanks so much for the answers, everyone - this is exactly the kind of feedback I needed. I don't know why it hadn't occurred to me to just dump the info in Excel and go from there - that's by far the easiest starting point, I think. I agree that this might be the perfect opportunity to dip my toes in the Rails pool - that might never have occurred to me, even though "do something with Rails" has been on my list for a while now.

Oh, and XMLicious - I hadn't seen that Exhibit thing before, but I think I have another little project that might need just exactly that!
posted by Banky_Edwards at 6:16 AM on June 22, 2012

I am going to say that Drupal+ Migrate module will do this pretty easily. Use Views to build out the front end, node to build out the entry point. Getting it pretty might be the bigger challenge.
posted by BrodieShadeTree at 8:12 AM on June 22, 2012