Solved

Inserting form results into MySQL database through CFQUERY

Posted on 2004-08-10
12
818 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
json_decode return null? 8 55
Website Test Question 6 101
IIS 8.5 2 44
how to setup ssl access to internal local machine 4 97
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now