CODEWITHSUNDEEP
javascriptjqueryajaxcoldfusion
Steve
Steve

Reputation: 129

ColdFusion and AJAX - Updating Database

I'm wanting to have a system set up where users can go to a page where there's a list of items that they can rate (say, 1 through 5) from a drop-down. This list is going to get quite long thus it would be much more convenient if they could go through and rank each item without ever having to hit "Save."

I'm very much a novice when it comes to AJAX but I figure this can't be that difficult. I found an answer in a different discussion that I think is quite relevant but it doesn't provide enough information for me to know exactly what to do with it.

In short, how do I use AJAX, in congruence with jQuery and ColdFusion, to update the database without the need for a save/submit button?

CLARIFICATION: I should have clarified that the user can rate the items - not rank. Meaning that there's no #1, #2, #3, etc. Instead, each item can be rated on a scale from 1-5.

Currently I'm basing everything off of ".change()" when the user makes a selection in the drop-down. At which point I have two jQuery variables that are set to the "ID" of the item changed as well as its new "rating." I just need to find a way to use these two variables to update the table in the database.

Update

On the main page, I now have a simple jQuery AJAX post call: $.post('update.cfc', {ID: inputID, rating: selRat}) that is triggered .onChange(). "inputID" is the ID of the entry that I want to update in the database and "selRat" is the selected rating in the drop-down.

<cfcomponent output="false">
<cffunction name="updateRating" access="remote" output="false">

    <cfupdate datasource="#session.db#" name="update">
        UPDATE ajaxTest
        SET rating = #FORM.rating#
        WHERE ID = #FORM.ID#
    </cfupdate>

</cffunction>
</cfcomponent>

I'm currently getting a 500 (Internal Server Error).

Thoughts/suggestions?

Upvotes: 2

Views: 1677

Answers (1)

Regular Jo
Regular Jo

Reputation: 5510

Have you tried submitting the traditional way to this form to see what happens? You can also add some simple debugging like writing to a file to log the executed attempt?

One thing that stands out to me: Should the <cfupdate opening and closing tag be <cfquery I don't use cfupdate or cfinsert, but I don't think any cf flavor supports the syntax you're trying to use, which leads me to believe it's an easy mistake.

On another note, that query could be especially dangerous, and sorry to go off on a tangent..

If I submitted "0; DROP TABLE Users" for the value of ID, your Users table disappears (if you have a users table). I could also pass other common table names like Members, News, Pages, Content, CMS, Transactions. (I wouldn't do this, but you never know when another user might.)

There's a really easy solution, <cfqueryparam>. I'll let you research the tag, but I'll show you how to alter that particular query.

<cfquery datasource="#session.db#" name="update">
    UPDATE ajaxTest
    SET rating = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.rating#">
    WHERE ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.id#">
</cfquery>

CFQUERYPARAMing your variables stops the risk of this and passes the variable values as text rather than sql.

Upvotes: 3

Related Questions