Parse array that contains input names and values b

Question

Background: I am going to save my ColdFusion forms data into an array and store that array into the column of a database. The only thing I will be doing with this array is making a call to the database for the data and parsing it back into my form but as "Read Only". The array will contain both the input name and value.

Question: In ColdFusion after I have queried the database for the array data what would be the best process to parse the data back into my form? Do I have to recreate my form in a loop? Or can I target the inputs and and add the values since I will know their names?

Also is there any value in saving the data as serialized JSON data verse an array created with ColdFusion?

****EDIT***

Below is an outline of the workflow of my data. Some of these items where requested by the client.

1. User completes form with 65 inputs.

2. On Submit all of the form data is stored in an array which is added to a database. The database also has the following data added. A unique numerical identifier, Submission Date, User ID that submitted the form (pulled from a Session variable), and finally a status of "not assigned" is added to the database

3. An admin checks a queue on a separate page. That queue simple pulls all form submissions that are still in "not assigned" status. The array is not used in this queue.

4. If an admin selects one of the entries in the queue the following page loads the exact same form but I will use the array to populate the values into the form and set all fields to read only. The admin literally only needs to copy and paste the values from the form fields into a different system. (Yes I know it sounds tedious but for this specific client there is no other option and this is actually better than the process they are currently using.)

5. The array data will literally never be used for anything else other than to make sure all of the data is collected.

6. The array data will always have to be loaded as a whole and will never be changed once submitted.

I hope this helps clear up why I am asking these questions. THanks

Answer 1

First, what pankaj means.

You can do something like this. Let's say I'm storing preferential aspects of a car. 1997 Honda Accord. You might store data in about it like this.

Cars Table

CarID (PrimKey) | Make  | Model     | Year
--------------------------------------------
47              | Honda | Accord    | 1997
48              | Chevy | Malibu    | 2005

And then I can have a separate table called CarSpecs

SpecID (PrimKey) | CarID | SpecName | SpecValue
-----------------------------------------------
1001             | 47    | Color    | Red
1002             | 47    | Transmsn | Auto
1003             | 47    | Doors    | 4
1004             | 48    | Color    | Green
1005             | 48    | Transmsn | Manual

You could store (Make, Model, Year) in this table as well, but let's say you didn't.

You're insertion becomes something like this..

(If you're not using cfqueryparam, please look it up, it protects against sql injection, a common form of hacking.)

  <cfquery name="NewCar">
    insert into Cars(Make, Model, Year)
    values(<cfqueryparam value="#form.make#" cfsqltype="cf_sql_varchar">,
      <cfqueryparam value="#form.model#" cfsqltype="cf_sql_varchar">,
      <cfqueryparam value="#form.year#" cfsqltype="cf_sql_integer">)
  </cfquery>

  <cfset DeletedFields = "make,model,year,submit_button,tos_agree">

  <cfloop list="#form.fieldnames#" index="df">
    <cfif not listfind(deletedfields,df)>
      <cfquery>
        insert into CarSpecs(CarID,SpecName,SpecValue)
        values(<cfqueryparam cfsqltype="cf_sql_integer" value="#NewCar.generatedKey#">,
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#fn#">,
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#form[fn]#">)
      </cfquery>
    </cfif>
  </cfloop>

There are a few easy ways to get the id of the record you just inserted (#NewCar.generatedkey#). Here's a short article on it if you're unfamiliar.

Now, as we covered in the comments on your previous question

The best way is inserting into separate rows as I've demostrated. Following that, separate columns would be better than inserting as a bulk.

The only benefit to inserting as a bulk is it's easy, that's it.

The downsides, however, are numerous.

To select any of the data, you have to either

• select all of the data. - needless overhead
• try and use sql to parse the data - needless overhead

Intertable queries (joins) based on this field are going to be a huge headache.

So suppose you devised something like my example above. You could select all the details for a car like this.

<cfoutput query="CarDetails" group="carID">#Make# #Model# #Year#<br /><br />
  <cfoutput>#replace(specname,"_"," ","ALL")#: <input type="text" name="#specname#" value="#htmleditformat(specvalue)#" /> <br /></cfoutput>
</cfoutput>