changing name in data base

Posted on 2009-12-16
Last Modified: 2012-05-08
Hi all, I am new to coding in Coldfusion and  MYSQL so used to C# and Microsoft SQL  that I am having rookie problems. LOL Anyway here is the situation.

I have created a online timecard app. The employee names are populated in the dropdown and then once selected they enter the hours they worked each day and then it saves it to a database. Here is my problem it seams to be saving the id and not the name that it is suposed to. But heck at least I got the darn thing to save to the database.

Database information:
table = newemployees
id <- primary key (auto incrament)

table = payroll
id <- primary key (auto incrament)
name  <--- it is incerting the ID from the table newemployees


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">

<cfparam name="id" default="1245">

<cfquery datasource="*********" name="newemployees">

	select id,firstname,lastname

    from newemployees

     order by lastname, firstname



<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Untitled Document</title>




<form id="form1" name="form1" method="post" action="save_hours.cfm">


  <table width="396" border="1">


    <td width="215">Please select your name here</td>

    <td width="165">

   <select name="id">

   		<cfloop query="newemployees">

            <option value="#id#" type="text" <cfif id EQ #id#>selected</cfif>>#lastname#,&nbsp;#firstname#</option>






    <td>Please enter your hours here</td>

    <td><input name="hours" type="text" id="hours" value=""/> </td>



    <td>Please enter todays date here</td>

    <td><input name="date" type="text"  id="date" value = "#dateformat(now(), "yyyy-mm-dd")#" /> </td>





<input type="submit" name="sr" id="save" value="Save" />






<cfquery datasource="*********">

	insert into payrolle16

    (name, hours, date)

    values ('', '#form.hours#', '#dateformat(date, "YYYY-MM-DD")#')



<cfif sr EQ "Save">

<cflocation url="Index.cfm?id=#id#" addtoken="no">


<cflocation url="Index.cfm?id=0" addtoken="no">


Open in new window

Question by:HawaiiDragon
    1 Comment
    LVL 22

    Accepted Solution

    You have at least started coding in cold fusion. I know zilch about it!
    But i will try to help you out with the design.

    I will lay out my understanding, so It will be easier to be on the same page for me ;)
    you have a drop down populate like this say

    <select name="id">
    <option id="1">Su Ling</option>
    <option id="2">Rafa Martinez</option>
    <option id="3">Roberto gonzales</option>

    and the you have got by the  select query you have mentioned:
    select id,firstname,lastname from newemployees order by lastname, firstname

    Then you add number of hours and the date and save it to the database. You did mention that this part is working properly. Great! but the issue is that you have stored the Id as 1 instead of Su Ling / 2 instead of Rafa Martinez / 3 instead of Roberto gonzales.

    What you did is indeed correct! Why? because the Id's are always unique but names are not. There may be may people with the same names as Rafa Martinez now If you have the name there in  the second table into which you are inserting, how will you know which Rafa Martinez entered the time?

    So, to facilitate this you have to change your second table design

    table = payroll
    id <- primary key (auto incrament)
    name  <--- it is incerting the ID from the table newemployees
    Instead of this have this as
    employeeId - foreign key to the Id column in newemployees table

    So the next question would be if there are multiple rafa Martinez there will be multiple entries in the drop down, how would I know which is the proper one as a end user?

    So to avoid this you would probably display it as

    <select name="id">
    <option id="1">Su Ling - 1</option>
    <option id="2">Rafa Martinez - 2</option>
    <option id="3">Roberto gonzales - 3</option>
    <option id="4">Roberto gonzales - 4</option>

    So the user will know which name to select. Here 1,2 3,4 might mean the employee Id's.. So each employee would know the employee Id and will definitely identify.

    I hope you got an insight into the design part.

    Let me know.


    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Suggested Solutions

    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    This article describes how to create custom column layout styles for Bootstrap. The article uses 5 columns to illustrate the concept, but the principle can be extended to any number of columns.
    In this tutorial viewers will learn how to embed videos in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: "<!DOCTYPE html>": Use the <video> tag to insert a video. Define the src as the URL of your video; this is similar to …
    The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

    754 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

    20 Experts available now in Live!

    Get 1:1 Help Now