1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Separating values from check list into query

Discussion in 'Programming' started by kimimaro, Dec 21, 2008.

  1. #1
    Hi I am new to coldfusion and I have been stucked doing a function for 2 days without avail. To put it short, I am trying to create a queries in table format and with each record having a checkbox. When the users click and tick on any of the checkbox, the query will be able to edit all the records that have been ticked.

    This is the code i use to display my queries



    <cfoutput query="qi">




    <tr>

    <td>


    <input type="checkbox" name="check" value="#qi.ic#">


    </td>

    <td> #qi.fname#</td>

    <td> #qi.ic#</td>
    <td> #qi.contact#</td>
    <td> #qi.position#</td>
    <td> #qi.grade#</td>
    </tr>

    </cfoutput>





    And this is my coding of what action should take if the user click on the checkbox and submit the form



    <cfif isdefined("form.accept")>

    <cfoutput query="qi">
    <cfif isdefined("form.check") AND form.check EQ "on">
    <cfquery name="qu" datasource="#application.fw.config.dsn#">

    UPDATE applicant
    SET status = 'accepted'
    where ic = '#form.check#'


    </cfquery>






    I have tried all possibilities. The problem is whatever that has been ticked will be output as record1,record2

    which is separated by a comma. I cant separate the result in which i can update the database on the ticked records only. I found out that whenever you tick any of the checkbox, the system will treat it as "As long as one box is ticked the whole query record will be updated" instead of "Only update those record which is ticked". Please help me out im really clueless
     
    kimimaro, Dec 21, 2008 IP
  2. kimimaro

    kimimaro Peon

    Messages:
    23
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    I have thought of an idea which is :



    <cfoutput query="qi">
    <cfparam name="number" default="1">

    <cfif currentrow mod 2>
    <cfset rowcolor = "##A6D2DD">
    <cfset fontcolor = "##000000">

    <cfelse>
    <cfset rowcolor = "##E2E6F2">
    <cfset fontcolor = "##000000">
    </cfif>


    <tr bgcolor="#rowcolor#" onMouseOver="this.bgColor='##259C41';" onMouseOut="this.bgColor='#rowcolor#'; ">

    <td>


    <input type="checkbox" name="check#number#" value="#qi.ic#">


    </td>

    <td> #qi.fname#</td>

    <td> #qi.ic#</td>
    <td> #qi.contact#</td>
    <td> #qi.position#</td>
    <td> #qi.grade#</td>
    </tr>
    <cfset number = number + 1>
    </cfoutput>


    By including the #number# this will specified that each check list is listed as check1, check2, check3 ...so on to uniquely distinguish each checkbox.


    But the problem is the action that is going to take afterwards which is





    <cfif isdefined("form.accept")>


    <cfparam name="nombor" default="1">

    <cfoutput query="qi">
    <cfif isdefined("form.check#nombor#") AND form.check#nombor# EQ "on">
    <cfquery name="qu" datasource="#application.fw.config.dsn#">

    UPDATE applicant
    SET status = 'terima'
    where ic = '#form.check#nombor##'


    </cfquery>


    <cfscript>
    application.fw_notice("Added #qi.ic#");
    </cfscript>

    </cfif>

    <cfset nombor = nombor + 1>

    </cfoutput>
    <cfreturn>

    </cfif>



    The real problem is this line :

    <cfif isdefined("form.check#nombor#") AND form.check#nombor# EQ "on">

    I dont know how to put a variable inside a CF tag so it remains as a variable.. My purpose is to make it loop
    from form.check1, form.check2, form.check3 until the end of the query... Please help
     
    kimimaro, Dec 21, 2008 IP
  3. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #3
    The checkbox value should be the id, not "on".


    FORM is a structure. You can use array notation to access dynamically named fields:

    Code (Text):
    1.  
    2. <cfif FORM["check"& nombor] ...>
    3. ...
    4. </cfif>
    5.  

    Yes, that is a good approach if each record may be updated with different values. But it looks like you are updating a bunch of records with the same value. For example, marking all of the checked records with the status "accepted":

    UPDATE applicant
    SET status = 'accepted'
    where ic = '#form.check#'

    If that is the case I would use your original code, with one change. In your query, use an IN clause. It will allow you to pass in a list of id's, instead of just one. So any record matching one of those ids will be updated.

    Code (Text):
    1.  
    2. <cfset form.check = "1,15,6,8,9">
    3. <cfif isDefined("form.check") and listLen(form.check)>
    4.    <!--- change the cfsqltype to match your column type --->
    5.    <cfquery ...>
    6.    UPDATE applicant
    7.    SET status = 'accepted'
    8.    where ic IN
    9.    (
    10.    <cfqueryparam value="#form.check#" list="true"
    11.          cfsqltype="cf_sql_integer">
    12.    )
    13.    </cfquery>
    14. </cfif>
    15.  
     
    cfStarlight, Dec 22, 2008 IP
  4. kimimaro

    kimimaro Peon

    Messages:
    23
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Hi, I sincerely thank you for your help!

    I was amazed how only two pieces of code can make a difference!

    What does they do? Please do enlighten me and thanks again :)
     
    kimimaro, Dec 22, 2008 IP
  5. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #5
    1. Array notation lets you access dynamically named fields
    2. The sql IN (...) clause lets you update multiple records in a single query.
     
    cfStarlight, Dec 23, 2008 IP
  6. kimimaro

    kimimaro Peon

    Messages:
    23
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Thanks for the previous help... How about if i would like to separate these values (where the form is checked) and insert into a new table?

    I try to search the net and somehow people are using arrays but i dont quite get it. Can someone give me a simple example using my coding?

    Sorry for the trouble I learn faster by example
     
    kimimaro, Jan 6, 2009 IP
  7. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #7
    You can use arrays, but a simple method is to use <cfloop list="...">. It is used to loop through a _list_ of values. Inside the loop you can do your INSERT:


    <cfparam name="form.check" default="">
    <cfloop list="#form.check#" index="currentValue">

    <!--- change the cfsqltype to match your column type --->
    <cfquery ...>
    INSERT INTO YourTable ( TheColumnName )
    VALUES
    (
    <cfqueryparam value="#currentValue#" cfsqltype="cf_sql_integer">
    )
    </cfquery>
    </cfloop>
     
    cfStarlight, Jan 7, 2009 IP
  8. kimimaro

    kimimaro Peon

    Messages:
    23
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #8
    Ah thanks alot !!!! I truly appreciate that... Though im still blur what is all the code is doing... When does the insert examples and the update examples that you show actually separate the values? From what I know is that the always group in a single value like "value1,value2,value3" but how did you separate them using the <cfloop>??

    You are really a genius and a good samaritan :eek:
     
    kimimaro, Jan 7, 2009 IP
  9. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #9
    Cfloop does it auto-magically ;-) When you use "list" ColdFusion assumes the value is a list of elements separated by a comma. It loops through the list, one-by-one, and each time it populates the "index" variable with the value of the current element.

    For example, run this code. It will print out 3 lines, one for each element in the list:

    <cfset form.check = "value1,value2,value3">

    <cfoutput>
    <cfloop list="#form.check#" index="currentValue">
    cfloop is now processing : #currentValue#<br>
    </cfloop>
    </cfoutput>

    You can also use different delimiters (ie something than a comma). But comma is the default.
     
    cfStarlight, Jan 7, 2009 IP
  10. kimimaro

    kimimaro Peon

    Messages:
    23
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #10
    Ok my last question is using the cfloop, how do i insert 2 values into the same query?

    as in

    Insert into table 1
    (column1, column2)
    values
    (value1, value1)


    As i believe the cfloop cannot have both two list and index :(
     
    kimimaro, Jan 18, 2009 IP
  11. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #11
    I suppose you could do it with some more complicated list logic, but why? I suspect you are no longer talking about the original case where: form.check = "item1,item2,item3".

    Normally, when you have multiple pieces of information that are related and must be inserted together you would structure your form differently.
     
    cfStarlight, Jan 18, 2009 IP
  12. kimimaro

    kimimaro Peon

    Messages:
    23
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #12
    Oh actually at 1st i only wanted to insert one column to another table but now i realised i need to insert 2.


    Its something like I query 4 columns for a single row

    Example:

    Firstname
    Jackson


    LastName
    Smith


    Phone
    32424234

    address
    England


    Ok so any of the row is checked on the check list, two values of first and last name must inserted to another table. As for the unchecked list just leave the rows there.

    I can only get one column to the new table but not more than that :(
     
    kimimaro, Jan 18, 2009 IP
  13. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #13
    Relate the form field names by using a common suffix. For example, assuming your database table has a unique ID, you could use the recordID as the checkbox value

    <input type="checkbox" value="#RecordID#" ..>

    Then use the recordID again to name the textboxes. That will let you relate those fields to a specific id.

    <input type="text" name="firstName_#RecordID#"...>
    <input type="text" name="lastName_#RecordID#"...>

    On your action page, the checked recordID's will be passed as a comma separated list. Loop through the ID's and use array notation to extract the related first and last names. All that is left is to insert those values into your table


    <cfloop list="#form.yourCheckboxName#" index="recordID">
    <cfset variables.firstName = form["firstName_"& recordID]>
    <cfset variables.lastName = form["lastName_"& recordID]>
    <cfquery ...>
    .... insert the values ...
    </cfquery>
    </cfloop>
     
    cfStarlight, Jan 22, 2009 IP