I recently worked on a project where I needed to store a large amount of data from a questionnaire in a database. My initial approach was to create a table with fields corresponding to each of my form fields. However, this resulted in a large unwieldy database table and some fairly complex code to process my form. If you need to be able to search or manipulate form data at a later date this is probably the best approach. However, I only needed to store and retrieve individual questionnaires and for this John Whish offered some good advice. By converting the ColdFusion form structure to JSON I could persist the whole form in a database text field. Here's how to do it...

Firstly, here is a sample ColdFusion form structure (mine was a lot more complicated than this!).

Using ColdFusion's SerializeJSON function we can convert our form structure to JSON. As JSON the form looks like this:

As a text string the form can be saved to the database. When retrieving the form from the database we use the DeserializeJSON function to convert the JSON back to a ColdFusion structure.

I think this is a really nice solution. It certainly saved me a lot of time and effort.

---

Tags

• ColdFusion (18)

---

Comments

Wednesday, February 15, 2012 John Whish

Nice post :) Just a word of warning that ColdFusion's implimentation of JSON does change slightly between versions so you may have issues. See:
http://www.aliaspooryorik.com/blog/index.cfm/e/posts.details/post/bug-in-serializejson-296

Also, if you use the SecureJSONPrefix then SerializeJSON will pad the JSON so it's not strictly valid.

---

Wednesday, February 15, 2012 Phil Duba

Interesting, but I think if you needed to edit and search the data you could use it as well, especially if the search form is mapped to the potentially changing data fields. You could search using a parameter the like value of '%"artist_notes":"%search_term%"%' (in a cfqueryparam of course) if the search form was targeted at that field, etc. The harder part would be if joins, etc., were needed as part of the query. Regardless, this little insight just brought about some ideas for a few applications I could/should've done using this technique.

---

Add Comment

First Name *

Last Name *

Email Address *

Your email address will not be displayed on this blog.

URL

The URL of your blog or web site.

Comment *