HowTo/Read Mp3 ID3 tags into Sql Database

Want your mp3 id tags in a local mysql database?

On a linux system? commandline?

Preparations
On Debian:

Install mysql
apt-get install mysql-server mysql-client

connect, setup root password, create user,create database,grant rights,...

see MySQL

Create table
CREATE TABLE `muzak` ( `id` int(11) NOT NULL auto_increment,  `filename` varchar(255) default NULL,  `artist` varchar(255) default NULL,  `title` varchar(255) default NULL,  `album` varchar(255) default NULL,  `comment` varchar(255) default NULL,  `track` tinyint(2) default NULL,  `year` smallint(4) default NULL,  `genre` varchar(255) default NULL,  `path` varchar(255) default NULL,  `filesize` smallint(6) default NULL,  `copyright` varchar(255) default NULL,  `layer` varchar(255) default NULL,  `stereo` varchar(32) default NULL,  `goodframes` int(6) default NULL,  `badframes` int(6) default NULL,  `frequency` int(6) default NULL,  `playtime` int(6) default NULL,  PRIMARY KEY  (`id`),  UNIQUE KEY `path` (`path`) ) TYPE=MyISAM;

Table structure
mysql> describe muzak; ++--+--+-+-++ ++--+--+-+-++ ++--+--+-+-++
 * Field     | Type         | Null | Key | Default | Extra          |
 * id        | int(11)      |      | PRI | NULL    | auto_increment |
 * filename  | varchar(255) | YES  |     | NULL    |                |
 * artist    | varchar(255) | YES  |     | NULL    |                |
 * title     | varchar(255) | YES  |     | NULL    |                |
 * album     | varchar(255) | YES  |     | NULL    |                |
 * comment   | varchar(255) | YES  |     | NULL    |                |
 * track     | tinyint(2)   | YES  |     | NULL    |                |
 * year      | smallint(4)  | YES  |     | NULL    |                |
 * genre     | varchar(255) | YES  |     | NULL    |                |
 * path      | varchar(255) | YES  | MUL | NULL    |                |
 * filesize  | smallint(6)  | YES  |     | NULL    |                |
 * copyright | varchar(255) | YES  |     | NULL    |                |
 * layer     | varchar(255) | YES  |     | NULL    |                |
 * stereo    | varchar(32)  | YES  |     | NULL    |                |
 * goodframes | int(6)      | YES  |     | NULL    |                |
 * badframes | int(6)       | YES  |     | NULL    |                |
 * frequency | int(6)       | YES  |     | NULL    |                |
 * playtime  | int(6)       | YES  |     | NULL    |                |

Install mp3info
apt-get install mp3info

Description: An MP3 technical info viewer and ID3 1.x tag editor MP3Info has an interactive mode (using curses) and a command line mode.



(we will use the command line mode of course)

Make import script
Then make a bash script to create an input file. This (or something similar) creates a file with SQL syntax for us:


 * 1) /bin/bash
 * 2) "mp3 id3 tag to sql" importer by mutante
 * 3) reads id3 tags using "mp3info" and outputs a dumpfile that can be imported to mysql
 * 4) how to import outfile:
 * 5) mysql -u user -p muzak < mp3.sql

outfile="mp3.sql"
 * 1) good ones to outfile

errfile="mp3.err"
 * 1) errors to error file (files without id tag)

count=1
 * 1) counter

find /home/mutante/Music/ -name \*.mp3 -print | while read file
 * 1) adjust your path here

do


 * 1) writing to out-files (mp3info rocks, -p option is like a printf statement)

mp3info -p "insert into muzak (filename,artist,title,album,comment,track,year,genre,path,filesize,copyright,layer, stereo,goodframes,badframes,frequency,playtime) VALUES (\"%f\",\"%a\",\"%t\",\"%l\",\"%c\",\"%n\",\"%y\",\"%g\",\"%F\",\"%k\",\"%C\",\"%L\",\"%o\",\"%u\",\"%b\",\"%Q\",\"%S\");" "$file" 1>>$outfile 2>>$errfile


 * 1) some additional,but unnecessary screen output,to see if the script is still running

echo -e "\n \n id: $count -" mp3info -p "%a,%t,%F" "$file"

let "count+=1"

done

Import
Now we have two new files, mp3.err contains a list of files we still need to fix and write correct id3 tags (can also use mp3info for that later), and mp3.sql can be imported to the server via: mysql -u user -p muzak < mp3.sql

And here i stop for tonight, because i still get in trouble with more special characters inside the tags ,need to escape " ' \. After getting rid of ' there are still "'s.

(If you want to clean your table and reset the id to 0 after unsuccesful imports use truncate table muzak).

Oh well, i cleaned those few manually. Can check for that later.

Make browser interface for searching
Install Apache

Make PHP (or Perl or Python) scripted html page that offers search form and connects to MySQL.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "DTD/xhtml1-strict.dtd">  My Muzak 

"; mysql_close;

?>

This is just an example!

found an other example written in perl here: mysql_mp3 the interesting here is, that steve splits the location in a parentdir (path) and a filename. since more than one file have the same path this will save a lot of space in the database ;)

take little bit care, if you copy and paste the bash-script, there is no(!) newline before line26, may be this could be discussed wiki-layout_and_code