Need Help Refining Script Routine

Download and get help for different MediaMonkey Addons.

Moderators: Peke, Gurus

Need Help Refining Script Routine

Postby agapeincognito » Wed Aug 31, 2005 6:48 pm

The following routine is a very basic listing of all Artists in the MM database, with simple statistics and a summary of the following:

- Album authored by them (Albums)
- Album they appeared on (Appearances)
- Songs they authored (Songs)

The following can be plugged into anything that writes to an MM Web Document (indicated by DOC).

Note: Please take the HTML formatting and order with a grain of salt. I just whipped that in real quick to separate out the items and make them easier to read - the HTML is by no means in final form.

I need help in two ways:

(1) I would like to know whether or not the routine is as simplified as it can be for its purposes of both accurately counting the designated info and displaying the designated info for each artist? In other words, are there simpler queries I could have used?

(2) I need to consolidate the APPEARANCES list and count. The other counts only show up for unique instances simply by the nature of the query. But the query I have used for APPEARANCES causes all appearances to show up. Therefore, if an artist appears on an album twice, then the artist will have the album listed twice under ALBUM APPEARANCES. I would like it to be listed only once.

Code (may be easier to understand if you paste in a text editor with word wrap turned off):

Code: Select all
   Set colArtists = SDB.Database.OpenSQL ("SELECT ID, Artist FROM Artists ORDER BY Artist")
   While NOT colArtists.EOF
      If NOT colArtists.StringByIndex(0) = "0" Then
         Set cntSongs = SDB.Database.OpenSQL ("SELECT COUNT(*) FROM Songs WHERE Songs.IDArtist = " & colArtists.StringByIndex(0))
         Set cntAlbums = SDB.Database.OpenSQL ("SELECT COUNT(*) FROM Albums WHERE Albums.IDArtist = " & colArtists.StringByIndex(0))
         Set cntAppearances = SDB.Database.OpenSQL ("SELECT COUNT(*) FROM Songs INNER JOIN Albums ON Songs.IDAlbum = Albums.ID WHERE Songs.IDArtist = " & colArtists.StringByIndex(0) & " AND Albums.IDArtist <> " & colArtists.StringByIndex(0))
         ' Artist Summary
         Doc.Write "<br /><span style='color: blue; font-size: large;'>" & UCase(colArtists.StringByIndex(1)) & "</span> (Appears: <b>" & cntAppearances.StringByIndex(0) & "</b>, Albums: <b>" & cntAlbums.StringByIndex(0) & "</b>, Songs: <b>" & cntSongs.StringByIndex(0) & "</b>)<br />"
         ' List Albums
         Set colAlbums = SDB.Database.OpenSQL ("SELECT Album FROM Albums WHERE Albums.IDArtist = " & colArtists.StringByIndex(0))
         If NOT colAlbums.EOF Then Doc.Write "<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>ALBUMS</b><br />"
         While NOT colAlbums.EOF
            Doc.Write "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" & colAlbums.StringByIndex(0) & "<br />"
            colAlbums.Next
         Wend
         ' List Album Appearances
         Set colAppearances = SDB.Database.OpenSQL ("SELECT Album FROM Albums INNER JOIN Songs ON Songs.IDAlbum = Albums.ID WHERE Songs.IDArtist = " & colArtists.StringByIndex(0) & " AND Albums.IDArtist <> " & colArtists.StringByIndex(0))
         If NOT colAppearances.EOF Then Doc.Write "<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>ALBUM APPEARANCES</b><br />"
         While NOT colAppearances.EOF
            Doc.Write "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" & colAppearances.StringByIndex(0) & "<br />"
            colAppearances.Next
         Wend
         ' List Songs
         Set colSongs = SDB.Database.OpenSQL ("SELECT SongTitle FROM Songs WHERE Songs.IDArtist = " & colArtists.StringByIndex(0))
         If NOT colSongs.EOF Then Doc.Write "<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>SONGS</b><br />"
         While NOT colSongs.EOF
            Doc.Write "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" & colSongs.StringByIndex(0) & "<br />"
            colSongs.Next
         Wend
         'Set colSongs = SDB.Database.OpenSQL ("SELECT SongTitle FROM Songs WHERE Songs.IDArtist = " & colArtists.StringByIndex(0))
         'While NOT colSongs.EOF
         '   Doc.Write "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" & colSongs.StringByIndex(0) & "<br>"
         '   colSongs.Next
         'Wend
      End If
      colArtists.Next
   Wend


Thanks for your help!
agapeincognito
 
Posts: 69
Joined: Fri Aug 05, 2005 4:10 pm
Location: Orlando, Florida, USA

Re: Need Help Refining Script Routine

Postby onkel_enno » Thu Sep 01, 2005 12:50 am

agapeincognito wrote:But the query I have used for APPEARANCES causes all appearances to show up. Therefore, if an artist appears on an album twice, then the artist will have the album listed twice under ALBUM APPEARANCES. I would like it to be listed only once.


You could add a "GROUP BY Albums.Album" to the Query.
SansaMonkey - for SanDisk Sansa and Rockbox Users

Please no PMs for Questions which should be asked in the Forum. Thx
onkel_enno
 
Posts: 2144
Joined: Fri Jan 14, 2005 1:45 am
Location: Germany

Postby agapeincognito » Thu Sep 01, 2005 8:21 am

OUTSTANDING! Didn't think of that at all. Works beautifully!
agapeincognito
 
Posts: 69
Joined: Fri Aug 05, 2005 4:10 pm
Location: Orlando, Florida, USA

Re: Need Help Refining Script Routine

Postby alb123 » Wed Jun 28, 2017 6:08 am

How do I use this script? I've only downloaded a few extensions and they've always been in a file that I just needed to double-click in Windows Explorer. :-?
alb123
 
Posts: 7
Joined: Sun Apr 02, 2017 8:09 am

Re: Need Help Refining Script Routine

Postby MMFrLife » Wed Jun 28, 2017 1:31 pm

Sounds pretty neato! :)
agapeincognito wrote:The following can be plugged into anything that writes to an MM Web Document (indicated by DOC).

So, would you create TXT or DOC > paste > to what location > ...no?

Also, you might want to change the title to a script name.
MM user since 2003 (lifetime since 2012) "Trying to imagine life without music gives me a headache"
track properties; dir/fn mask configurations; check out ZvezdanD's scripts
MMW 4.1.18.1842b; fav scripts > RegExp Find & Replace 5.1.0; Magic Nodes 5.1.0; Advanced Duplicate Find & Fix 3.8.2;
Backup 6.0; Clear Now Playing Button 1.2
...always back it up, especially before doing anything major!!!
Win. explorer alt. for power users [free]; great file/folder renamer [used by nasa]
MMFrLife
 
Posts: 2734
Joined: Fri Oct 26, 2012 9:04 pm


Return to Need Help with Addons?

Who is online

Users browsing this forum: No registered users and 12 guests