How can I create a rating system on my website where people can rate a page, and their rating goes into a database. Then, the average of all user's ratings appears on the page?
Now this I'm sure is not the best way to do this but i hope atleast it gets you pointed in some sort of direction. I used a rock band as an example. Heres what the coldfusion may look like to start with. This will insert the users rate choice from the form into your database. <cfparam name="form.rate" default=""> <cfif form.rate neq ""> <cfquery datasource="#datasource#" name="rate"> INSERT INTO TABLE.NAME (RATEID, BANDID, RATING) VALUES ('#FORM.RATEID#', '#FORM.BANDID#', '#FORM.RATE#') </cfquery> You gave this band a <cfoutput>#form.rate#</cfoutput> <cfelse> Rate this band </cfif> Code (markup): And heres what the form may look like: <form id="form1" name="form1" method="post" action=""> <input type="hidden" name="rateid" value="<cfoutput>#create.uuid#</cfoutput>" /> <input type="hidden" name="bandid" value="<cfoutput>#bandid#</cfoutput>" /> <p> <table> <tr> <td>1</td><td>2</td><td>3</td><td>4</td> </tr> <tr> <td> <input type="radio" name="rate" value="1" id="rate_0" /> </td> <td> <input type="radio" name="rate" value="2" id="rate_1" /> </td> <td> <input type="radio" name="rate" value="3" id="rate_2" /> </td> <td> <input type="radio" name="rate" value="4" id="rate_3" /> </td> <tr> <td colspan="4"> <input name="" type="submit" /> </td> </tr> </tr> </table> </p> </form> Code (markup): If this helps then great if not I'm truly sorry. I know that this is only half way there because we still need to return the results and average them out. That is not hard but I think for now one thing at a time is best.
OK... so this is what I have on my page: <cfparam name="form.rate" default=""> <cfif form.rate neq ""> <cfquery datasource="mydatabase" name="rate"> INSERT INTO TABLE.RATE (RATE_ID, BAND_ID, RATING) VALUES ('#FORM.RATE_ID#', '#FORM.BAND_ID#', '#FORM.RATING#') </cfquery> You gave this band a <cfoutput>#form.rating#</cfoutput> <cfelse> Rate this </cfif> <form id="form1" name="form1" method="post" action=""> <input type="hidden" name="RATE_ID" value="<cfoutput>#create.uuid#</cfoutput>" /> <input type="hidden" name="BAND_ID" value="<cfoutput>#BAND_ID#</cfoutput>" /> <p> <table> <tr> <td>1</td><td>2</td><td>3</td><td>4</td><td>5</td> </tr> <tr> <td> <input type="radio" name="rate" value="1" id="rate_0" /> </td> <td> <input type="radio" name="rate" value="2" id="rate_1" /> </td> <td> <input type="radio" name="rate" value="3" id="rate_2" /> </td> <td> <input type="radio" name="rate" value="4" id="rate_3" /> </td> <td> <input type="radio" name="rate" value="5" id="rate_4" /> </td> <tr> <td colspan="5"> <input name="" type="submit" /> </td> </tr> </tr> </table> </p> </form> Code (markup): Now, it seems there's a problem with the following code: INSERT INTO TABLE.RATE (RATE_ID, BAND_ID, RATING) VALUES ('#FORM.RATE_ID#', '#FORM.BAND_ID#', '#FORM.RATING#') Code (markup): It seems that Coldfusion does not recognize the INSERT INTO TABLE code. Do you know what may solve this? Also, How do I get this data to insert into my database (mydatabase.mdb)? Also, my database has the equivalent of a table with all the Band names. So, I was thinking of using the band_id from this table, as well as my RATE table, with some sort of INNER JOIN to be able to average the results to display on the webpage. Ideas?
Well, lets start with INSERT INTO TABLE.RATE (RATE_ID, BAND_ID, RATING) VALUES ('#FORM.RATE_ID#', '#FORM.BAND_ID#', '#FORM.RATING#') Code (markup): Change the "TABLE.RATE" to the name of the table that is in your database in which you will store the ratings. As long as you have the correct datasource name in the <cfquery> tag and all the cloumn names match exactly, it should work. For instance in your database dns is "mydatabase" then this should go in the datasource attribute of the cfquery tag. then if the table name is band_ratings this should go after INSERT INTO statment then after that, all the columns you list should match the names exactly how they are in the table in your database. i.e. <cfquery dayasource="mydatabase" name="rate"> INSERT INTO BAND_RATING (RATE_ID, BAND_ID, RATING) VALUES ('#FORM.RATE_ID#', '#FORM.BAND_ID#', '#FORM.RATING#') </cfquery> Code (markup): can you post the error you get from coldfusion?
I'm having a second thought on how to set up the database. You can create new tables and edit the exitsting ones in your database right? If so I may have a better idea that might work a little easier.
Thank you for the response. I also did some fooling around before your last post. I managed to get it to work as such... Database: mydatabase.mdb Form page: teststar1.cfm Action page: teststar2.cfm Database setup: TBLBANDS: Includes: BAND_ID, BAND_NAME RATE: Includes: RATE_ID, RATE, BAND_ID Here is teststar1.cfm: <form action="teststar2.cfm" method="post"> <p> I want to rate this band: <table> <tr> <td>1</td><td>2</td><td>3</td><td>4</td><td>5</td> </tr> <tr> <td> <input type="radio" name="rate" value="1" id="rate_0" /> </td> <td> <input type="radio" name="rate" value="2" id="rate_1" /> </td> <td> <input type="radio" name="rate" value="3" id="rate_2" /> </td> <td> <input type="radio" name="rate" value="4" id="rate_3" /> </td> <td> <input type="radio" name="rate" value="5" id="rate_4" /> </td> <tr> <td colspan="5"> <input name="" type="submit" /> </td> </tr> </tr> </table> </p> </form> Code (markup): The above displays the 5 radio buttons to rate 1 through 5. Then, the action page, teststar2.cfm is as follows: <body> <!--- Insert the new record ---> <cfinsert datasource="mydatabase" tablename="RATE"> <h1>Rating added</h1> <cfoutput>You have added your rating. </cfoutput> </body> Code (markup): This seems to work properly. It adds the rating chosen to the RATE table. It assigns a new RATE_ID (via autonumber), and gives the chosen rating under RATE. However, the BAND_ID is constantly "0". I want to be able to control this value. So, if the user is on a specific "band" page (Say, Stone Temple Pilots... and they have a BAND_ID of 5)... i'd want any rating done by that specific page to have a BAND_ID value of 5 to be inserted in the database with the rating. Does this make sense? Next, how do I get to display an average rating value, for all ratings that have a certain BAND_ID number? So, average all ratings for entries with a BAND_ID of 5 for the Stone Temple Pilots page? (Is there like a <cfoutput query> average(RATE) for all BAND_ID=5 <cfoutput> Code (markup): type script?)
It looks like you need to add a hidden input tag named "BAND_ID" to your form. Then assign the correct band_id value. <input type="hidden" name="band_id" value="5"/> Code (markup): The <cfinsert> tag should take care of the rest automatically. I assume you are using the autonumber feature with Ms Access. Just a suggestion, you may want to change to using coldfusions <cfoutput>#createuuid()#</cfoutput> to make your unique id's. I used to use access as well, then I realized that my database was going to outgrow access and had to switch to MySQL. When I moved everything over I had to create a unique id for every entry and it took a long time. If you are not that far along you may want to start just using coldfusions unique id function. heres how it would look <input type="hidden" name="RATE_ID" value="<cfoutput>#createuuid()#</cfoutput>"/> Code (markup): So instead of letting msaccess create the id you are inserting it yourself via the <cfoutput>#createuuid()#</cfouput> tag. If you decied to go this route just note that once you change the data type from "autonumber" to anything else YOU CANT GO BACK! the autonumber function wont work anymore. the createuuid() will always make a unique id. I'm working on the averaging of all the ratings. Talk to you soon!
Thanks a bunch. I think this will work (I was wondering how to add hidden form fields). Also, cfStarlight answered the AVG question in another post I made this morning: http://forums.digitalpoint.com/showthread.php?t=555611 Thanks!!!
Thats great. CfStarlight is really helpful, I dont think I would be anywhere close to where www.UnitedLocalBands.com is, if it wasnt for CfStarlight. I checked out the other post. Looks great, heres what i came up with anyway, Only thing is that i dont know if mine will work with access. works fine with Mysql though. <cfquery datasource="yourdatasource" name="average"> SELECT AVG(RATE) AS AVG_RATE FROM USER_RATE WHERE BAND_ID = 5 </cfquery> <cfoutput>#AVERAGE.AVG_RATE#</cfoutput> Code (markup):
If you like you can check out the band rating feature we have developed at http://www.unitedlocalbands.com/use...m?groupid=95E50997-AE4D-6C28-4C035DA19DE1D3C2 This is the result of your post. It works pretty good. Thanks for the idea!
Awesome. Yeah, it all works well. Per your comment about the unique ID: So, does this place the unique number in the KEY_ID field in my access database? Can you explain this a bit more to help me understand it? Thanks!
Also, now that this works... I can get the rating average on my page. How can I create a neat little "star graphic" to go with the ranking? For instance, if a particular "band" has an average star rating of 4 out of 5.... how can I make a graphic to be displayed to show 4 stars lit up out of 5?
I found a css based star rating script. http://komodomedia.com/blog/index.php/2007/01/20/css-star-rating-redux/ you can download it from this website and try to alter it to work for you. I'm trying to make it work this coldfusion supplying the average number so the stars repersent the correct average rating of the band.
Also about the unique id: The quick and short anwser is yes So Lets say you wanted to use the #creatuuid()#. you would create a hidden field in the form to be submited. All you have to make sure of is that you name this hidden field the same name and the unique id column in the table your going to insert this information. <input type="hidden" name="KEY_ID" value="<cfoutput>#createuuid()#</cfoutput>"/> Code (markup): Then when you submit the form to the <cfinsert> tag it would automaticaly insert all the fields from the form to their apropreate columns in the table.
Another quick question - probably really simple. How can I make the following code a "drop down list" a user can choose from to rate something, rather than radio buttons? <table> <tr> <td>1</td><td>2</td><td>3</td><td>4</td><td>5</td> </tr> <tr> <td> <input type="radio" name="rate" value="1" id="rate_0" /> </td> <td> <input type="radio" name="rate" value="2" id="rate_1" /> </td> <td> <input type="radio" name="rate" value="3" id="rate_2" /> </td> <td> <input type="radio" name="rate" value="4" id="rate_3" /> </td> <td> <input type="radio" name="rate" value="5" id="rate_4" /> </td> <tr> <td colspan="5"> <input name="" type="submit" /> </td> </tr> </tr> </table> Code (markup):
I found the answer online: <FORM METHOD="POST" ACTION="nextpage.cfm"> <SELECT SIZE="1" NAME="Fruits"> <OPTION>Select an item</OPTION> <OPTION VALUE="apples">Apples</OPTION> <OPTION VALUE="oranges">Oranges</OPTION> <OPTION VALUE="peaches">Peaches</OPTION> </SELECT> <INPUT TYPE="submit" VALUE="Submit Fruit"> </FORM> Code (markup):
Yes thats correct. just know that once you change from "autonumber" to "number" you cant go back. Ms Access wont let you. When I had to generate new KEY_ID's for all my entrys I used this code <form action="" method="post"> <input type="text" name="newid" value="<cfoutput>#createuuid()#</cfoutput>"/> <input type="submit"> </form> Code (markup): I refereshed the form to generate a new KEY_ID, then I copy and paste the new KEY_ID to the database table. It took me the whole night.
Does the MS Access autonumber feature only go up to a certain number? Is this why you chose your method?
I decieded to go this route because I have many different tables and I know that i will always be adding more tables and I hope some day there will be many entries with lots of people adding content. So I wanted to make sure that every record had a unique id. With autonumber there are just way tomany simalar KEY_ID's Also MySql doesent have an autonumber feature So I really didnt have a choice. Everyone I talked to suggested that I switch before my database got to big. So I switch from access to mysql.