IPB

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> MySQL Export
Mp1337
post Mar 26 2010, 19:27
Post #1


Member


Group: Members
Posts: 4
Joined: 26-March 10
Member No.: 12149
Mp3tag Version: 2.46



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:
QUOTE
$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:
QUOTE
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)" ohmy.gif. 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
Go to the top of the page
 
+Quote Post
DetlevD
post Mar 26 2010, 20:00
Post #2


Member


Group: Full Members
Posts: 4918
Joined: 26-May 06
From: Wuppertal, Germany, Planet Earth
Member No.: 3194
Mp3tag Version: 2.63



QUOTE (Mp1337 @ Mar 26 2010, 19:27) *
... 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


--------------------
* Beyond that, don't ask, when you don't know what to do with the answer. *
♥ home is where the heart is ♥
Go to the top of the page
 
+Quote Post
Mp1337
post Mar 26 2010, 21:00
Post #3


Member


Group: Members
Posts: 4
Joined: 26-March 10
Member No.: 12149
Mp3tag Version: 2.46



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)?
Go to the top of the page
 
+Quote Post
LyricsLover
post Mar 26 2010, 21:14
Post #4


Member


Group: Full Members
Posts: 794
Joined: 21-September 06
From: Central Europe
Member No.: 3709
Mp3tag Version: 2.62



Nice idea, mp1337!
Go to the top of the page
 
+Quote Post
Mp1337
post Mar 27 2010, 16:47
Post #5


Member


Group: Members
Posts: 4
Joined: 26-March 10
Member No.: 12149
Mp3tag Version: 2.46



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.
QUOTE
$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()
Go to the top of the page
 
+Quote Post
Mp1337
post Mar 27 2010, 21:03
Post #6


Member


Group: Members
Posts: 4
Joined: 26-March 10
Member No.: 12149
Mp3tag Version: 2.46



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! ohmy.gif
Go to the top of the page
 
+Quote Post
LyricsLover
post Mar 27 2010, 21:33
Post #7


Member


Group: Full Members
Posts: 794
Joined: 21-September 06
From: Central Europe
Member No.: 3709
Mp3tag Version: 2.62



You could try it with a SELECT DISTINCT-command to get only every album once:
http://www.w3schools.com/sql/sql_distinct.asp
Go to the top of the page
 
+Quote Post
DetlevD
post Mar 28 2010, 07:33
Post #8


Member


Group: Full Members
Posts: 4918
Joined: 26-May 06
From: Wuppertal, Germany, Planet Earth
Member No.: 3194
Mp3tag Version: 2.63



QUOTE (Mp1337 @ Mar 27 2010, 22:03) *
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! ohmy.gif

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

DD.20100328.0833.CEST


--------------------
* Beyond that, don't ask, when you don't know what to do with the answer. *
♥ home is where the heart is ♥
Go to the top of the page
 
+Quote Post
RU486
post Oct 26 2010, 20:26
Post #9


Member


Group: Members
Posts: 1
Joined: 22-February 09
Member No.: 8672
Mp3tag Version: 2.42



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:
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 



RSS Lo-Fi Version Time is now: 3rd September 2014 - 03:06