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 descAnd finally, I executed the following SQL to show my highest rated albums.
select *
from album_rankings
where countX > 3
lowest rating
I also used this SQL to show the albums with the lowest rating:
select Name, Artist, Album, Genre, My_Rating, avg(My_Rating) as avg_rating, count(*) as count_xfrom itunes
where Album != 'misc'
group by Artist, Album
having count_x > 1
order by avg_rating asc
Importing into Drupal 7 nodes
A while back, I wrote a little script to import the data into Drupal 7 nodes (via drush src)...
<?php
//////////////////////////////////////////////////
// REMOVE EXISTING NODES
$sql = "select nid from {node}";
$result = db_query($sql);
$records = $result->fetchAll();
foreach ($records as $record) {
node_delete($record->nid);
}
//////////////////////////////////////////////////
// IMPORT NEW NODES
$itunes_export_path = '/var/www/vhosts/itunes.vm/Music.txt';
// load text file
$file = file_get_contents($itunes_export_path);
// explode on new line
$file = explode("\r", $file);
$columns = array_shift($file);
$columns = explode("\t", $columns);
foreach ($file as $key => $value) {
$data = explode("\t", $value);
//if ($key == 0) {
if (1 == 1) {
// create new node
$node = new StdClass();
$node->type = 'itunes';
node_object_prepare($node);
$node->language = LANGUAGE_NONE;
$node->uid = 1;
// sample date
// 2011-06-25T11:55:17
$date_format = 'Y-m-d\TH:i:s';
foreach ($data as $k => $v) {
// trim value
$v = trim($v);
// ensure a value exists
if (!strlen($v)) {
continue;
}
// get column
$column = $columns[$k];
switch ($column) {
case 'Name':
$node->title = $v;
break;
case 'Artist':
$node->field_artist[$node->language][0]['value'] = $v;
break;
case 'Composer':
$node->field_composer[$node->language][0]['value'] = $v;
break;
case 'Album':
$node->field_album[$node->language][0]['value'] = $v;
break;
case 'Grouping':
$node->field_grouping[$node->language][0]['value'] = $v;
break;
case 'Genre':
$node->field_genre[$node->language][0]['value'] = $v;
break;
case 'Size':
$node->field_size[$node->language][0]['value'] = $v;
break;
case 'Time':
$node->field_time[$node->language][0]['value'] = $v;
break;
case 'Disc Number':
$node->field_disc_number[$node->language][0]['value'] = $v;
break;
case 'Disc Count':
$node->field_disc_count[$node->language][0]['value'] = $v;
break;
case 'Track Number':
$node->field_track_number[$node->language][0]['value'] = $v;
break;
case 'Track Count':
$node->field_track_count[$node->language][0]['value'] = $v;
break;
case 'Year':
$node->field_year[$node->language][0]['value'] = $v;
break;
case 'Date Modified':
$node->field_date_modified[$node->language][0]['value'] = date($date_format, strtotime($v));
break;
case 'Date Added':
$node->field_date_added[$node->language][0]['value'] = date($date_format, strtotime($v));
break;
case 'Bit Rate':
$node->field_bit_rate[$node->language][0]['value'] = $v;
break;
case 'Sample Rate':
$node->field_sample_rate[$node->language][0]['value'] = $v;
break;
case 'Volume Adjustment':
$node->field_volume_adjustment[$node->language][0]['value'] = $v;
break;
case 'Kind':
$node->field_kind[$node->language][0]['value'] = $v;
break;
case 'Equalizer':
$node->field_equalizer[$node->language][0]['value'] = $v;
break;
case 'Comments':
$node->field_comments[$node->language][0]['value'] = $v;
break;
case 'Plays':
$node->field_plays[$node->language][0]['value'] = $v;
break;
case 'Last Played':
$node->field_last_played[$node->language][0]['value'] = $v;
break;
case 'Skips':
$node->field_skips[$node->language][0]['value'] = $v;
break;
case 'Last Skipped':
$node->field_last_skipped[$node->language][0]['value'] = date($date_format, strtotime($v));
break;
case 'My Rating':
$node->field_my_rating[$node->language][0]['value'] = $v;
break;
case 'Location':
$node->field_location[$node->language][0]['value'] = $v;
break;
// end: switch
}
// end: foreach
}
// save node
node_save($node);
}
}
?>
That's almost a clone of my
That's almost a clone of my iTunes library ;). And you develop Drupal on a Mac! Man! You must be my twin brother!
nice!
I always wanted a twin brother! :)
full code
any chance of putting up all this files for this program?