Moving items between listboxes using javascript and submitting selections to database with Coldfusion

I  have a dynamically populated listbox where the user can select multiple authors for journal articles they are adding to my library.  However the list of authors, and the number of authors for one record (journal article), is so long that using ctrl/click to select each author is cumbersome and leads to many mistakes.  So, I decided to set it up so that the user can see which authors they currently have selected.  I did this by creating 2 listboxes and using javascript to allow the user to move author names back and forth between the boxes.  I know that when the user selects an author to add or remove, it simply modifies the listboxes client-side and not server side.  My question is: "How using Coldfusion do I write a method to add the authors the user has selected (moved to listbox 2) to my dbase?

I apologize in advance for my lack of knowledge.  This stuff is way outside my area of expertise (I'm a biologist).
<table width="100%" border="1">
      <tr>
        <td>
        <!--- Listbox 1 = Raw list of authors in the database --->
        <select name="Author" size="6" multiple>
          <option value="0">AUTHOR(S) NOT LISTED</option>
          <cfoutput query="rsAuthor">
            <option value="#rsAuthor.AuthorID#">
              <cfif IsDefined("rsAuthor.lastname") AND #rsAuthor.lastname# NEQ "">
                #rsAuthor.lastname#,
              </cfif>
              #rsAuthor.title# #rsAuthor.firstname# #rsAuthor.suffix#</option>
          </cfoutput>
        </select>
        </td>
        <td align="center" valign="middle">
          <!--- buttons for moving specific authors from Listbox 1 to Listbox 2  --->
          <input type="Button" value="Add >>" style="width:100px" onClick="SelectMoveRows(document.AddAuthor.Author,document.AddAuthor.AuthorID)"><br>
          <br>
          <input type="Button" value="<< Remove" style="width:100px" onClick="SelectMoveRows(document.AddAuthor.AuthorID,document.AddAuthor.Author)">
          </td>
        <td>
          <!--- Listbox 2 = User-selected list of authors for specific journal article i.e. record--->
        <select name="AuthorID" size="6" Multiple>
         </select>
         </td>
        </tr>
      </table>

Open in new window

RestonScaperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James RodgersWeb Applications DeveloperCommented:
need the supporting js and output as viewed form the browser

also, after the selections why not just submit the changes to a form processing page or use ajax to manage the changes directly?
<form action="saveChanges.cfm" method="post">
<table width="100%" border="1">
      <tr>
        <td>
        <!--- Listbox 1 = Raw list of authors in the database --->
        <select name="Author" size="6" multiple>
          <option value="0">AUTHOR(S) NOT LISTED</option>
          <cfoutput query="rsAuthor">
            <option value="#rsAuthor.AuthorID#">
              <cfif IsDefined("rsAuthor.lastname") AND #rsAuthor.lastname# NEQ "">
                #rsAuthor.lastname#,
              </cfif>
              #rsAuthor.title# #rsAuthor.firstname# #rsAuthor.suffix#</option>
          </cfoutput>
        </select>
        </td>
        <td align="center" valign="middle">
          <!--- buttons for moving specific authors from Listbox 1 to Listbox 2  --->
          <input type="Button" value="Add >>" style="width:100px" onClick="SelectMoveRows(document.AddAuthor.Author,document.AddAuthor.AuthorID)"><br>
          <br>
          <input type="Button" value="<< Remove" style="width:100px" onClick="SelectMoveRows(document.AddAuthor.AuthorID,document.AddAuthor.Author)">
          </td>
        <td>
          <!--- Listbox 2 = User-selected list of authors for specific journal article i.e. record--->
        <select name="AuthorID" size="6" Multiple>
         </select>
         </td>
        </tr>
      </table>
<input type="submit" value="Save Changes">
</form>

Open in new window

0
RestonScaperAuthor Commented:
Don't know anything about ajax and am not sure how to submit the changes to a form processing page  since the changes are client-side and the server is not "aware"  that items have been moved.  
<script language="Javascript">
function SelectMoveRows(SS1,SS2)
{
    var SelID='';
    var SelText='';
    // Move rows from SS1 to SS2 from bottom to top
    for (i=SS1.options.length - 1; i>=0; i--)
    {
        if (SS1.options[i].selected == true)
        {
            SelID=SS1.options[i].value;
            SelText=SS1.options[i].text;
            var newRow = new Option(SelText,SelID);
            SS2.options[SS2.length]=newRow;
            SS1.options[i]=null;
        }
    }
    SelectSort(SS2);
}
function SelectSort(SelList)
{
    var ID='';
    var Text='';
    for (x=0; x < SelList.length - 1; x++)
    {
        for (y=x + 1; y < SelList.length; y++)
        {
            if (SelList[x].text > SelList[y].text)
            {
                // Swap rows
                ID=SelList[x].value;
                Text=SelList[x].text;
                SelList[x].value=SelList[y].value;
                SelList[x].text=SelList[y].text;
                SelList[y].value=ID;
                SelList[y].text=Text;
            }
        }
    }
}
 
</script>

Open in new window

0
James RodgersWeb Applications DeveloperCommented:
>>Don't know anything about ajax
ok, no ajax

>>and am not sure how to submit the changes to a form processing page  since the changes are client-side and the server is not "aware"  that items have been moved.  

yes the changes are client side but they can still be submitted via a form

in the example i gave above you would make  a cfm page to accept the new input values and process them for the updates, the way i usually manage that is clear all existing selection from the db and then add the updated items from the form submission
0
RestonScaperAuthor Commented:
Creating a cfm page to accept the new input values and process them would not work until I figured out how to make the server recognize the items that had been selected.  The (typical) listbox function merely moves the items back and forth and does not "select" any of the items to be sent along with the post. However, I found an example that has the added function "selectAll " which loops through the righthand box and "selects" all the items (using the function "selectAll") that have been moved to the box before the form is submitted. The function selectAll is called in the onClick event of the submit button.
Take a look at: http://www.netevolution.co.uk/scripts.asp?ID=86

Also, I'm attaching the code as revised for my purposes.


<!--- create a unique id for the document --->
<cfif NOT IsDefined("Session.NEWUUID")>
<cflock timeout="20" scope="session" type="exclusive">
  <cfset Session.NEWUUID  = createUUID() />
  </cflock>
</cfif>
 
<!--- This Javascript code moves form elements from one select box to the other. It allows for multiple items to be moved at once, and all items are sorted as they are moved.  Also includes the function selectAll which loops through the righthand box and selects all the of the items that had been moved to the righthand box to be sent along with the post. --->
 
<script language="JavaScript" type="text/JavaScript">
    function move(fbox, tbox) {
        var arrFbox = new Array();
        var arrTbox = new Array();
        var arrLookup = new Array();
        var i;
        for (i = 0; i < tbox.options.length; i++) {
            arrLookup[tbox.options[i].text] = tbox.options[i].value;
            arrTbox[i] = tbox.options[i].text;
        }
        var fLength = 0;
        var tLength = arrTbox.length;
        for (i = 0; i < fbox.options.length; i++) {
            arrLookup[fbox.options[i].text] = fbox.options[i].value;
            if (fbox.options[i].selected && fbox.options[i].value != "") {
                arrTbox[tLength] = fbox.options[i].text;
                tLength++;
            } else {
                arrFbox[fLength] = fbox.options[i].text;
                fLength++;
            }
        }
        arrFbox.sort();
        arrTbox.sort();
        fbox.length = 0;
        tbox.length = 0;
        var c;
        for (c = 0; c < arrFbox.length; c++) {
            var no = new Option();
            no.value = arrLookup[arrFbox[c]];
            no.text = arrFbox[c];
            fbox[c] = no;
        }
        for (c = 0; c < arrTbox.length; c++) {
            var no = new Option();
            no.value = arrLookup[arrTbox[c]];
            no.text = arrTbox[c];
            tbox[c] = no;
        }
    }
    function selectAll(box) {
        for (var i = 0; i < box.length; i++) {
            box[i].selected = true;
        }
    } // End -->      
</script>
 
<!--- Form with left- and right-hand Listboxes along with buttons to move selections between listboxes. --->
<form name="form1" method="post" action="">
 <table border="0" cellspacing="2" cellpadding="0">
    <tr align="center" valign="middle">
      <td align="right">
      <select name="AuthorID1" size="15" multiple id="AuthorID1" style="width:250">
            <option value="0">AUTHOR(S) NOT LISTED</option>
          <cfoutput query="rsAuthor">
            <option value="#rsAuthor.AuthorID#">
               <cfif IsDefined("rsAuthor.lastname") AND #rsAuthor.lastname# NEQ "">
                #rsAuthor.lastname#,
              </cfif>
              #rsAuthor.title# #rsAuthor.firstname# #rsAuthor.suffix#</option>
          </cfoutput>
        </select></td>
      <td>
      <input type="button" onClick="move(this.form.AuthorID,this.form.AuthorID1)" 
      value="<< Remove" style="height:50">
      <input type="button" onClick="move(this.form.AuthorID1,this.form.AuthorID)" 
      value= "Add >>" style="height:50">
      </td>
      <td align="left">
      <select name="AuthorID" size="15" multiple id="AuthorID" style="width:250">
      </select>
      </td>
    </tr>
  </table>
</form>
 
<!--- Code to insert the AuthorIDs into database once form has been submitted --->
<cfif IsDefined("FORM.AuthorID") AND FORM.AuthorID NEQ "0">
  <cfloop list="#Form.AuthorID#" index="item">
	<cfquery name="AuthorID" datasource="sii_contaminants">
		INSERT INTO doc_author (DOCUUID, AuthorID)
		VALUES (
		<cfif IsDefined("Session.NEWUUID")>
		<cfqueryparam value="#Session.NEWUUID#" cfsqltype="cf_sql_clob" maxlength="45">
		<cfelse>
		''
		</cfif>
		,
		<cfif IsDefined("FORM.AuthorID") AND #FORM.AuthorID# NEQ "0">
		<cfqueryparam value="#item#">
		<cfelse>
		NULL
		</cfif>
		)
	</cfquery>
  </cfloop>
</cfif>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.