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
Now for the PHP:
', '''
$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
$sql2
"; // 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.
Amazing!
You saved me a bunch of work with this 😀
Thank you very much!
warning !! replace '1' by ‘1’ in the first code 😉
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.
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…
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.
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!!!