Need Help Refining Script Routine

Download and get help for different MediaMonkey for Windows 4 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 »

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: 2153
Joined: Fri Jan 14, 2005 1:45 am
Location: Germany
Contact:

Re: Need Help Refining Script Routine

Post by onkel_enno »

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.
agapeincognito
Posts: 69
Joined: Fri Aug 05, 2005 4:10 pm
Location: Orlando, Florida, USA

Post by agapeincognito »

OUTSTANDING! Didn't think of that at all. Works beautifully!
alb123
Posts: 10
Joined: Sun Apr 02, 2017 8:09 am

Re: Need Help Refining Script Routine

Post by alb123 »

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: 2894
Joined: Fri Oct 26, 2012 9:04 pm
Location: MM Forum

Re: Need Help Refining Script Routine

Post by MMFrLife »

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 lic. 2012) "Trying to imagine life without music gives me a headache"
Top 2 scripts: RegExp Find & Replace (e.v.) and Magic Nodes (e.v.) ZvezdanD's scripts site
Please take a moment to read the bottom of the linked page to support the one and only - ZvezdanD! (the "originator" since 2006).
MMW 4.1.31.1919; 5.0.4.2690 || back it up...frequently!
|| software for power users: "Q-Dir" (free alt. to explorer) and file/folder renamer: "ReNamer" (den4b)
"The absurd is the essential concept and the first truth"
😜
Post Reply