Solved

Inserting form results into MySQL database through CFQUERY

Posted on 2004-08-10
12
830 Views
Last Modified: 2013-12-24
I need help with inserting data from a form into a MySQL data base using CFQUERY tag.  The problem is, I have a form that has text boxes and checkboxes when the form is submitted they are posted to the database.  Now the user can select multiple checkboxes before the form is submitted.  But, if multiple checkboxes are selected and the form is submitted to the database the checkboxes are not posted in the database, but if only one checkbox is checked and the form is submitted it shows up in the database...  HOW CAN I MAKE IT SO THAT ALL THE CHECKBOXES THAT ARE CHECKED ARE POSTED TO THE DATABASE ETHER WITH A SPACE OR PREFERABLLY A COMMA.  Here is what my code looks like for the form with the CFQUERY I am using:

Can anyone help? Please supply code examples that I can possibly modify to help me.

Thanks

---------------------------------------------------------------------

<!--inserting data into database table escalation_data from form submit--->
<cfif isDefined("FORM.account_number") and isDefined("FORM.result_type")>
      <cfquery datasource="#APPLICATION.dsn#">
            INSERT INTO escalation_data_test VALUES('#FORM.escalation_agent#',
                                          '#FORM.employee_list#',
                                          current_date,
                                          current_time,
                                          '#FORM.account_number#',
                                          '#FORM.tl_racf#',
                                          '#FORM.dm_racf#',
                                          '#FORM.center#',
                                          '#FORM.result_type#',
                                          '#FORM.feed_back#')
      </cfquery>
      <!--- Sending feedback mail to designated agents --->
      <!---<cfif SESSION.feed_back is not "">
            <cfmail to="#SESSION.feed_back_email#"
                        cc="#SESSION.feed_back_email_cc#"
                        from="#COOKIE.EMPEMAILID#"
                        subject="Escalation FeedBack On Account #SESSION.account_number#">
                        #SESSION.feed_back#
            </cfmail>
      </cfif>--->
      
      <cfset SESSION.feed_back = "">
</cfif>

<cfset SESSION.feed_back = "">

<cfquery name="cma_hierarchy" datasource="#APPLICATION.fpds_dsn#"
cachedwithin="#CreateTimeSpan(0,2,0,0)#">
 select employee_racf, dm_racf, tl_racf
 from cmalp_fpds.cma_hierarchy
 where position_active_date = current_date
 and employee_center = "#SESSION.center#"
 order by employee_racf
 </cfquery>
 
<cfquery name="escal_agent_cma_hierarchy" dbtype="query"
cachedwithin="#CreateTimeSpan(0,2,0,0)#">
 select employee_racf, tl_racf, dm_racf
 from cma_hierarchy
 where employee_racf = '#SESSION.racfid#'
 </cfquery>
 
<cfquery name="createcheckboxes" datasource="#APPLICATION.dsn#">
<!--- cachedwithin="#CreateTimeSpan(0,12,0,0)#"> --->
      SELECT result_type, non_combinable
      FROM `call_result`
      group by display_order
</cfquery>

<script src="/CFIDE/scripts/wddx.js" language="JavaScript" type="text/javascript"></script>
<script language="JavaScript" type="text/javascript">

<cfwddx action="cfml2js" input="#createcheckboxes#" topLevelVariable="createcheckboxes">
<cfwddx action="cfml2js" input="#cma_hierarchy#" topLevelVariable="cma_hierarchy">
<cfwddx action="cfml2js" input="#escal_agent_cma_hierarchy#" topLevelVariable="escal_agent_cma_hierarchy">

<!---This fucnction selects the team leader and department manager of the agent RACF selected--->

function setAgent_TL_DM_value()
{      
      var listObj                         =       document.call_tracker_form.employee_list;
      var agent_racf_selected       =       listObj.selectedIndex;
      var agent_dm_racf                  =       cma_hierarchy.dm_racf[agent_racf_selected-1];
      var agent_tl_racf                  =       cma_hierarchy.tl_racf[agent_racf_selected-1];
                        
      if(agent_racf_selected != 0)
      {      
            document.call_tracker_form.tl_racf.value = agent_tl_racf ;
            document.call_tracker_form.dm_racf.value = agent_dm_racf ;
      }
      else
      {
            document.call_tracker_form.tl_racf.value = "";
            document.call_tracker_form.dm_racf.value = "";
      }
}

<!--- this function checks to see which boxes are marked and puts them into an array--->
function selected_count()
{
      var checkboxes = document.call_tracker_form.result_type.length;
      var selected_index = 0;
      selected_array = new Array();
      
      for (var idx = 0; idx < checkboxes; idx++)
      {
            if (document.call_tracker_form.result_type[idx].checked == true)
            {      selected_array[selected_index++] = idx;      }
      }
      if(selected_array.length > 1)
      {      check_selected_array();      }
}

<!--- This function checks each eliment of the array against the non-combinables in the database--->
function check_selected_array()
{
      for(var idx = 0; idx < selected_array.length; idx++)
      {
            for( var idx_b = 0; idx_b < selected_array.length; idx_b++)
            {
                  if(idx != idx_b)
                  {
                        non_combine = createcheckboxes.non_combinable[selected_array[idx_b]].split(",");
                        for(var non_comb_id = 0; non_comb_id < non_combine.length; non_comb_id++)
                        {
//                              non_combine[non_comb_id] = Trim(non_combine[non_comb_id]);
                              if(non_combine[non_comb_id] == createcheckboxes.result_type[selected_array[idx]])
                              {
                                    alert("ERROR: These can not be selected together!");
                                    document.call_tracker_form.result_type[selected_array[idx_b]].checked=false;
                                    return false;
                              }
                        }
                  }
            }
      }
      return true;
}

<!---This function opens feedback window and populates the required fields through the URL--->
function openFeedback()
{      
      var agent_dm_racf                  =       document.call_tracker_form.dm_racf.value;
      var agent_tl_racf                  =       document.call_tracker_form.tl_racf.value;
      var agent = document.call_tracker_form.employee_list.options[document.call_tracker_form.employee_list.selectedIndex].text;
      
      window.open("mailto:" + agent + "@discoverfinancial.com," + agent_tl_racf + "@discoverfinancial.com," + agent_dm_racf + "@discoverfinancial.com");
      //window.open("http://lpowebd1.novusservices.com/cma/call_escalation_tracker2/call_escalation_ticksheets/escalation_feedback.cfm?account_number=" + document.call_tracker_form.account_number.value + "&tl_racf=" + document.call_tracker_form.tl_racf.value + "&dm_racf=" + document.call_tracker_form.dm_racf.value + "&employee_list=" + document.call_tracker_form.employee_list.options[document.call_tracker_form.employee_list.selectedIndex].value,"feedbackWin"," width=600, height=475");
}
<!---This function strips all NonDigets from the value that is asked to evaluate--->
function stripNonDigits(str)
{
   return str.replace(/[^\d]*/gi,"");
}

<!---This function checks the form to make sure all the required field are populated before submitting to the database--->
function checkForm()
{
      var checkboxes = document.call_tracker_form.result_type.length;
      var selected_index = 0;
      selected_array = new Array();
      
      for (var idx = 0; idx < checkboxes; idx++)
      {
            if (document.call_tracker_form.result_type[idx].checked == true)
            {      selected_array[selected_index++] = idx;      }
      }
      if(selected_array.length == 0)
      {      
            alert("Please Select a Result Type!");
            return false;
      }
      else if(selected_array.length >= 1)
      {
            for(var idy = 0; idy < selected_array.length; idy++)
            {
                  //if(createcheckboxes.result_type[selected_array[idy]] == "Bad Transfer")
                  if(createcheckboxes.result_type[selected_array[idy]] == "Bad Transfer" && document.call_tracker_form.feed_back.value == "" && document.call_tracker_form.employee_list.options[document.call_tracker_form.employee_list.selectedIndex].text != "Select RACF")
                  {
                        document.call_tracker_form.feed_back.value = "Feed Back Sent"
                        openFeedback();
                        return false;
                  }
                  else if(document.call_tracker_form.employee_list.options[document.call_tracker_form.employee_list.selectedIndex].text == document.call_tracker_form.escalation_agent.value)
                  {
                        alert("Selected Agent RACF Can Not Be The Same As Escalation Agent RACF");
                        document.call_tracker_form.employee_list.options[document.call_tracker_form.employee_list.selectedIndex].text = "UNKNOWN";
                        document.call_tracker_form.tl_racf.value = "UNKNOWN";
                        document.call_tracker_form.dm_racf.value = "UNKNOWN";      
                        return false;
                  }
                  else if(createcheckboxes.result_type[selected_array[idy]] != "Dropped Call" && createcheckboxes.result_type[selected_array[idy]] != "800 Number" && createcheckboxes.result_type[selected_array[idy]] != "Other" && document.call_tracker_form.employee_list.options[document.call_tracker_form.employee_list.selectedIndex].text == "Select RACF")
                  {      
                        alert("Please Select Account Manager RACF!");      
                        return false;
                  }
                  else if(createcheckboxes.result_type[selected_array[idy]] == "Dropped Call" && document.call_tracker_form.employee_list.options[document.call_tracker_form.employee_list.selectedIndex].text == "Select RACF" )
                  {      document.call_tracker_form.employee_list.options[document.call_tracker_form.employee_list.selectedIndex].text = "UNKNOWN";      }
                  else if(createcheckboxes.result_type[selected_array[idy]] == "Other" && document.call_tracker_form.employee_list.options[document.call_tracker_form.employee_list.selectedIndex].text == "Select RACF" && document.call_tracker_form.account_number.value == 6011)
                  {      
                        document.call_tracker_form.employee_list.options[document.call_tracker_form.employee_list.selectedIndex].text = "UNKNOWN";
                        document.call_tracker_form.account_number.value = "";
                  }
                  else if(createcheckboxes.result_type[selected_array[idy]] == "800 Number" && document.call_tracker_form.employee_list.options[document.call_tracker_form.employee_list.selectedIndex].text == "Select RACF"      )
                  {      document.call_tracker_form.employee_list.options[document.call_tracker_form.employee_list.selectedIndex].text = "CMCI";      }
                  
                  var account = document.call_tracker_form.account_number.value;
      
                  if(account == "" && createcheckboxes.result_type[selected_array[idy]] != "Other")
                  {      
                        alert("Account Number Can Not Be Blank!!");
                        document.call_tracker_form.account_number.focus();
                        return false;
                  }
                  else if(account != "")
                  {
                        account = stripNonDigits(account);
                    document.call_tracker_form.account_number.value = account;
                  
                        var CheckLen = document.call_tracker_form.account_number.value;
            
                        if(CheckLen.length != 16)
                        {      
                              alert("Account Number Is Not 16 Digits Long");
                              document.call_tracker_form.account_number.focus();
                              return false;
                        }
                  }
            }
      }
      document.call_tracker_form.submit();
}

</script>

<form name="call_tracker_form" method="post" action="call_tick_sheet.cfm">
  <input type="hidden" name="center" value="<cfoutput>#SESSION.center#</cfoutput>">
      <table width="499" border="0" cellpadding="0" cellspacing="0">
        <tr>
               
      <td width="499"> <table width="100%" border="0" cellspacing="5" cellpadding="0" align="center">
          <tr valign="top">
          <tr valign="top">
            <td width="95%"><table width="94%" border="0" align="left">
                <tr>
                  <td width="50%" nowrap><div align="center"><font color="#333333" size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>Cad/Escalation
                      Agent:
                      <input type="text" name="escalation_agent" readonly="" value="<cfoutput>#SESSION.racfid#</cfoutput>" maxlength="12" size="12">
                      </strong></font></div></td>
                </tr>
              </table></td>
          </tr>
          <tr valign="top">
            <td height="200" valign="left" nowrap> <font size="1" face="Verdana, Arial, Helvetica, sans-serif">&nbsp;
              </font> <table width="100%" height="177" border="0" align="left">
                <tr>
                  <td width="31%" nowrap> <p align="right"><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><strong>Account
                      Number:
                      <input type="text" name="account_number" value="6011" maxlength="16" size="17">
                      </strong></font></p>
                    <p align="right"><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><strong>Account
                      Manager:
                      <select name="employee_list"  onChange="setAgent_TL_DM_value()">
                        <option>Select RACF </option><cfoutput query="cma_hierarchy">
                        <option value="#employee_racf#">#employee_racf# </cfoutput>
                      </select>
                      </strong></font></p>
                    <p align="right"><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><strong>Team
                      Leader:
                      <input type="text" name="tl_racf" maxlength="10" size="12">
                      </strong></font></p>
                    <p align="right"><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><strong>Department
                      Manager:
                      <input type="text" name="dm_racf" maxlength="10"size="12">
                      </strong></font></p></td>
                  <td width="69%" nowrap> <table align="left">
                      <cfoutput query="createcheckboxes">
                        <tr>
                          <td><input type="checkbox" name="result_type" value="#result_type#" onClick="selected_count()">
                            #result_type#</td>
                        </tr>
                      </cfoutput> </table></td>
                </tr>
              </table></td>
          </tr>
          <table width="98%" border="0" align="left">
              <td><input name="Reset" type="Reset" value="Reset Fields"></td>
              <td><input name="button" type="button" onClick="checkForm()" value="Call Complete"></td>
              <td><input type="button" name="button" onClick="openFeedback()" value="Feed Back"></td>
              <input type="hidden" name="feed_back" value="<cfoutput>#SESSION.feed_back#</cfoutput>">
          </table>
        </table>
        </td>
     </tr>
  </table>
</form>

0
Comment
Question by:veeroy
12 Comments
 
LVL 35

Expert Comment

by:mrichmon
ID: 11763753
what does the table look like?
0
 
LVL 11

Accepted Solution

by:
hart earned 400 total points
ID: 11763953
It would have come in as a coma if the checkbox is submitted...
Now what needs to be checked is that is what is the datatype of the field that stores the checkbox values...
if you want a coma delimited value to be stored then it has to be text type [char / varchar type] and not numeric

Regards
Hart
0
 

Author Comment

by:veeroy
ID: 11764341
This is the insert statment for the table.....
-----------------------------------------------------
INSERT INTO escalation_data_test VALUES('#FORM.escalation_agent#',
                        #FORM.employee_list#',
                        current_date,
                        current_time,
                        '#FORM.account_number#',
                        '#FORM.tl_racf#',
                        '#FORM.dm_racf#',
                        '#FORM.center#',
                        '#FORM.result_type#',
                        '#FORM.feed_back#')
------------------------------------------------------------------------------------

The [#FORM.result_type] is where the checkboxes are pulled from the form and stored under the field name [Call_result] and it is set as an "ENUM" should it be a varchar?

Any help
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 21

Assisted Solution

by:pinaldave
pinaldave earned 100 total points
ID: 11764768
Hi veeroy,
In the same line of answer what 'hart' says, if you directly insert the checkbox to the database  like
if your checkbox name is checkname then they should insert in your database with comma as delim
NSERT INTO escalation_data_test VALUES('#FORM.escalation_agent#',
                    #FORM.employee_list#',
                    current_date,
                    current_time,
                    '#FORM.account_number#',
                    '#FORM.tl_racf#',
                    '#FORM.dm_racf#',
                    '#FORM.center#',
                    '#FORM.check_name#',
                    '#FORM.feed_back#')

I will also keep that as varchar as I am not sure what enum is.

Regards,
---Pinal
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11765231
I saw the insert, but that does not answer the question.

If the data is not being stored we need to know what the table structure looks like.

What is the datatype of each field?
0
 

Author Comment

by:veeroy
ID: 11765386
Thanks for both your help. I changed the field type to a varchar and it worked.  As for enum it allows you to specify a list and when you insert into that field it looks at the list and if it is in the list it post to the database the problem is that it only lets you put one value in the database not multiples of the list difined. So its like if you had a choice for the user of ether 'Yes' or 'No' or 'Maybe' it can only be one of those not more one.

thanks for your help

veeroy
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11765909
It sounds like you had a bit field which we could have told you would be the problem if you had posted the table structure in the beginning like was asked.  This kind of information (i.e. what you table fields look like) is VERY importatnt when posting a question.
0
 

Author Comment

by:veeroy
ID: 11766392
It did not seem to be to hard of a question to understand even with out the table fields.  Both HART and PINALDAVE seem to understand what the problem was, and they came back with a solution for me to look at.  If I have to be more specific for you mrichmon I will try to the next time I have a question.  But the suggestions from the other two experts was the solution I was looking for,  Sorry for your misunderstanding the question with out all the information.

Thanks  
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11767083
Actually they both guessed.  Which is not a problem.

my point is just that the guess could have been wrong, but if you had provided all of the information up front then any of the experts here - including pinaldave and hart would have been able to tell you the answer for sure rather than just guessing.

I have seen many times when the initial guess was worng - not because the expert did not know what they were doing, but because the asker did not provide complete information.
0
 

Author Comment

by:veeroy
ID: 11767754
mrichmon,  if you look at my first comment following my insert code you would see that I did supply the data type of the field that I was having problems with I stated that it was a "enum" I was not having any problems with the other data being posted to the database.  I stated in my origanal question that when the form was submitted with multiple checkboxes selected, there was a blank entry and when just one checkbox was selected it showed up in the database. It sound like it is a problem for you that the other two took a guess and that is your issue you could have guessed also and received the points. It seems to me that they felt they had enough information to supply a guess as to what I was asking for help about.  Besides, I did not award the points until after I tried out their suggestion.  All I can say to you is you, win some and you loose some.

Thanks for your input though, I will try and clearify question and supply all the information next time.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11774243
I am not interested in the points.  I think it is great that they went to hart and pinaldave.

I am just trying to help you realize what will make a better formed question so that in the future others will be able to help you.

For example, enum is implemented differently on different databases.  You never specified what databaes you were using.  On some databases it may have worked without changing the field type.
0
 

Author Comment

by:veeroy
ID: 11776349
mrichmon,  Thank you for your input. In the future I will try and supply as much information so that there is no misunderstanding with the question I am asking.  I just want to say that I did tell you what type of database I was using the the first line of my question, but that is beside the point.  Thanks again and have  a great day you are a big  help and I do mean that.

veeroy
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question