Simplified Case Statement using First letter of Artist Tag


#1

I'm familiar with how case statements work, I'm looking for some help on how to select the proper case statement based solely on the artists first letter. This is being used for a MySQL database being imported via csv.

So it would be something like:

if (%artist% == 'A')

then SQL foo = SQL A directory

else

if (%artist% == 'B')

then SQL foo = SQL B directory

.

.

if (%artist% == 'Z')

then SQL foo = SQL Z directory

if (%artist% == '0-9')

then SQL foo = SQL NUMBER directory

This will be done in the export option that I have to build. I have full control of the csv aspect, I'm just trying to figure out how to do case selection or do I have to do a long winded if/else/elseif/elseif type thing

Can someone point me to a previous example or give me a short hand script of how this would look in the export routine.

Thanks!

Chris


#2

You really just need an if/then/else construct, albeit a long winded one, as are most complex things in Mp3tag's scripting language.

$if($and($geql($left(%artist%,1),'0'),$leql($left(%artist%,1),'9')),SQL foo = SQL NUMBER directory,SQL foo = SQL $left(%artist%,1) directory)

#3

So I see it as if X is true then y else z.....got that......I don't understand the test logic, will that cover 0 through 9 or just 0 AND 9?

Also, how will this propagate to A through Z?

I realize I will have have 27 lines for each condition, I just want to ensure I understand the logic behind it.......

thanks for the fast answer to my first question.

Chris


#4

Make sure you look at the Mp3tag help for scripting and the available functions.

http://help.mp3tag.de/main_scripting.html

Roughly translated:

if (first character is between 0 and 9) then
  return: SQL foo = SQL NUMBER directory
else
  return: SQL foo = SQL + (first character) + directory
endif

The 'between' is done with the $and, the $geql (greater than or equal) and the $leql (less than or equal) functions. If the character is greater than or equal 0 AND it's less than or equal 9, then it's a digit.

The A through Z part is simply the ELSE part of the if then else, which inserts the first character into the SQL string.


#5

It is not clear to me what you want to do.

Do you want to export MySQL data into CSV file?
==> See MySQL scripting language.
http://en.wikipedia.org/wiki/MySQL

Do you want to export tag-field data into CSV file?
==> See Mp3tag scripting language.
http://help.mp3tag.de/main_scripting.html
http://help.mp3tag.de/options_export.html

In Mp3tag scripting language the first letter of a tag-field can be extracted using the function $left().

Examples:

$left(%ARTIST%,1) $upper($left(%ARTIST%,1)) $if($isdigit($left(%ARTIST%,1)),'SQL NUMBER directory','SQL '$upper($left(%ARTIST%,1))' directory') 'SQL '$if($isdigit($left(%ARTIST%,1)),'NUMBER',$upper($left(%ARTIST%,1)))' directory'

DD.20120809.0818.CEST


#6

Thanks to both of you, I'll give these a try understand the logic.

To answer DetlevD, yes, tags to CSV > CSV into SQL via HTML CSV import utility.

You guys are the best.

Regards,

Chris


#7

Regarding SQL see also ...
/t/10187/1
Export data to database?

DD.20120809.1443.CEST


#8

Thanks for the SQL info, I'll bookmark those for future use. My input at the core is csv import, but the HTML is bringing that into a MySQL database for web application. So I don't really need to have true SQL generation.

I'm using this for an online "wishlist" system for a backend. They can search by artist and then create an online playlist.

I'm looking at the code now, I understand how it works and again, it seems the simplistic logic just clouded my thought......easy when I saw the examples.

Thanks again.....:slight_smile:

Chris


#9

I'm bumping into syntax errors and I thought I had a good handle on it, not sure what I'm doing wrong. I've tried a few variants like:

($if($and($geql($left(%artist%,1),'0'),$leql($left(%artist%,1),'9'))),ABP Alphabetical Music List/NUMBERS, NULL)

($if($and($geql($left(%artist%,1),'0'),$leql($left(%artist%,1),'9'))),ABP Alphabetical Music List NUMBERS, NULL)

($if($and($geql($left(%artist%,1),'0'),$leql($left(%artist%,1),'9'))),ABP Alphabetical Music List/NUMBERS,)

But the output keeps giving me:

invalid $if syntax

I want to do this for the numbers and then a-z, so I believe leaving the false part of the statement blank should leave the output alone and then test for the remaining 26 cases. I need the final output to wind up as > ABP Alphabetical Music List/NUMBERS or ABP Alphabetical Music List/A or ABP Alphabetical Music List/B............etc

I thought it might need an escape char for the \ but even removing that isn't work. I'm not sure what the issue is. I'm sure its something stupid on my end. Thoughts?

Thanks


#10

It is not clear to me what you want to achieve.
Do you want to use Mp3tag to create a report file, hm ... maybe yes, and the output lines should be sorted by groups, depending on the first letter of the artist name?

So you have to concentrate your work to develop a $loop() ... $loopend() construct, which fits to your need.

Example:

1: $filename($getEnv('USERPROFILE')'\Desktop\Mp3tag.Report.ByFirstNumberOrLetter.txt',UTF-8)

2: $loop($regexp($regexp($if2(%ARTIST%,%ARTISTSORT%),'^[\W](.).$','\U$1',1),'\d','0-9'),1)
3: '['$regexp($regexp($if2(%ARTIST%,%ARTISTSORT%),'^[\W](.).$','\U$1',1),'\d','0-9')']'
4: $loop($if2(%ARTIST%,%ARTISTSORT%),1)$if2(%ARTIST%,%ARTISTSORT%)
5: $loopend()$loopend()

Doing another approach you can create a temporary tag-field within each media file, which holds the sort criterium for the report loop, and what can be removed afterwards if needed.

Action: Format value
Field: TMP_SORT
Format string: $if($isdigit($left(%ARTIST%,1)),'0',$upper($left(%ARTIST%,1)))

The export loop expression can be rather simple like ...

1: $loop(%TMP_SORT%)%ARTIST%

2: $loopend()

DD.20120809.1630.CEST


#11

I was able to use

$if($isdigit($left(%ARTIST%,1)), ABP Alphabetical Music List/NUMBERS , ABP Alphabetical Music List/$upper($left(%ARTIST%,1)))

to achieve my goal for generating the tag to output. My CSV is now working and I can import into my web app.

Thanks for your help. Your solution is simple and brilliant.....:slight_smile:

Chris