HowTo/Read Mp3 ID3 tags into Sql Database

From s23
Jump to navigation Jump to search

Want your mp3 id tags in a local mysql database?

On a linux system? commandline?

Preparations[edit]

On Debian:

Install mysql[edit]

apt-get install mysql-server mysql-client
connect, setup root password, create user,create database,grant rights,...
see MySQL


Create table[edit]


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[edit]

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[edit]

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[edit]

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[edit]

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[edit]

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