asked on

Inserting form results into MySQL database through CFQUERY

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

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


<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>
      
      

      <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#">

      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">



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 = "";
      }
}


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();      }
}


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;
}


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");
}

function stripNonDigits(str)
{
return str.replace(/[^\d]*/gi,"");
}


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"> 
</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>

mrichmon

what does the table look like?

ASKER CERTIFIED SOLUTION

hart

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

veeroy

ASKER

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

SOLUTION

pinaldave

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mrichmon

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?

veeroy

ASKER

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

mrichmon

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.

veeroy

ASKER

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

mrichmon

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.

veeroy

ASKER

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.

mrichmon

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.

veeroy

ASKER

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