{"id":294,"date":"2007-10-05T23:47:49","date_gmt":"2007-10-06T06:47:49","guid":{"rendered":"http:\/\/strawhousepig.net\/wp\/?p=294"},"modified":"2007-10-05T23:47:49","modified_gmt":"2007-10-06T06:47:49","slug":"itunes-library-to-mysql-database","status":"publish","type":"post","link":"https:\/\/strawhousepig.net\/wordpress\/2007\/10\/05\/itunes-library-to-mysql-database\/","title":{"rendered":"iTunes Library to MySQL database"},"content":{"rendered":"

A PHP XML parser that will populate a MySQL table using an exported iTunes library file as the source.
\n
\nFirst, create a table:
\nCREATE TABLE `table` (
\n `track_id` mediumint(9) DEFAULT '1',
\n `name` varchar(255) DEFAULT NULL,
\n `album` varchar(255) DEFAULT NULL,
\n `artist` varchar(255) DEFAULT NULL,
\n `genre` varchar(255) DEFAULT NULL,
\n `year` smallint(4) unsigned DEFAULT NULL,
\n `rating` smallint(4) unsigned DEFAULT NULL,
\n `track_number` smallint(4) unsigned DEFAULT NULL,
\n `track_count` smallint(4) unsigned DEFAULT NULL,
\n `play_count` mediumint(6) unsigned DEFAULT NULL,
\n `date_added` datetime DEFAULT NULL,
\n UNIQUE KEY `track_id` (`track_id`)
\n) ENGINE=MyISAM DEFAULT CHARSET=utf8;<\/code>
\nYou can of course roll your own, but you’ll need to add any other columns you would like to have. Also, you must<\/em> 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<\/strong> array in the PHP script near the top.<\/p>\n

Now for the PHP:
\n', '''
\n$replacevalue = array('=amp1=','=amp2=','=amp3=','=amp4=');\t\t\/\/ the replacement
\n$finalvalueforitdb = array('&','<','>','\\'');\t\t\t\t\t\/\/ the character to insert into table.<\/p>\n

############## END USER AREA ################<\/p>\n

function db_connect() {
\n\tglobal $db_host, $db_name, $db_table, $db_username, $db_password;
\n\tmysql_connect($db_host, $db_username, $db_password) or die (\"Cannot connect to mySQL server\");
\n\tmysql_select_db($db_name) or die (\"Cannot connect to mySQL database\");
\n}<\/p>\n

\/\/change 'to_print' to match MySQL columns
\nfunction alter_print_arr(&$input, $key) {
\n\t$input = str_replace(' ','_',strtolower($input));
\n}
\narray_walk($to_print,'alter_print_arr');<\/p>\n

function array_to_table($array) {
\n\t\/\/expects multi-dimensional array, all with the same keys
\n\tglobal $db_table, $to_print, $replacevalue, $finalvalueforitdb;
\n\tdb_connect();
\n\t$preflight = mysql_query(\"SELECT * FROM $db_table\");
\n\tif (mysql_num_rows($preflight) > 0) {
\n\t\tmysql_query(\"DELETE FROM $db_table\") or die (\"Could not remove old records.\");
\n\t\tmysql_query(\"OPTIMIZE TABLE $db_table\");
\n\t}
\n\tforeach($array as $elem_key=>$element) {
\n\t\tif (isset($element['track_id'])) {
\n\t\t\t$sql = \"\";
\n\t\t\tforeach($element as $k=>$v) {
\n\t\t\t\tif(in_array($k, $to_print)) {
\n\t\t\t\t\t\/\/ Reverse the symbol changes done earlier to insert actual character
\n\t\t\t\t\t$sql .= \"$k='\".mysql_real_escape_string(str_replace($replacevalue,$finalvalueforitdb,$v)).\"', \";
\n\t\t\t\t}
\n\t\t\t}
\n\t\t\t$sql = rtrim(ltrim($sql,\"track_id='$element[track_id]', \"),\", \");
\n\t\t\t$sql1 = \"INSERT INTO $db_table (track_id) VALUES ('$element[track_id]');\";
\n\t\t\t$sql2 = \"UPDATE $db_table SET $sql WHERE track_id=$element[track_id];\";
\n\t\t\tmysql_query($sql1) or die(mysql_error());
\n#\t\t\techo\"$sql1
$sql2<\/p>\n

\";\t\/\/ For debugging. Uncomment with caution!
\n\t\t\tmysql_query($sql2) or die(mysql_error());
\n\t\t}
\n\t}
\n#\tprint_r($array);\t\/\/ For debugging. Uncomment with caution!
\n}
\n############## end MySQL ###################<\/p>\n

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

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

function start_element($parser, $name, $attribs) {
\n\tglobal $current_element, $number_dicts, $current_inside_data;
\n\tif($name==\"DICT\"){
\n\t\t$number_dicts++;
\n\t}
\n\tif ($number_dicts>2){
\n\t\t$current_element=$name;
\n\t}
\n\t$current_inside_data = false;
\n}<\/p>\n

function end_element($parser, $name) {
\n\tglobal $songs, $current_element, $current_data, $number_dicts, $current_key, $end_of_songs, $current_inside_data;
\n\tif($end_of_songs){
\n\t\t$current_inside_data = false;
\n\t\treturn;
\n\t}
\n\tif($current_element==\"KEY\"){
\n\t\t$u = trim($current_data);
\n\t\t$current_key=str_replace(' ','_',strtolower($u));
\n\t}else{
\n\t\tif ($number_dicts>2){
\n\t\t\t$songs[$number_dicts][$current_key]=trim($current_data);
\n\t\t}
\n\t}
\n\t$current_inside_data = false;
\n}<\/p>\n

# Remember the use for $current_inside_data to switch between = and .=
\nfunction character_data($parser, $data) {
\n\tglobal $number_dicts, $current_data, $end_of_songs, $current_element, $old_current_element, $current_inside_data;
\n\tif($data==\"Playlists\") {
\n\t\tprintf(\"%d records parsed!\\n\\nHave a nice day. :)\", ($number_dicts-2));
\n\t\t$end_of_songs=true;
\n\t}
\n\tif ($current_inside_data){
\n\t\t$current_data.=$data;
\n\t} else {
\n\t\t$current_data=ltrim($data);
\n\t}
\n\t$current_inside_data=true;
\n}<\/p>\n

$xml_parser = xml_parser_create(\"UTF-8\");
\nxml_parser_set_option($xml_parser, XML_OPTION_CASE_FOLDING, 1);
\nxml_parser_set_option($xml_parser, XML_OPTION_TARGET_ENCODING, UTF-8);
\nxml_set_element_handler($xml_parser, \"start_element\", \"end_element\");
\nxml_set_character_data_handler($xml_parser, \"character_data\");<\/p>\n

if (!($fp = @fopen($file, \"r\"))) {
\n\treturn false;
\n}<\/p>\n

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

xml_parser_free($xml_parser);
\nfclose($fp);
\narray_to_table($songs);
\n?><\/code>
\nSave 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.<\/p>\n

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.<\/p>\n

PPS. BIG thanks to ‘lgal’ for more betterer character wrangling and other tweaks.<\/p>\n","protected":false},"excerpt":{"rendered":"

A PHP XML parser that will populate a MySQL table using an exported iTunes library file as the source.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,7],"tags":[30],"class_list":["post-294","post","type-post","status-publish","format-standard","hentry","category-code","category-web","tag-php"],"_links":{"self":[{"href":"https:\/\/strawhousepig.net\/wordpress\/wp-json\/wp\/v2\/posts\/294","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/strawhousepig.net\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/strawhousepig.net\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/strawhousepig.net\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/strawhousepig.net\/wordpress\/wp-json\/wp\/v2\/comments?post=294"}],"version-history":[{"count":0,"href":"https:\/\/strawhousepig.net\/wordpress\/wp-json\/wp\/v2\/posts\/294\/revisions"}],"wp:attachment":[{"href":"https:\/\/strawhousepig.net\/wordpress\/wp-json\/wp\/v2\/media?parent=294"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/strawhousepig.net\/wordpress\/wp-json\/wp\/v2\/categories?post=294"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/strawhousepig.net\/wordpress\/wp-json\/wp\/v2\/tags?post=294"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}