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 <KEY> 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 # 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"; ############## 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 ("<p style='font-color:red'>Cannot connect to mySQL server</p>"); mysql_select_db($db_name) or die ("<p style='font-color:red'>Cannot connect to mySQL database</p>"); } 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; db_connect(); 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 ampersand replacing done earlier $sql .= "$k='".mysql_real_escape_string(str_replace('=amp=','&',$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()); } } echo"Done! :)"; # print_r($array); // For debugging. Uncomment with caution! } $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 function start_element($parser, $name, $attribs) { global $current_element, $number_dicts; if($name=="DICT"){ $number_dicts++; } if ($number_dicts>2){ $current_element=$name; } } function end_element($parser, $name) { global $songs, $current_element, $current_data, $number_dicts, $array_key, $end_of_songs; if($end_of_songs){ return; } if($current_element=="KEY"){ $array_key=str_replace(' ','_',strtolower($current_data)); }else{ $songs[$number_dicts][$array_key]=$current_data; } } function character_data($parser, $data) { global $number_dicts, $current_data, $end_of_songs; if($data=="Playlists") { $end_of_songs=TRUE; } $current_data=trim($data); } $xml_parser = xml_parser_create(); xml_parser_set_option($xml_parser, XML_OPTION_CASE_FOLDING, 1); 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)) { // xml_parser jumps over ampersands. Decode any entities then replace any ampersands. // Reverse this when building SQL statement. if (!xml_parse($xml_parser, str_replace('&','=amp=',html_entity_decode($data)), 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); 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.
This would (or will....) be much nicer with a lot more automation. Kinda crude as is, but snappy enough. My iTunes library file (2054 items, 4MB xml file) parsed in around 8 seconds on an Intel Core Duo 1.66 Mac mini. Your mileage may vary.
