MySQL Export Options

[Mp1337]

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:

$filename(sql.txt,utf-8)
INSERT INTO music_stats (list_date, tot_tracks, tot_length) VALUES ("%_date%", "%_total_files%", "%_total_time2%");
$loop(%_title%)INSERT INTO music '('title, artist, album, length, year, genre')' VALUES '('"%title%", "%artist%", "%album%", "%_length%", "%year%", "%genre%"')';
$loopend()

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 :-)

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:

INSERT INTO music_stats (list_date, tot_tracks, tot_length) VALUES ("26-3-2010", "5639", "452:57:04");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("Baby, Please Don't Go", "AC/DC", "'74 Jailbreak", "04:52", "1974", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("Jailbreak", "AC/DC", "'74 Jailbreak", "04:43", "1974", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("Show Business", "AC/DC", "'74 Jailbreak", "04:48", "1974", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("Soul Stripper", "AC/DC", "'74 Jailbreak", "06:26", "1974", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("You Ain't Got A Hold On Me", "AC/DC", "'74 Jailbreak", "03:34", "1974", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("Are You Ready (Live)", "AC/DC", "AC/DC Live", "04:35", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("Back In Black (Live)", "AC/DC", "AC/DC Live", "04:29", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("Bonny (Live)", "AC/DC", "AC/DC Live", "01:03", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("Dirty Deeds Done Dirt Cheap (Live)", "AC/DC", "AC/DC Live", "05:03", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("Fire Your Guns (Live)", "AC/DC", "AC/DC Live", "03:42", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("For Those About To Rock (Live)", "AC/DC", "AC/DC Live", "07:08", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("Heatseeker (Live)", "AC/DC", "AC/DC Live", "03:38", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("Hells Bells (Live)", "AC/DC", "AC/DC Live", "06:02", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("High Voltage (Live)", "AC/DC", "AC/DC Live", "10:34", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("Highway To Hell (Live)", "AC/DC", "AC/DC Live", "03:54", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("Jailbreak (Live)", "AC/DC", "AC/DC Live", "14:45", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("Let There Be Rock (Live)", "AC/DC", "AC/DC Live", "12:19", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("Money Talks (Live)", "AC/DC", "AC/DC Live", "04:19", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("Shoot To Thrill (Live)", "AC/DC", "AC/DC Live", "05:24", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("Sin City (Live)", "AC/DC", "AC/DC Live", "05:41", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("T.N.T. (Live)", "AC/DC", "AC/DC Live", "03:48", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("That's The Way I Want My Rock (Live)", "AC/DC", "AC/DC Live", "03:58", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("The Jack (Live)", "AC/DC", "AC/DC Live", "06:58", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("The Razors Edge", "AC/DC", "AC/DC Live", "04:36", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("Thunderstruck (Live)", "AC/DC", "AC/DC Live", "06:35", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("Who Made Who (Live)", "AC/DC", "AC/DC Live", "05:17", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("Whole Lotta Rosie (Live)", "AC/DC", "AC/DC Live", "04:31", "1992", "Hard Rock");
INSERT INTO music (title, artist, album, length, year, genre) VALUES ("You Shook Me All Night Long (Live)", "AC/DC", "AC/DC Live", "03:55", "1992", "Hard Rock");

_{Hey, "The Razors Edge" isn't tagged as "The Razors Edge (Live)" . 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 :-) (not as downloadable files, but for information about what I've got)

This post has been edited by Mp1337: Mar 26 2010, 19:32

[DetlevD]

... Hello, I guess many of you would like to get all data into a MySQL database. ...

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:
http://forums.mp3tag.de/index.php?showtopi...ost&p=43525

DD.20100326.2000.CET

[Mp1337]

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

[LyricsLover]

Nice idea, mp1337!

[Mp1337]

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.

$filename(sql.txt,utf-8)
INSERT INTO music_stats (list_date, tot_tracks, tot_length, tot_size) VALUES ("%_date%", "%_total_files%", "%_total_time2%", "%_total_size%");
$loop(%artist%)INSERT INTO music_artists '('artist')' VALUES '('"%artist%"')';
$loop(%_title%)INSERT INTO music '('title, artist, album, length, year, genre')' VALUES '('"%title%", "%artist%", "%album%", "%_length%", "%year%", "%genre%"')';
$loopend()
$loopend()

[Mp1337]

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!

[LyricsLover]

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

[DetlevD]

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!

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

DD.20100328.0833.CEST

[RU486]

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: