• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

Need to output names dynamically to Poll system

I have a poll that I need to add the names of a player too.
The images are creating by player#image.id#.jpg"

index.cfm:

<!--- Query for images to rate. --->
<cfquery name="image" datasource="#application.dsn#" username="#application.username#" password="#application.password#">
	SELECT
		i.id,
 
		<!--- Get the current rating for the image. --->
		(
			CASE
				WHEN
					COUNT( r.rating ) > 0
				THEN
					(
						SUM( r.rating ) /
						COUNT( r.rating )
					)
				ELSE
					0
			END
		) AS rating,
 
		<!--- Query for existing rating by user. --->
		COALESCE( er.id, 0 ) AS has_existing_rating
	FROM
		(
			SELECT 1 AS id UNION ALL
			SELECT 2 AS id UNION ALL
			SELECT 3 AS id UNION ALL
			SELECT 4 AS id UNION ALL
			SELECT 5 AS id UNION ALL
			SELECT 6 AS id UNION ALL
			SELECT 7 AS id UNION ALL
			SELECT 8 AS id 
		) AS i
 
	<!--- Join this to the rating table to get rating. --->
	LEFT OUTER JOIN
		rating r
	ON
		i.id = r.image_id
 
	<!---
		Join this to the rating table AGAIN to see if the current
		user has already rated the given image.
	--->
	LEFT OUTER JOIN
		rating er
	ON
		(
				er.image_id = i.id
			AND
				er.ip_address = <cfqueryparam value="#cgi.remote_addr#" cfsqltype="cf_sql_varchar" />
			AND
				er.user_agent = <cfqueryparam value="#cgi.http_user_agent#" cfsqltype="cf_sql_varchar" />
		)
 
	GROUP BY
		i.id, r.image_id,er.id
	ORDER BY
		i.id ASC
</cfquery>
 
 
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>

	<title>jQuery And ColdFusion Rating System Demo</title>
	<script type="text/javascript" src="jquery-1.3.2.min.js"></script>
	<script type="text/javascript">
 
		// Define jquery plugin.
		jQuery.fn.rating = function( postUrl ){
			// Loop over each list to apply meta data.
			this.each(
				function( index, listNode ){
					var list = $( this );
					var metaData = list.find( "script.meta-data" );
 
					// Check to see if meta data was found.
					if (metaData.size()){
 
						// Apply meta data.
						list.data(
							"metaData",
							eval( "(" + metaData.html() + ")" )
							);
 
						// Remove the meta data node.
						metaData.remove();
 
					}
				}
				);
 
 
			// Initialize the links within the list.
			this.find( "a" )
				.attr( "href", "javascript:void( 0 )" )
				.click(
					function( clickEvent ){
						var link = $( this );
						var list = link.parents( "ul:first" );
						var metaData = list.data( "metaData" );
 
						// Post the rating.
						jQuery.ajax({
							type: "post",
							url: postUrl,
							data: {
								image_id: metaData.id,
								rating: link.text()
								},
							dataType: "json",
							success: function( apiResponse ){
								// Check to see if the API request
								// was valid.
								if (apiResponse.success){
 
									// Replace the list with the
									// current rating.
									list
										.empty()
										.append(
											"<li>Rating: " +
											apiResponse.data.toFixed( 1 ) +
											"</li>"
											)
									;
 
								}
							}
							});
 
						// Cancel default event.
						return( false );
					})
			;
 
			// Return jQuery object for chaining.
			return( this );
		};
 
 
		// When the DOM is ready, initialize the plugin.
		$(function(){
			$( "ul" ).rating( "rate_image.cfm" );
		});
 
	</script>
	<style type="text/css">
 
		ul.rating {
			height: 20px ;
			list-style-type: none ;
			margin: 20px 0px 0px 0px ;
			padding: 0px 0px 0px 0px ;
			}
 
		ul.rating li {
			float: left ;
			margin: 0px 5px 0px 0px ;
			padding: 0px 0px 0px 0px ;
			}
 
		ul.rating a {
			background-color: #F0F0F0 ;
			border: 1px solid #333333 ;
			color: #333333 ;
			float: left ;
			height: 20px ;
			line-height: 20px ;
			text-align: center ;
			text-decoration: none ;
			width: 20px ;
			}
 
	</style>
</head>
<body>
 <!--- --->
	<font face="Helvetica, Arial, sans-serif" color="#FFFFFF" size="3px">
		 Who is Chicagoland's best high school boys basketball player of all time?
	</font>
	<br />
<br />

	<cfoutput>

		<cfloop query="image">
 
			<div style="float: left ; margin-right: 10px ;">
 
				<img
					src="/rating/images/greatest/player#image.id#.jpg"
					width="105"
					style="display: block ;"
					/>
 
				<!--- Check to see if user has rated yet. --->
				<cfif image.has_existing_rating>
 
					<!---
						User has already rated, just show the
						current rating.
					--->
					<ul class="rating">
						<li>
							<font face="Helvetica, Arial, sans-serif" size="2px" color="##ffffff">Rating: #numberFormat(
								image.rating,
								"0.0"
								)#</font>
						</li>
					</ul>
 
				<cfelse>
 
					<!--- Show the rating options. --->
					<ul class="rating">
						<!---
							Set up the meta-data for this image.
							This data will be applied when the
							rating plugin is initialized.
						--->
						<script
							type="application/x-json"
							class="meta-data">
							{
								id: #image.id#
							}
						</script>
						<li>
							<a>1</a>
						</li>
						<li>
							<a>2</a>
						</li>
						<li>
							<a>3</a>
						</li>
						<li>
							<a>4</a>
						</li>
					</ul>
 
				</cfif>
 
			</div>
 
		</cfloop>
 
	</cfoutput>
 
</body>
</html>

Open in new window



rate_image.cfm:

 <cfset apiResponse = structNew()>
<cfset apiResponse.success = true>
<cfset apiResponse.errors = arrayNew(1)>
<cfset apiResponse.data = ""> 
 
<!--- Try to execute the api request / response. --->
<cftry>
 
	<!--- Param the FORM variable. --->
	<cfparam name="form.image_id" type="numeric" />
	<cfparam name="form.rating" type="numeric" />
 
 
	<!---
		Check to see if this user has already rated this image.
		We do not want to allow duplicate ratings.
	--->
	<cfquery name="existingRating" datasource="#application.dsn#"  username="#application.username#" password="#application.password#">
		SELECT
			r.id
		FROM
			rating r
		WHERE
			r.image_id = <cfqueryparam value="#form.image_id#" cfsqltype="cf_sql_integer" />
		AND
			r.ip_address = <cfqueryparam value="#cgi.remote_addr#" cfsqltype="cf_sql_varchar" />
		AND
			r.user_agent = <cfqueryparam value="#cgi.http_user_agent#" cfsqltype="cf_sql_varchar" />
	</cfquery>
 
 
	<!--- Check to see if the rating exists. --->
	<cfif existingRating.recordCount>
 
		<!--- Add error. --->
		<cfset arrayAppend(
			apiResponse.errors,
			"You have already rated this image."
			) />
 
	</cfif>
 
 
	<!--- Check to see if we have any errors. --->
	<cfif NOT arrayLen( apiResponse.errors )>
 
		<!--- Insert new rating. --->
		<cfquery name="insertRating" datasource="#application.dsn#" username="#application.username#" password="#application.password#">
			INSERT INTO rating
			(
				ip_address,
				user_agent,
				rating,
				date_created,
				image_id
			) VALUES (
				<cfqueryparam value="#cgi.remote_addr#" cfsqltype="cf_sql_varchar" />,
				<cfqueryparam value="#cgi.http_user_agent#" cfsqltype="cf_sql_varchar" />,
				<cfqueryparam value="#form.rating#" cfsqltype="cf_sql_integer" />,
				<cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp" />,
				<cfqueryparam value="#form.image_id#" cfsqltype="cf_sql_integer" />
			);
 
			<!--- Get the new overall rating. --->
			<!--- SELECT
				(
					SUM( r.rating ) /
					COUNT( r.rating )
				) AS overall_rating --->
				SELECT COUNT( r.rating ) AS overall_rating
			FROM
				rating r
			WHERE
				r.image_id = <cfqueryparam value="#form.image_id#" cfsqltype="cf_sql_integer" />
			;
		</cfquery>
 
 
		<!--- Set the current rating as the response data. --->
		<cfset apiResponse.data = insertRating.overall_rating />
 
	</cfif>
 
 
	<!--- Catch any api errors. --->
	<cfcatch>
 
		<!--- Set the error in our api response object.---> 
		<!---
		<cfset apiResponse.errors = [ cfcatch.message, cfcatch.detail ] />
 --->
  <cfset arrayAppend(apiResponse.errors, cfcatch.message)>
         <cfset arrayAppend(apiResponse.errors, cfcatch.detail )> 
	</cfcatch>
</cftry>
 
 
<!--- Check to see if we have any errors at this point. --->
<cfif arrayLen( apiResponse.errors )>
 
	<!--- Flag the API request as unsuccessful. --->
	<cfset apiResponse.success = false />
 
</cfif>
 
 
<!--- Searialize the API response into our JSON value. --->
<!---
<cfset jsonResponse = serializeJSON( apiResponse ) />
 --->
 
   <!--- <cfset obj = createObject("component", "path.to.CFJSON")> --->
   <cfset obj = createObject("component", "chicagoeliteclassic.rating.CFJSON")> 
           <cfset jsonResponse = obj.encode( apiResponse ) /> 
 
 
 
<!--- Convert the response string to binary for streaming. --->
<cfset binaryResponse = toBinary( toBase64( jsonResponse ) ) />
 
 
<!--- Stream the binary data back. --->
<cfheader
	name="content-length"
	value="#arrayLen( binaryResponse )#"
	/>
 
<cfcontent
	type="text/x-json"
	variable="#binaryResponse#"
	/>

Open in new window

0
swaggrK
Asked:
swaggrK
1 Solution
 
_agx_Commented:
Normally you'd have a separate "images" table that you'd populate with the image names and id's.  Then you could add/remove images without losing all the rating history associated with the id's.

          TABLE: Images  COLUMNS:  ID int identity, name varchar(200)

Just use the new table in your query instead of the SELECT .. UNION ALL stuff:

	SELECT
		i.id,
                i.name, 
		<!--- Get the current rating for the image. --->
		(
			CASE
				WHEN
					COUNT( r.rating ) > 0
				THEN
					(
						SUM( r.rating ) /
						COUNT( r.rating )
					)
				ELSE
					0
			END
		) AS rating,
 
		<!--- Query for existing rating by user. --->
		COALESCE( er.id, 0 ) AS has_existing_rating
	FROM  images i
	                  LEFT OUTER JOIN rating r ON i.id = r.image_id
	                 LEFT OUTER JOIN rating er ON er.image_id = i.id
			            AND er.ip_address = <cfqueryparam value="#cgi.remote_addr#" cfsqltype="cf_sql_varchar" />
			           AND er.user_agent = <cfqueryparam value="#cgi.http_user_agent#" cfsqltype="cf_sql_varchar" />
 
	GROUP BY
		i.id, i.name, r.image_id,er.id
	ORDER BY
		i.id ASC

Open in new window


If you can't create a new table, just add a "Name" column to the UNION ALL part.

	SELECT
		i.id,
                i.name, 
		<!--- Get the current rating for the image. --->
		(
			CASE
				WHEN
					COUNT( r.rating ) > 0
				THEN
					(
						SUM( r.rating ) /
						COUNT( r.rating )
					)
				ELSE
					0
			END
		) AS rating,
 
		<!--- Query for existing rating by user. --->
		COALESCE( er.id, 0 ) AS has_existing_rating
	FROM
               (
			SELECT 1 AS id, 'Name of first photo' AS Name UNION ALL
			SELECT 2 AS id, 'Name of second photo'  UNION ALL
			.... etc ....
			SELECT 8 AS id, 'Name of last photo'
		) AS i
 
	<!--- Join this to the rating table to get rating. --->
	LEFT OUTER JOIN
		rating r
	ON
		i.id = r.image_id
 
	<!---
		Join this to the rating table AGAIN to see if the current
		user has already rated the given image.
	--->
	LEFT OUTER JOIN
		rating er
	ON
		(
				er.image_id = i.id
			AND
				er.ip_address = <cfqueryparam value="#cgi.remote_addr#" cfsqltype="cf_sql_varchar" />
			AND
				er.user_agent = <cfqueryparam value="#cgi.http_user_agent#" cfsqltype="cf_sql_varchar" />
		)
 
	GROUP BY
		i.id, i.name, r.image_id,er.id
	ORDER BY
		i.id ASC

Open in new window


Then output the #name# beneath the image

<cfoutput>
           <cfloop query="image">
                  <img ..... >
                   <span> Player name: #name#</span>
                   ....
           </cfloop>
</cfoutput>
0
 
swaggrKAuthor Commented:
Thanks _agx_
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now