HowTo/Read Mp3 ID3 tags into Sql Database

From S23Wiki
Jump to: navigation, search

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.

[1]

(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";
}

if (!in_array($where, array('filename', 'artist', 'title', 'album', 'comment', 'year', 'genre', 'path', 'copyright', 'stereo', 'frequency'))) {
 die ("hackattack");
}
$result=mysql_query( "SELECT * FROM muzak where $where LIKE '%".mysql_real_escape_string($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!


found an other example written in perl here: [2]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