×
Create a new article
Write your page title here:
We currently have 3,188 articles on s23. Type your article name above or create one of the articles listed here!



    s23
    3,188Articles

    HowTo/Read Mp3 ID3 tags into Sql Database

    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

    Cookies help us deliver our services. By using our services, you agree to our use of cookies.
    Cookies help us deliver our services. By using our services, you agree to our use of cookies.