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).

Make Webinterface for Searching
TO DO