iTunes Library to MySQL database

A PHP XML parser that will populate a MySQL table using an exported iTunes library file as the source.

First, create a table:

CREATE TABLE `table` (
  `track_id` mediumint(9) DEFAULT '1',
  `name` varchar(255) DEFAULT NULL,
  `album` varchar(255) DEFAULT NULL,
  `artist` varchar(255) DEFAULT NULL,
  `genre` varchar(255) DEFAULT NULL,
  `year` smallint(4) unsigned DEFAULT NULL,
  `rating` smallint(4) unsigned DEFAULT NULL,
  `track_number` smallint(4) unsigned DEFAULT NULL,
  `track_count` smallint(4) unsigned DEFAULT NULL,
  `play_count` mediumint(6) unsigned DEFAULT NULL,
  `date_added` datetime DEFAULT NULL,
  UNIQUE KEY `track_id` (`track_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

You can of course roll your own, but you’ll need to add any other columns you would like to have. Also, you must mimic the label from the iTunes library xml file, using only lower case characters and replacing space with underscore. The columns you use in the MySQL table will have to be added to the $to_print array in the PHP script near the top.

Now for the PHP:

<?php
# http://strawhousepig.net/
# Modified from: http://developer.apple.com/internet/opensource/php.html
# Very useful character wrangling and data parsing contributed by 'lgal'.

# Exported iTunes library file
$file="iTunes Music Library.xml";

# Array of columns available in MySQL table
$to_print = array("Name", "Artist", "Album", "Track ID", "Year", "Play Count", "Track Number", "Track Count", "Genre", "Rating", "Date Added");

# MySQL info
$db_host = "host";
$db_name = "database";
$db_table = "table";
$db_username = "user";
$db_password = "password";

# Array of characters xmlparser may treat as markup delimiters (might be more)
$placeholders = array('&#38;','&#60','&#62','&#39');      // the encoded '&', '<', '>', '''
$replacevalue = array('=amp1=','=amp2=','=amp3=','=amp4=');    // the replacement
$finalvalueforitdb = array('&','<','>','\'');          // the character to insert into table.

############## END USER AREA ################

function db_connect() {
  global $db_host, $db_name, $db_table, $db_username, $db_password;
  mysql_connect($db_host, $db_username, $db_password) or die ("Cannot connect to mySQL server");
  mysql_select_db($db_name) or die ("Cannot connect to mySQL database");
}

//change 'to_print' to match MySQL columns
function alter_print_arr(&$input, $key) {
  $input = str_replace(' ','_',strtolower($input));
}
array_walk($to_print,'alter_print_arr');

function array_to_table($array) {
  //expects multi-dimensional array, all with the same keys
  global $db_table, $to_print, $replacevalue, $finalvalueforitdb;
  db_connect();
  $preflight = mysql_query("SELECT * FROM $db_table");
  if (mysql_num_rows($preflight) > 0) {
    mysql_query("DELETE FROM $db_table") or die ("Could not remove old records.");
    mysql_query("OPTIMIZE TABLE $db_table");
  }
  foreach($array as $elem_key=>$element) {
    if (isset($element['track_id'])) {
      $sql = "";
      foreach($element as $k=>$v) {
        if(in_array($k, $to_print)) {
          // Reverse the symbol changes done earlier to insert actual character
          $sql .= "$k='".mysql_real_escape_string(str_replace($replacevalue,$finalvalueforitdb,$v))."', ";
        }
      }
      $sql = rtrim(ltrim($sql,"track_id='$element[track_id]', "),", ");
      $sql1 = "INSERT INTO $db_table (track_id) VALUES ('$element[track_id]');";
      $sql2 = "UPDATE $db_table SET $sql WHERE track_id=$element[track_id];";
      mysql_query($sql1) or die(mysql_error());
#      echo"$sql1<br />$sql2<br /><br />";  // For debugging. Uncomment with caution!
      mysql_query($sql2) or die(mysql_error());
    }
  }
#  print_r($array);  // For debugging. Uncomment with caution!
}
############## end MySQL  ###################

$xml_parser="";
$songs=array();      //will hold each song in a 2-d array
$number_dicts=0;    //counter, number of 'dict' elements encountered
$current_key="";    //key for each element in second dimension of array
$current_element="";  //stores xml element name
$current_data="";    //value for second dimension array elements
$end_of_songs=FALSE;  //boolean used to help let us know if we're done with the song list

$current_inside_data=false; // Variable to control concatenation within character_data function (it mainly happens with extended characters for some reason)

function start_element($parser, $name, $attribs) {
  global $current_element, $number_dicts, $current_inside_data;
  if($name=="DICT"){
    $number_dicts++;
  }
  if ($number_dicts>2){
    $current_element=$name;
  }
  $current_inside_data = false;  
}

function end_element($parser, $name) {
  global $songs, $current_element, $current_data, $number_dicts, $current_key, $end_of_songs, $current_inside_data;
  if($end_of_songs){
    $current_inside_data = false;
    return;
  }
  if($current_element=="KEY"){ 
    $u = trim($current_data);
    $current_key=str_replace(' ','_',strtolower($u));
  }else{
    if ($number_dicts>2){
      $songs[$number_dicts][$current_key]=trim($current_data);
    }
  }
  $current_inside_data = false;
}

# Remember the use for $current_inside_data to switch between = and .=
function character_data($parser, $data) {
  global $number_dicts, $current_data, $end_of_songs, $current_element, $old_current_element, $current_inside_data;
  if($data=="Playlists") {
    printf("%d records parsed!\n\nHave a nice day. :)", ($number_dicts-2));
    $end_of_songs=true;
  }
  if ($current_inside_data){
    $current_data.=$data;
  } else {
    $current_data=ltrim($data); 
  }
  $current_inside_data=true;  
}

$xml_parser = xml_parser_create("UTF-8");
xml_parser_set_option($xml_parser, XML_OPTION_CASE_FOLDING, 1);
xml_parser_set_option($xml_parser, XML_OPTION_TARGET_ENCODING, UTF-8);
xml_set_element_handler($xml_parser, "start_element", "end_element");
xml_set_character_data_handler($xml_parser, "character_data");

if (!($fp = @fopen($file, "r"))) {
  return false;
}

while ($data = fread($fp, 4096)) {
  // Certain literal characters are treated as delimiters. See arrays definition above for details. Current list might not be complete. 
  $mysubstitution=str_replace($placeholders,$replacevalue,$data);
  if (!xml_parse($xml_parser, html_entity_decode($mysubstitution), feof($fp))) {
    die(sprintf("XML error: %s at line %d", xml_error_string(xml_get_error_code($xml_parser)),xml_get_current_line_number($xml_parser)));
  }
}

xml_parser_free($xml_parser);
fclose($fp);
array_to_table($songs);
?>

Save the PHP script on your web server and export your iTunes library (File > Export Library… ) to the same directory. Lastly you’ll need to hit the PHP file with your web browser to initiate the records insertion process.

PS. Thanks to those who have e-mailed about this. One bug has been covered. Unfortunately some special characters (ie., accents or umlauts) do not parse correctly. Others do, such as Katakana characters. I have neither the skill set nor much drive these days to make it work properly. If you do, any insight would be greatly appreciated.

PPS. BIG thanks to ‘lgal’ for more betterer character wrangling and other tweaks.

6 thoughts on “iTunes Library to MySQL database

    1. avatarKevin Post author

      Perhaps you mean the line in the SQL statement “… DEFAULT '1'”? Good catch. Thank you.

      If you mean something else, I am not sure what.

      Reply
  1. avatarDave Goossen

    Hi there, just came across your page and, with one tweak, it worked great!
    I had to change the table name to ‘tunes_table’ as having a table named table was throwing a mySQL error.
    Now to build a front end viewer with search…

    Reply
    1. avatarKevin Post author

      Hmm, I used to keep one around here, but it does not connect to the db, which probably no longer exists. I’ll try to remember to resurrect it later.

      Reply
  2. avatarChris

    Hello!
    Thank you for this awesome script. I’ve been looking for a way to parse an ITunes playlist for a few weeks now and this easy script made it a incredibly easy. Just to help out for any other users looking for the same, I made an update to include all the available fields in an ITunes playlist. Just a note, Lyrics seem to be the only field that don’t show up.

    USE THIS TO CREATE THE TABLE:

    CREATE TABLE IF NOT EXISTS `tunes` (
    `track_id` mediumint(9) DEFAULT ‘1’,
    `name` varchar(255) DEFAULT NULL,
    `album` varchar(255) DEFAULT NULL,
    `artist` varchar(255) DEFAULT NULL,
    `genre` varchar(255) DEFAULT NULL,
    `year` smallint(4) unsigned DEFAULT NULL,
    `rating` smallint(4) unsigned DEFAULT NULL,
    `track_number` smallint(4) unsigned DEFAULT NULL,
    `track_count` smallint(4) unsigned DEFAULT NULL,
    `play_count` mediumint(6) unsigned DEFAULT NULL,
    `album_artist` varchar(255) DEFAULT NULL,
    `composer` varchar(255) DEFAULT NULL,
    `grouping` varchar(255) DEFAULT NULL,
    `kind` varchar(255) DEFAULT NULL,
    `size` mediumint(12) unsigned DEFAULT NULL,
    `total_time` mediumint(12) unsigned DEFAULT NULL,
    `start_time` mediumint(12) unsigned DEFAULT NULL,
    `stop_time` mediumint(12) unsigned DEFAULT NULL,
    `disc_number` smallint(4) unsigned DEFAULT NULL,
    `disc_count` smallint(4) unsigned DEFAULT NULL,
    `bpm` smallint(4) unsigned DEFAULT NULL,
    `date_modified` varchar(255) DEFAULT NULL,
    `bit_rate` smallint(4) unsigned DEFAULT NULL,
    `volume_adjustment` smallint(4) unsigned DEFAULT NULL,
    `equalizer` varchar(255) DEFAULT NULL,
    `comments` longtext NOT NULL,
    `play_date` mediumint(12) unsigned DEFAULT NULL,
    `play_date_utc` varchar(255) DEFAULT NULL,
    `album_rating` smallint(4) unsigned DEFAULT NULL,
    `album_rating_computed` varchar(255) DEFAULT NULL,
    `compilation` varchar(255) DEFAULT NULL,
    `loved` varchar(255) DEFAULT NULL,
    `artwork_count` smallint(4) unsigned DEFAULT NULL,
    `sort_album` varchar(255) DEFAULT NULL,
    `sort_album_artist` varchar(255) DEFAULT NULL,
    `sort_artist` varchar(255) DEFAULT NULL,
    `sort_composer` varchar(255) DEFAULT NULL,
    `sort_name` varchar(255) DEFAULT NULL,
    `persistent_id` varchar(255) DEFAULT NULL,
    `track_type` varchar(255) DEFAULT NULL,
    `apple_music` varchar(255) DEFAULT NULL,
    `date_added` datetime DEFAULT NULL,
    UNIQUE KEY `track_id` (`track_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    Then replace:

    $to_print = array(“Name”, “Artist”, “Album”, “Track ID”, “Year”, “Play Count”, “Track Number”, “Track Count”, “Genre”, “Rating”, “Date Added”);

    With:

    $to_print = array(“Name”, “Artist”, “Album”, “Track ID”, “Year”, “Play Count”, “Track Number”, “Track Count”, “Genre”, “Rating”, “Date Added”, “Album Artist”, “Composer”, “Grouping”, “Kind”, “Size”, “Total Time”, “Start Time”, “Stop Time”, “Disc Number”, “Disc Count”, “BPM”, “Date Modified”, “Bit Rate”, “Volume Adjustment”, “Equalizer”, “Comments”, “Play Date”, “Play Date UTC”, “Album Rating”, “Album Rating Computed”, “Compilation”, “Loved”, “Artwork Count”, “Sort Album”, “Sort Album Artist”, “Sort Artist”, “Sort Composer”, “Sort Name”, “Persistent Id”, “Track Type”, “Apple Music”);

    And finally this will display your data in a simple table format

    <?php
    $db_host = "HOSTNAME";
    $db_name = "DB NAME";
    $db_table = "tunes";
    $db_username = "USERNAME";
    $db_password = "PASSWORD";

    $db = mysql_connect($db_host, $db_username, $db_password);
    mysql_select_db($db_name,$db);

    $result = mysql_query("SELECT * FROM $db_table",$db);

    if ($row = mysql_fetch_array($result)) {
    echo "
    Name
    Artist
    Album
    Track ID
    Year
    Play Count
    Track Number
    Track Count
    Genre
    Rating
    Date Added
    Album Artist
    Composer
    Grouping
    Kind
    Size
    Total Time
    Start Time
    Stop Time
    Disc Number
    Disc Count
    BPM
    Date Modified
    Bit Rate
    Volume Adjustment
    Equalizer
    Comments
    Play Date
    Play Date UTC
    Album Rating
    Album Rating Computed
    Compilation
    Loved
    Artwork Count
    Sort Album
    Sort Album Artist
    Sort Artist
    Sort Composer
    Sort Name
    Persistent Id
    Track Type
    Apple Music
    “;
    do {
    printf(“%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s”, $row[‘name’], $row[‘artist’], $row[‘album’], $row[‘track_id’], $row[‘year’], $row[‘play_count’], $row[‘track_number’], $row[‘track_count’], $row[‘genre’], $row[‘rating’], $row[‘date_added’], $row[‘album_artist’], $row[‘composer’], $row[‘grouping’], $row[‘kind’], $row[‘size’], $row[‘total_time’], $row[‘start_time’], $row[‘stop_time’], $row[‘disc_number’], $row[‘disc_count’], $row[‘bpm’], $row[‘date_modified’], $row[‘bit_rate’], $row[‘volume_adjustment’], $row[‘equalizer’], $row[‘comments’], $row[‘play_date’], $row[‘play_date_utc’], $row[‘album_rating’], $row[‘album_rating_computed’], $row[‘compilation’], $row[‘loved’], $row[‘artwork_count’], $row[‘sort_album’], $row[‘sort_album_artist’], $row[‘sort_artist’], $row[‘sort_composer’], $row[‘sort_name’], $row[‘persistent_id’], $row[‘track_type’], $row[‘apple_music’]);
    } while ($row = mysql_fetch_array($result));
    } else {
    echo “Sorry, no records were found!”;
    } ?>

    Thanks again!!!

    Reply

Leave a Reply

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