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:
#/bin/bash # "mp3 id3 tag to sql" importer by mutante # reads id3 tags using "mp3info" and outputs a dumpfile that can be imported to mysql # how to import outfile: # mysql -u user -p muzak < mp3.sql # good ones to outfile outfile="mp3.sql" # errors to error file (files without id tag) errfile="mp3.err" # counter count=1 # adjust your path here find /home/mutante/Music/ -name \*.mp3 -print | while read file do # 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 # 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"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <title> My Muzak </title> <style type="text/css"> <!-- body { background-color: #332255; color: #aaaa00; } table { width:100%; empty-cells: show; } td { border:1px solid #000; vertical-align:top; overflow:hidden; white-space: nowrap;} th { border:2px solid #000; vertical-align:top; overflow:hidden; } input { background-color: #000000; color: #00aa00; } select { background-color: #000000; color: #00aa00; } --> </style> </head> <body> <table> <tr><th colspan="3">my muzak db v0.23</th></tr> <tr><td colspan="3"> <?php echo "My name is Sirius.</td></tr><tr><td colspan=\"3\">Hello, fnord from ".$_SERVER['REMOTE_ADDR'].", you are using ".$_SERVER['HTTP_USER_AGENT']."</td></tr>"; echo "<tr><td colspan=\"3\">My IP / hostname is: ".$_SERVER['SERVER_NAME']."</td></tr>"; ?> <tr><td colspan="3"> <form action="index.php" method="post"> <p> search for: <input type="text" id="what" name="what" size="23" /> in <select name="where" id="where"> <option value="filename">filename</option> <option value="artist">artist</option> <option value="title">title</option> <option value="album">album</option> <option value="comment">comment</option> <option value="year">year</option> <option value="genre">genre</option> <option value="path">path</option> <option value="copyright">copyright</option> <option value="stereo">stereo</option> <option value="frequency">frequency</option> </select> <input type="submit" value="Go" /> </p> </form> </td></tr> <tr><td colspan="3"> <?php $db="muzak"; $link = mysql_connect("localhost","fnord","ownzya"); # no, this is not my real pass :) if (! $link) die("Couldn't connect to MySQL"); mysql_select_db($db , $link) or die("Couldn't open $db: ".mysql_error()); if (isset($_POST["what"])) { $what = $_POST["what"]; } else { $what="fnord"; } if (isset($_POST["where"])) { $where = $_POST["where"]; } else { $where="filename"; } $result=mysql_query( "SELECT * FROM muzak where $where LIKE '%$what%'" ); $num=mysql_num_rows($result); echo "Searched for <b>$what</b> in <b>$where</b>. $num files found.</td></tr>"; echo "<tr><th></th></tr><tr><th>id</th><th>filename</th><th>artist</th><th>title</th><th>album</th><th>comment</th><th>track</th><th>year</th><th>genre</th><th>path</th><th>filesize</th><th>copyright</th><th>layer</th><th>stereo</th><th>goodframes</th><th>badframes</th><th>frequency</th><th>playtime</th></tr>"; while($row = mysql_fetch_array($result)) { echo "<tr><td>".$row["id"]."</td><td>".$row["filename"]."</td><td>".$row["artist"]."</td><td>".$row["title"]."</td><td>".$row["album"]."</td><td>".$row["comment"]."</td><td>".$row["track"]."</td><td>".$row["year"]."</td><td>".$row["genre"]."</td><td>".$row["path"]."</td><td>".$row["filesize"]."</td><td>".$row["copyright"]."</td><td>".$row["layer"]."</td><td>".$row["stereo"]."</td><td>".$row["goodframes"]."</td><td>".$row["badframes"]."</td><td>".$row["frequency"]."</td><td>".$row["playtime"]." s</td></tr>"; } echo "</table>"; mysql_close(); ?> </body> </html>
This is just an example!