Need Help Refining Script Routine

Download and get help for different MediaMonkey Addons.

Moderators: Peke, Gurus

agapeincognito
Posts: 69
Joined: Fri Aug 05, 2005 4:10 pm
Location: Orlando, Florida, USA

Need Help Refining Script Routine

Post by 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!

onkel_enno
Posts: 2146
Joined: Fri Jan 14, 2005 1:45 am
Location: Germany
Contact:

Re: Need Help Refining Script Routine

Post by 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

agapeincognito
Posts: 69
Joined: Fri Aug 05, 2005 4:10 pm
Location: Orlando, Florida, USA

Post by agapeincognito » Thu Sep 01, 2005 8:21 am

OUTSTANDING! Didn't think of that at all. Works beautifully!

alb123
Posts: 9
Joined: Sun Apr 02, 2017 8:09 am

Re: Need Help Refining Script Routine

Post by 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. :-?

MMFrLife
Posts: 2774
Joined: Fri Oct 26, 2012 9:04 pm

Re: Need Help Refining Script Routine

Post by 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]

Post Reply