For no real reason other than saying that I did it, I wanted to have the last 10 songs that I’ve played on my PC listed in the sidebar of my blog. Simple enough..

Here’s what I have: I’m a fan of Last.FM, plus I have about 25Gigs of mp3s that I like to randomize. I also like simplicity, so I use RhythmBox on my Ubuntu desktop. It has a built-in interface to Last.FM (among others) and handles my mp3 library with ease. It also is easily extensible.

At first, I was going to write my own plugin to get the song info, then I found the NowPlaying XML plugin. Perfect! Simply unzip it to your ~/.gnome2/rhythmbox/plugins directory and you’re set (you may have to create the directory first, as did I). Fire up RhythmBox, click “Edit”, “Plugins” and make sure that your NowPlaying XML plugin is checked. Play a song and it should create a file at /tmp/nowplaying.xml.

The /tmp/nowplaying.xml file looks something like this:

<?xml version="1.0" ?><nowplaying><song><album>Saturate</album><track-number>0</track-number><title>Polyamorous</title><artist>Breaking Benjamin</artist><genre></genre><duration>177</duration><bitrate>0</bitrate></song></nowplaying>

Yep, all one line, but that’s okay. It has the data that we want…

Now, to get it into WordPress. First, I created a table in my blog database:

create table music_history (id int primary key not null auto_increment, title nvarchar(30), artiist nvarchar(30), played nvarchar(30));

Yes, I mis-spelled artist, and yes, I probably should have used a datetime data type for played, but work with me here…

Next, I created a Perl script on my workstation called updatePlayHistory.pl that looks something like this:

#!/usr/bin/perl

use Date::Format;
use XML::Simple;

$now = time2str("%D %r", time);

$file = '/tmp/nowplaying.xml';

## make sure that the file exists, if not just exit.
unless (-e $file) { exit(0); }

$xml = new XML::Simple;

## read the xml file in
$data = $xml->XMLin("/tmp/nowplaying.xml");

## get our song object
$song = $data->{song};

## get the song details
$title = $song->{title};
$artist = $song->{artist};

## set up our connection
use DBI;
$dbh = DBI->connect('DBI:mysql:blogdatabase;host=myhost', 'dbuser', 'dbpass')
    || die "Could not connect to the database: $DBI::errstr";

## get the last song inserted and see if it is the same as the current one
$sql_last = "SELECT id, artiist, title FROM music_history order by id desc limit 1";
$out_last = $dbh->prepare($sql_last);
$out_last->execute;
($id, $lastArtist, $lastTitle) = $out_last->fetchrow_array;
$out_last->finish;

## if its the same one, exit out
if (($lastArtist eq $artist) && ($lastTitle eq $title)) {
    $dbh->disconnect();
    exit(0);
}

## insert the song

$sql_insert = "INSERT INTO music_history (title, artiist, played) VALUES ('$title', '$artist', '$now')";
$out_insert = $dbh->prepare($sql_insert);
$out_insert->execute;
$out_insert->finish;

$dbh->disconnect();

The comments should be pretty self explanatory. Tweak as needed. Now, ‘chmod +x’ your script edit your crontab (‘crontab -e’) to run it every minute:

* * * * * * /home/gkurts/scripts/updatePlayHistory.pl

Last, we need to tweak WordPress to display the last 10 played songs. For this, I just copied some of the existing code for one of the widgets and modified it to select the last 10 played and loop through them..

<?php
   $songs = $wpdb->get_results("SELECT id, title, artiist, played from music_history order by id desc limit 10");
?>
   <div class="widget">
      <h3>Song History <span style="font-size: 10pt">(Last 10 played)</span></h3>

      <?php if($songs) : foreach($songs as $song) : ?>
         <ul>
            <li><b><?php echo $song->title; ?></b> by <b><?php echo $song->artiist; ?></b><p style="font-size: 7pt; margin-top: 1px; padding-left: 5px;"><?php echo $song->played; ?></p></li>
         </ul>
         <div class="fixed"></div>
      <?php endforeach; endif; ?>
   </div>

That should be it. It’s really pretty simple. If you use this or have any questions, let me know!

This entry was posted on January 3, 2010 at 1:39 pm. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

Posted in Linux MySQL Perl Php Ubuntu WordPress by gkurts 1 Comment