Solved

jQuery Not Updating List Position

Posted on 2011-03-19
3
370 Views
Last Modified: 2012-05-11
I'm testing a sorting function which needs to update the SQL DB with the position of the photo on the fly. It sorts fine on the page but will not update the DB. My three files are below.

SORTINGDEMO.CFM

 
<html>
<head>
  <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
  <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.5/jquery-ui.min.js"></script>
  <style type="text/css">
  #sortable { list-style-type: none; margin: 0; padding: 0; width: 60%; }
  #sortable li { margin: 0 3px 3px 3px; padding: 0.4em; padding-left: 1.5em; font-size: 1.4em; width:200px; border:1px solid gray; background-color:#fff;}
  #sortable li span.godelete { float:right; margin-left:2px; padding:0px; background-image:url(delete.jpg); width:30px; height:30px; cursor:pointer;}
  #sortable li span.gotop { float:right; margin-left:2px; padding:0px; background-image:url(top.jpg); width:30px; height:30px; cursor:pointer;}
  </style>
  <script type="text/javascript">
  $(function() {
    $sortable = $("#sortable").sortable({
      update: function(event, ui){
        updateOrder();
      }
    }).disableSelection();
    
    //$('<span>delete</span>').css('float','right').appendTo( $('li', $sortable) ).click(function(){
	$('<span class="godelete"></span>').css('float','right').appendTo( $('li', $sortable) ).click(function(){
      $this = $(this);
      $this.parent().remove();
      updateOrder();
    });
    
    $('<span class="gotop"></span>').css('float','right').appendTo( $('li', $sortable) ).click(function(){
      $this = $(this);
      $li = $this.parent().detach();
      $li.prependTo($sortable);
      updateOrder();
    });

    function updateOrder(){
      $.getJSON(
        'sortable.cfc?wsdl',
        { 
          method : 'updateOrder', 
          key: '<cfoutput>#session.ajaxkey#</cfoutput>',
          orderedList: $sortable.sortable('toArray').toString(),
          returnformat: 'json', 
          queryformat: 'column'
        },
        callback
      );
    }
  });
  
  function callback(json){
    if ( json.RESULT )
    {
      $('#serverresult').text( "Order updated to: " + json.NEWORDER );
    }
    else
    {
      $('#serverresult').text( "Something went wrong!" );
    }
  }
  </script>
</head>
<body>
<div class="demo">

  <ul id="sortable">
  <cfquery name="getPhotoID" datasource="cranberryrealtor">
  	select *
    from mytable
    order by test_id
  </cfquery>
  	<cfoutput>
  	<cfloop query="getPhotoID">
    <li id="id_#id#"><img src="tn_#test_id#.jpg"></li>
    </cfloop>
    </cfoutput>
  </ul>

  <div id="serverresult">
  </div>

</div>

</body>
</html>

Open in new window


SORTABLE.CFC

 
<cfcomponent output="false" hint="I respond to AJAX requests">

  <!--- 
  --------------------------------------------
  Remote Methods 
  --------------------------------------------
  --->
  
  <cffunction name="updateOrder" output="false" access="remote">
    <cfargument name="orderedList" required="true" type="string">
    <cfargument name="key" required="true" type="string">
    
    <cfset var ndx = "">
    <cfset var id = 0>
    <cfset var position = 0>
    <cfset var updatedids = "">
    <cfset var result = {result="false"}>

    <!--- do a basic security check --->
    <cfif isAllowed( arguments.key )>
      
      <!--- Prevent race conditions --->
      <cflock name="updateOrder" timeout="60">
        <cftransaction>
          <cfloop list="#arguments.orderedList#" index="ndx">
          
            <cfset id = Val( ListLast( ndx, "_" ) )>
            <cfset position = position+1>
            <cfset updatedids = ListAppend( updatedids, id )>

            <cfquery datasource="cranberryrealtor">
              update myTable set
                order = <cfqueryparam value="#position#" cfsqltype="cf_sql_integer">
              where 
              id = <cfqueryparam value="#id#" cfsqltype="cf_sql_integer">
            </cfquery>

          </cfloop>
          
          <!--- delete any items not in the list --->
          <cfquery datasource="cranberryrealtor">
            delete from myTable
            where id not in ( <cfqueryparam value="#updatedids#" cfsqltype="cf_sql_integer" list="true"> )
          </cfquery>
          
        </cftransaction>
      </cflock>
      
      <cfset result = {result="true", neworder=updatedids, position=position }>

    </cfif>
    
    <cfreturn result>
  </cffunction>
  
  <!--- 
  --------------------------------------------
  Private Methods 
  --------------------------------------------
  --->
  
  <cffunction name="isAllowed" output="false" access="private" returntype="boolean">
    <cfargument name="key" required="true">
    <cfset var result = false>
    <!--- check that request is coming from the same browser that created the session --->
    <cfif IsDefined( "session.ajaxkey" ) AND ( session.ajaxkey eq arguments.key )>
      <cfset result = true>
    </cfif>
    <cfreturn result>
  </cffunction>
  
</cfcomponent>

Open in new window


APPLICATION.CFC

 
<cfcomponent output="false">
  
  <cfset this.name = Hash( GetCurrentTemplatePath() )> 
  <cfset this.sessionManagement = true>
  
  <cffunction name="onSessionStart" returnType="void" output="false">
    <!--- we'll use this key to provide some basic protection for the AJAX webservice --->
    <cfset session.ajaxkey = CreateUUID()>
  </cffunction>
  
</cfcomponent>

Open in new window


I'm a novice so please take it easy on the comments.

Thanks.
0
Comment
Question by:snobrdrt
  • 2
3 Comments
 
LVL 82

Expert Comment

by:leakim971
ID: 35173429
Do you have a link to see the page in action ?
0
 
LVL 1

Accepted Solution

by:
snobrdrt earned 0 total points
ID: 35212439
I ended up figuring this out. I was using the variable name "order" for the sequence order of the photos. this is a taboo word in SQL, for example ORDER BY.

I ended up changing the field name to SEQUENCE.

Thanks.
0
 
LVL 1

Author Closing Comment

by:snobrdrt
ID: 35239196
No answers.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

OverviewThis article demonstrates a simple search form using AJAX. The purpose of the article is to demonstrate how to use the same code to render a page and javascript (JQuery) and AJAX to make subsequent calls to refine the results. The princip…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now