Skip to main content
A
Anonymous
November 8, 2007
Question

Looping over an update query

  • November 8, 2007
  • 5 replies
  • 577 views
Hello everyone. I am trying to update individual rows in my db table by using cfloop. I am having trouble figuring out the WHERE section of my update query. As it stands right now, I update every row with the same values instead of stepping through each row.

<!--- Update multiple rows --->
<cfset id = '#form.id#'>
<cfset counter = '#form.counter#'>
<cfset loop_value = '#form.loop_value#'>

<cfloop index="loop" from="1" to="#loop_value#" step="1">

<cfset name = form["name" & "#counter#"]>
<cfset title = form["title" & "#counter#"]>

<cfquery name="update" datasource="mydb">
UPDATE names
SET
group_officer_name = '#group_officer_name#',
group_officer_title = '#group_officer_title#'
WHERE
id = '8'
</cfquery>
<cfset counter = ('#counter#' - 1)>
</cfloop>

WHERE id = '8' needs to be the primary key for each row but I'm not sure how to do that since the values come from a dynamic form where the unique values are designated by the form value name with a #counter# attached to the end. If I do that for the primary key the row won't update at all.
    This topic has been closed for replies.

    5 replies

    A
    Anonymous
    November 8, 2007
    Let me plug in JR's solution and see what happens...
    N
    Newsgroup_User
    Inspiring
    November 8, 2007
    Then you just make a dynamic hidden field that you give a value of the
    primary key for each dynamic set of fields.


    <cfoutput query="MyQuery">
    <input type="text" name="name#MyQuery.currentRow#"
    value="#myquery.name#">
    <input type="text" name="title#MyQuery.currentRow#"
    value="#MyQuery.title#">
    <input type="hidden" name="key#MyQuery.currentRow#"
    value="#MyQuery.PK#">
    </cfoutput>

    Your action page can then associate the value of form.key3 with
    form.name3 and form.title3 and build the correct update query.

    As JR said. One should validate the data because a nefarious user can
    send bogus data to your action page.
    A
    Anonymous
    November 8, 2007
    I can't do it that way because like I said before the form output itself is dynamic so on the other form values I can attach a #counter# variable but can't do that for the primary key since the addition of the #counter# makes the value invalid.
    P
    paross1
    Participating Frequently
    November 8, 2007
    Looks like you are out ot luck unless you find a way to correlate the PK with the update fields.

    Phil
    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    November 8, 2007
    See code below. If this does not help you please post all your code and explain precisely what you are trying to accomplish.

    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    November 8, 2007
    You could pass your primary key value using a hidden form field. Just make sure you validate that the user has authority to update the records in question in case someone alters the form field values.

    I also recommend you use cfqueryparam inside your cfquery.
    N
    Newsgroup_User
    Inspiring
    November 8, 2007
    Your form would need to somehow pass in the primary key to associate
    with the data you want to update. Either another field, usually hidden,
    that contains the key associated to the same 'counter' value as the rest
    of the related data or the counter value is itself the key.

    Then you would use this value in your update query where clause.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices