I was recently working on a social network back-end when I came across a problem. There was a users table, and these users had to have the option of “friending” each other. And so I created a “connections” table, which associated user to user. There were a few extra pieces but essentially what I ended up with was a table that stated the ID, friendA and friendB. I did not want multiple records for each connection so for each “friend” link there would be only one connection record.

The problem here is when it came time to use code to display the list of a given user’s friends, I hit a bit of a wall. I didn’t think I could arrange the users in any sort of order because I would need to use two queries like this (assuming 1 is your ID as the user trying to query the database for a list of your friends):

		SELECT
		user_to_user.id,
		user_to_user.userA
		FROM
		user_to_user
		WHERE
		user_to_user.userB = '1'

		SELECT
		user_to_user.id,
		user_to_user.userB
		FROM
		user_to_user
		WHERE
		user_to_user.userA = '1'

The first query checks all results where you are the user initiating the “friend” connection, and the second checks results where others initiated the connection with you. I thought I would have to loop through the results with userA and then userB separately.

As it turns out, I was wrong. The UNION operator is used to combine the result-set of two or more SELECT statements. So all I had to do is put UNION between the two queries and finish up with an ORDER BY clause and I was in business. Incidentally, I’ll note that the results of this would have columns id and userA – but what about userB? W3Schools says it best: The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION. So all of the results – both userA and userB – will return under the userA column. If I were to swap the queries so I ask for the userB results first, then all results would be as userB.

Ok this has been done before, but everything I’ve read up until now seemed incomplete to me, although what is available was very instructive. For example, Ben Forta’s blog covers Reading MP3 ID3 tags with ColdFusion, while Rupesh Kumar covered Reading MP3 Meta-data from ColdFusion. At first glance the titles almost look like they’re talking about the same thing, but they’re not. The java library “jid3lib” that Ben spoke of could not easily be harnessed to give information such as track length, frequency or bitrate while Rupesh’s code didn’t handle any of the ID3 tag info.

So I decided to combine them.

Firstly you must download the jid3lib and place it in %CFHOME%\runtime\servers\lib and restart the application server.

Secondly, the file I used thanks to Ben Forta.
mp3.cfc:

<cfcomponent>
<!---<cfset mp3File = createObject("java", "coldfusion.util.MP3File").init(".\MP3")>
<cfoutput>mp3File.getBitRate() : #mp3File.getBitRate()# kbps</cfoutput><br>
<cfoutput>mp3File.getFrequency() : #mp3File.getFrequency()# Hz</cfoutput><br>
<cfoutput>mp3File.getVersion() : #mp3File.getVersion()#</cfoutput><br>
<cfoutput>mp3File.getDuration() : #mp3File.getDuration()# Sec</cfoutput><br>
<cfoutput>mp3File.isCopyRighted() : #mp3File.isCopyRighted()#</cfoutput><br>
<cfoutput>mp3File.getChannelMode() : #mp3File.getChannelMode()#</cfoutput><br>--->
    <cfset variables.filename = "">
    <cfset variables.loaded = false>
    <cfset variables.id3tag = "">

    <cffunction name="init" access="public" returnType="mp3" output="false">
        <cfargument name="filename" type="string" required="false">

        <!--- create an instance of the java code --->
        <cfset variables.mp3 = createObject("java", "org.farng.mp3.MP3File")>

        <cfif structKeyExists(arguments, "filename")>
            <!--- read it in --->
            <cfset variables.filename = arguments.filename>
            <cfset read(variables.filename)>
        </cfif>

        <cfreturn this>
    </cffunction>

    <cffunction name="checkLoaded" access="private" returnType="void" output="false"
                hint="Helper function to throw error if no mp3 loaded.">
        <cfif not variables.loaded>
            <cfthrow message="You must first read in an MP3!">
        </cfif>
    </cffunction>

	<cffunction name="getArtist" access="public" returntype="string" output="false" hint="Returns the Artist.">
		<cfreturn variables.id3tag.getLeadArtist()>
	</cffunction>

    <cffunction name="getAlbumTitle" access="public" returnType="string" output="false"
                hint="Returns the album title.">
        <cfreturn variables.id3tag.getAlbumTitle()>
    </cffunction>

    <cffunction name="getSongGenre" access="public" returnType="string" output="false"
                hint="Returns the song genre.">
        <cfreturn variables.id3tag.getSongGenre()>
    </cffunction>

    <cffunction name="getSongTitle" access="public" returnType="string" output="false"
                hint="Returns the song title.">
        <cfreturn variables.id3tag.getSongTitle()>
    </cffunction>

    <cffunction name="getTrackNumber" access="public" returnType="string" output="false"
                hint="Returns the song title.">
        <cfreturn variables.id3tag.getTrackNumberOnAlbum()>
    </cffunction>

    <cffunction name="getYearReleased" access="public" returnType="string" output="false"
                hint="Returns the song's release date.">
        <cfreturn variables.id3tag.getYearReleased()>
    </cffunction>

    <cffunction name="hasID3V1" access="public" returnType="boolean" output="true"
                hint="Returns true if the mp3 has id3v1 information.">
        <cfset checkLoaded()>

        <cfreturn variables.mp3.hasID3v1Tag()>
    </cffunction>

    <cffunction name="hasID3V2" access="public" returnType="boolean" output="false"
                hint="Returns true if the mp3 has id3v2 information.">
        <cfset checkLoaded()>

        <cfreturn variables.mp3.hasID3v2Tag()>
    </cffunction>

    <cffunction name="read" access="public" returnType="void" output="false">
        <cfargument name="filename" type="string" required="true">

        <!--- does the file exist? --->
        <cfif not fileExists(arguments.fileName)>
            <cfthrow message="#arguments.fileName# does not exist.">
        </cfif>

        <!--- copy to global scope --->
        <cfset variables.filename = arguments.filename>

        <cftry>
            <cfset variables.mp3.init(variables.filename)>
            <cfset variables.loaded = true>

            <cfif hasID3V1()>
                <cfset variables.id3tag = variables.mp3.getID3v1Tag()>
            </cfif>
            <cfif hasID3V2()>
                <cfset variables.id3tag = variables.mp3.getID3v2Tag()>
            </cfif>

            <cfcatch>
                <cfthrow message="Invalid MP3 file: #arguments.filename# #cfcatch.message#">
            </cfcatch>
        </cftry>
    </cffunction>

</cfcomponent>

Next, I modified Rupesh’s code to include the coldfusion.util.mp3file class – which you’ll notice I put inside the file loop because the class will not read a directory but will only read an individual file. I just made this the index.cfm file but it can be anything.

<cfset mp3 = createObject("component", "mp3").init()>
<cfset dir = "C:\ColdFusion9\wwwroot\Media Library\MP3\">

<cfdirectory action="list" directory="#dir#" filter="*.mp3" name="music">

<cfloop query="music">
	<cfoutput>filename = #name#<br></cfoutput>
	<cftry>
		<cfset mp3.read(dir &amp; name)>
		<cfset mp3File = createObject("java", "coldfusion.util.MP3File").init(dir & name)>
		<cfset minutes = #mp3file.getDuration()#>
		<cfset minutes = #numberformat(int(minutes/60),"00")#>
		<cfset seconds = #mp3File.getDuration()# mod 60>
		<cfoutput>
			artist: #mp3.getArtist()#<br>
			title: #mp3.getSongTitle()#<br>
			album: #mp3.getAlbumTitle()#<br>
			runtime : #minutes#':#numberformat(seconds,'00')#"<br>
			bitrate: #mp3File.getBitRate()# kbps<br>
			genre: #mp3.getSongGenre()#<br>
			track: #mp3.getTrackNumber()#<br>
			year: #mp3.getYearReleased()#<br>
			frequency: #mp3File.getFrequency()# Hz
		</cfoutput>
	<cfcatch>
		bad file <cfoutput>#cfcatch.message#</cfoutput>
	</cfcatch>
	</cftry>
	<hr>
</cfloop>

At this point it only reads files in a single directory and will not recursively check any subfolders. The output is pictured below:

I’m open to any suggestions on how to extend the class or code to include subfolders – obviously I derived most of the code based on the genius of others so I’m a bit hazy on how to take the ball and run with it. Another problem is the fact that the genre cranks out a genre code unless it’s a custom genre. You’ll notice Styx and Toto both have a genre code of 17 in the image above. This corresponds to “Rock”, but for some reason It won’t show a string.