Update Score to Overwrite Previous Score

I have a page that lists poem titles, average scores and lists the judges who have scored the poems. These items are in 3 columns. When the judge clicks on the poem title, he is taken to the poem overview page that displays the poem content and allows the judge to rate the poem from 1 to 3. If the judge has already scored the poem, his score is loaded in the drop down list and a message appears saying he has already scored it and if he changes the score, it will update his score. My update query is not working but I wonder if it is due to a logic issue on my behalf.

The full code in question is here. The update query starts on line 86. My thinking is if there is a score already, the arrDuplicate array will display a m开发者_如何学Cessage saying saying and load the previous score in the drop down list. The judge may change the score if desired. However, the way things are now, the score is being written to a new record each time and not updating the existing score.

The PoemScores table consists of poemID, judgeID, and score. There is no pk. Should there be?

---

You will need to add

AND judgeID = ?

to your query, otherwise it will try to update every score for that particular poem.

---

Add cfqueryparam to the duplicateCheck, updateScore and qRatePoem queries, primarily to avoid SQL injection.

You can change this

<cfif duplicateCheck.recordCount GT '0'>

(which is a string comparison) to this (numeric comparison)

<cfif duplicateCheck.recordCount GT 0>

or even just this (boolean)

<cfif duplicateCheck.recordCount>

These 2 IF statements are identical, so if there are no errors, you will do both an Update then an Insert. You need to rethink your logic.

<cfif NOT ArrayLen( arrErrors ) >
        <cfquery name="updateScore" datasource="#request.dsn#">
                UPDATE PoemScores
                SET score = '#form.rating#'
                WHERE poemID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#url.id#">
        </cfquery>
</cfif>


<cfif NOT ArrayLen( arrErrors )>
        <!--- INSERT DATABASE QUERY--->
        <cfquery name="qRatePoem" datasource="#request.dsn#">
                INSERT INTO PoemScores (poemID, score, judgeID)
                VALUES ('#url.id#', '#form.rating#', '#session.username#')
        </cfquery>

        <!---<cflocation url="dash.cfm" addtoken="no">--->
</cfif>

You probably want to add an extra IF statement here checking if arrDuplicates also has length. If it does, they've previously scored this, do the update query. Otherwise do the insert.

<cfif NOT ArrayLen( arrErrors ) >
   <cfif ArrayLen(arrDuplicate)> <!--- already submitted a score for this poem --->
        <cfquery name="updateScore" datasource="#request.dsn#">
                UPDATE PoemScores
                SET score = '#form.rating#'
                WHERE poemID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#url.id#">
        </cfquery>
   <cfelse>
        <!--- INSERT DATABASE QUERY--->
        <cfquery name="qRatePoem" datasource="#request.dsn#">
                INSERT INTO PoemScores (poemID, score, judgeID)
                VALUES ('#url.id#', '#form.rating#', '#session.username#')
        </cfquery>
   </cfif>
        <!---<cflocation url="dash.cfm" addtoken="no">--->
</cfif>