Need Help Refining Script Routine

Post a reply

Smilies
:D :) :( :o :-? 8) :lol: :x :P :oops: :cry: :evil: :roll: :wink:
BBCode is ON
[img] is ON
[url] is ON
Smilies are ON
Topic review
   

Expand view Topic review: Need Help Refining Script Routine

Re: Need Help Refining Script Routine

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.

Re: Need Help Refining Script Routine

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

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

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

Re: Need Help Refining Script Routine

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.

Need Help Refining Script Routine

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!

Top