Query outputting all rows, I just want one.

Report · Jan 29, 2014

On a form, when submited contains a few variables in hidden form controls.

There is a loop in the form that outputs <li> items from a database, and a button with each list item, you click the button, it deletes that item.

But...it looks like the form is passing the IDs for all the <li> items. I just want to pass the id of one item. I am getting an error "Invalid data (then a list of all the IDs) for CFSQLTYPE CF_SQL_DECIMAL.

The button in row 5 and the <li> in the loop starting on line 3 are the question.

<form action="update-sort.cfm?faculty_id=<cfoutput>#qGetFacultyCV.faculty_id#</cfoutput>" name="sort_serialized"method="post" id="frm-sort">
  <ul name="anitem" id="sortable">
    <cfloop query="qGetFacultyCV">
        <button OnClick="document.forms.action = 'delaction.cfm'">X</button>
        <cfoutput><li id="section_id_#qGetFacultyCV.id#" class="ui-state-default">#qGetFacultyCV.full_citation#</li>
         <input type="hidden"  name="id" value="#id#"/>
      </cfoutput>
    </cfloop>
  </ul>
  <input type="submit" id="deleteBtn"name="delete" value="Delete Publication"/>
 <input type="submit" value="ADDPUB.nextval" style="display:none">
  <input type="submit" id="saveBtn" value="Save" style="display:none" OnClick="document.forms['sort_serialized'].action = 'addaction.cfm'" >
  <input type="submit" id="editBtn" value="Save"style="display:none" OnClick="document.forms['sort_serialized'].action = 'updateaction.cfm'">
  <input type="submit" id="sortBtn" value="Save"style="display:none" OnClick="document.forms['sort_serialized'].action = 'update-sort.cfm?faculty_id=<cfoutput>#qGetFacultyCV.faculty_id#</cfoutput>'" >
  <input type="hidden" name="faculty_id" id="faculty_id" value="<cfoutput>#qGetFacultyCV.faculty_id#</cfoutput>" />
</form>

The action page is simply

<cfquery name="addpubnumber" datasource="db_cie">
INSERT INTO PUBLICATIONS (id, full_citation, faculty_id)
VALUES (ADDPUB.nextval, '#full_citation#', '#faculty_id#')
</cfquery>

Report · Jan 29, 2014

You have a <button> element that, when clicked, changes your form's 'action' attribute to 'delaction.cfm', but nothing more. This in no way flags the item as being intended for deletion as you did not change the state of any element to reflect this selection. After the button you have a hidden field (id) with the value of that id. As is, this element will always be submitted each time you submit the form. Also, the button here does not submit the form, it merely changes an attribute value.

Then below you have a submit button that performs a submission of the form.

Try outputting this on each <li>

And hide it if need be. Because it is disabled by default, when the form is submitted, it will not pass its value along. Then, just use some Javascript to remove/add the disabled attribute to that element when they click on the per-<li> delete button. When the click on it (to mark it for deletion), JavaScript can turn off the 'disabled' attribute (and vice-versa)

That way, when you hit the submit button, only non-disabled fields will submit, and if there is more than 1 <li> marked for deletion, the FORM.listOfIDsToDelete will contain a comma-delimited list of IDs.

Report · Feb 03, 2014

What you observe is in fact the expected behaviour. By default, when a form is posted, all input form-fields of type 'hidden' are submitted.

My suggestions are as follows:

1) Use cfselect. It fits the situation you describe much better.

2) Give each submit field a name.

3) Avoid the duplication in posting qGetFacultyCV.faculty_id as a URL variable in the form's action and as a hidden form field. I would post it as just a hidden field. This has the advantage of being more secure.

4) It appears that addPub is a struct. If so, then replace ADDPUB.nextval with #ADDPUB.nextval# as the value of the form field.

5) Use cfqueryparam.

When you put it all together, the result will be somelike

</form>

and on the action page

INSERT INTO PUBLICATIONS (id, full_citation, faculty_id)

VALUES (<cfqueryparam cfsqltype="cf_sql_integer" value="#form.added_id#">,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.citation#">,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.faculty_id#">)

</cfquery>