MySQL Export


#1

Hello,

I guess many of you would like to get all data into a MySQL database. This is possible with a simple MySQL export script I've made.
Go to "Export" in Mp3Tag, create a new configuration, name it as you prefer. Then add the following code:

This will generate a SQL code which you can run in PHPMyAdmin or somewhere else you run your SQL scripts. You need to create two tables first, one named "music_stats" which has 3 fields, and another one named "music", with 6 fields.
I've used VARCHAR for the title, artist, album, year, genre, list_date and tot_length, and the others INT.
When running the SQL, all tables will be filled with the right data if you did all correct!

You can modify the code however you want. If you have any questions, please feel free to do so :slight_smile:

Example of some AC/DC from my database, first a part of the SQL code Mp3Tag generated with the script above, second a screenshot of the part (the first page) after putting it into the database using phpMyAdmin:

:astonished:. Things you discover while doing this. The reason my database needs to be so tidy is because I'm a radio DJ, and I wan't to offer the right information to listeners + the request page based on this needs to carry the correct information to ofcourse.

I'm now going to read out all data in the database and show it on a nice webpage to share my collection with my mates :slight_smile: (not as downloadable files, but for information about what I've got)


Simplified Case Statement using First letter of Artist Tag
#2

Hmm, not so many, but a few who know why and how to do.

A few days ago in January 2010 we've started here in the forum to talk about exporting to SQL using the commandline tool sqlite3.exe. It looks like a rather simple thing.
Read there:
Export data to database?

DD.20100326.2000.CET


#3

Well they're both possible :slight_smile:
Is it possible for anyone active on this forum to include my post into that topic and delete this one (obviously a mod)?


#4

Nice idea, mp1337!


#5

I've made a little adjustment to it, which creates a table with only the artist. Well, the table itself you have to create yourself like shown above or change to code so it will do that for you automaticly. This will generate the sql files with an extra command that will insert all artists 1 time into the table "music_artists". If you would read out "artist" from the other table, you'll get the artist repeated as many times the artist has songs, which you don't want in some cases.


#6

I'll need some help with the next thing I'm working on. Does someone have a clue how to export all album names only once? I've got the album names, but they appear as many times there're songs on that particular album! :astonished:


#7

You could try it with a SELECT DISTINCT-command to get only every album once:
http://www.w3schools.com/sql/sql_distinct.asp


#8

Read the Mp3tag help file. Use $loop(p1,p2) with p2=1.

DD.20100328.0833.CEST


#9

I'm loving this Mp1337! Thanks for the work. Going to go play around with some of the script and see how I can tweak it to my personal specifications. Haven't been on here in a while, and was wondering when someone would make the jump into MySQL. It just seems so practical with large collections.

:thumbsup: