Exporting iTunes data into MySQL and creating SQL to show top rated albums

In anticipation of my new iPhone 4 (and a larger hard drive), I decided to write a script to help choose which albums I should include. Through iTunes you can export your library as text, which can then be imported into MySQL, and SQL can be executed to show.. well, whatever your heart desires!

I started by exporting my entire music library, and chose Plain Text as the format. This iTunes functionality creates a tab separated list of columns and data.

I then created a new MySQL database and table (SEE: TABLE_NAME) to contain my iTunes song data. I also added an additional column as a primary key, called sql_id.

Next I created a PHP script to parse the text file, dynamically add the columns to my table, and import all my song data. This code uses PEAR’s DB library for the database access layer.

<?php

// define table name
define('TABLE_NAME', 'itunes');

// create database connection
require_once('DB.php');
$dsn = 'mysqli://db_user:db_password@db_host/database_name';
$DB =& DB::connect($dsn);
if (DB::isError($DB)) {
  die($DB->getMessage());
}
$DB->setFetchMode(DB_FETCHMODE_ASSOC);

// load text file
$file = file_get_contents('Music.txt');

// explode on new line
$file = explode("\r", $file);

// get a list of existing columns
$sql = "show columns in " . TABLE_NAME;
$result = $DB->getAll($sql);
$existing_columns = array();
foreach ($result as $key => $value) {
  $existing_columns[] = $value['Field'];
}

// create a variable to contain sql column names
$sql_column_name = array();

// loop through each line in the file
foreach ($file as $key => $value) {

  // explode on tab to get column list
  $exploded = explode("\t", $value);

  // check for first row, which contains column headers
  if ($key == 0) {

    // loop through column list and ensure they exist
    foreach ($exploded as $new_table) {

      // create a new column name without spaces
      $new_table = str_replace(' ' , '_', $new_table);

      // check if the new column should be added
      if (!in_array($new_table, $existing_columns)) {

        // define sql to add new column
        $sql = "alter table " . TABLE_NAME . " add column `$new_table` varchar(255) default null";
        $result = $DB->query($sql);

        // check for error
        if (!$result) {
          echo "<pre>" . print_r($result, true) . "</pre>";
          die;
        }

        // define sql to add index
        $sql = "alter table " . TABLE_NAME . " add index `index_$new_table` ($new_table)";
        $result = $DB->query($sql);

        // check for error
        if (!$result) {
          echo "<pre>" . print_r($result, true) . "</pre>";
          die;
        }

      } // end if

      $sql_column_names[] = $new_table;

    } // end foreach

  } // end if ($key[0])
  else {

    // prepare values to insert
    $insert_values = array();
    foreach ($exploded as $k => $v) {
      $insert_values[$k] = $DB->quoteSmart($v);
    }

    // define SQL to insert data into table
    $sql = "insert into " . TABLE_NAME . " (" . implode(',', $sql_column_names) . ") values (" . implode(',', $insert_values) . ")";

    // execute sql
    $result = $DB->query($sql);

    // check for SQL error
    if (!$result) {
      echo "<pre>" . print_r($result, true) . "</pre>";
    }

  }

}
?>

Now all my iTunes data is accessible by SQL, sweet. I decided to create a view showing my albums with an average ranking.

create view album_rankings as
select Artist, Album, Genre, avg(My_Rating) as album_rating, count(*) as countX
from itunes
where Album != 'misc'
group by Artist, Album
order by avg(My_Rating) desc, countX desc

And finally I executed the following SQL to show my highest rated albums.

select *
from album_rankings
where countX > 3

Top Rated Albums

Here’s a sample table schema:

CREATE TABLE `itunes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) DEFAULT NULL,
  `Artist` varchar(255) DEFAULT NULL,
  `Composer` varchar(255) DEFAULT NULL,
  `Album` varchar(255) DEFAULT NULL,
  `Grouping` varchar(255) DEFAULT NULL,
  `Genre` varchar(255) DEFAULT NULL,
  `Size` varchar(255) DEFAULT NULL,
  `Time` varchar(255) DEFAULT NULL,
  `Disc_Number` varchar(255) DEFAULT NULL,
  `Disc_Count` varchar(255) DEFAULT NULL,
  `Track_Number` varchar(255) DEFAULT NULL,
  `Track_Count` varchar(255) DEFAULT NULL,
  `Year` varchar(255) DEFAULT NULL,
  `Date_Modified` varchar(255) DEFAULT NULL,
  `Date_Added` varchar(255) DEFAULT NULL,
  `Bit_Rate` varchar(255) DEFAULT NULL,
  `Sample_Rate` varchar(255) DEFAULT NULL,
  `Volume_Adjustment` varchar(255) DEFAULT NULL,
  `Kind` varchar(255) DEFAULT NULL,
  `Equalizer` varchar(255) DEFAULT NULL,
  `Comments` varchar(255) DEFAULT NULL,
  `Plays` varchar(255) DEFAULT NULL,
  `Last_Played` varchar(255) DEFAULT NULL,
  `Skips` varchar(255) DEFAULT NULL,
  `Last_Skipped` varchar(255) DEFAULT NULL,
  `My_Rating` varchar(255) DEFAULT NULL,
  `Location` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_Name` (`Name`),
  KEY `index_Artist` (`Artist`),
  KEY `index_Composer` (`Composer`),
  KEY `index_Album` (`Album`),
  KEY `index_Grouping` (`Grouping`),
  KEY `index_Genre` (`Genre`),
  KEY `index_Size` (`Size`),
  KEY `index_Time` (`Time`),
  KEY `index_Disc_Number` (`Disc_Number`),
  KEY `index_Disc_Count` (`Disc_Count`),
  KEY `index_Track_Number` (`Track_Number`),
  KEY `index_Track_Count` (`Track_Count`),
  KEY `index_Year` (`Year`),
  KEY `index_Date_Modified` (`Date_Modified`),
  KEY `index_Date_Added` (`Date_Added`),
  KEY `index_Bit_Rate` (`Bit_Rate`),
  KEY `index_Sample_Rate` (`Sample_Rate`),
  KEY `index_Volume_Adjustment` (`Volume_Adjustment`),
  KEY `index_Kind` (`Kind`),
  KEY `index_Equalizer` (`Equalizer`),
  KEY `index_Comments` (`Comments`),
  KEY `index_Plays` (`Plays`),
  KEY `index_Last_Played` (`Last_Played`),
  KEY `index_Skips` (`Skips`),
  KEY `index_Last_Skipped` (`Last_Skipped`),
  KEY `index_My_Rating` (`My_Rating`),
  KEY `index_Location` (`Location`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Eric London