Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


changing name in data base

Posted on 2009-12-16
Medium Priority
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" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<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.id#', '#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

kadaba earned 2000 total points
ID: 26070663
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this article, we’ll look at how to deploy ProxySQL.
In this tutorial viewers will learn how to embed an audio file in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: : The declaration should display (CODE) HTML5 is supported by the most recent versions of all major browsers…
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…
Suggested Courses

564 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