rrattie
asked on
Getting all record ID in a single table row instead of one record ID
Okay, here's the problem.
I have a table that lists all the classes an employee needs to take.
The cfquery classes pulls out what classes are due for the employee
and the table displays them.
Now I have an onclick event set to open a CFWindow when a person clicks on the row that works fine, however I get an error because the URL.ID that is sent is all of the class IDs and not just the ID of the class in that specific row.
Part of the error message that is relevant.
Page code.
I have a table that lists all the classes an employee needs to take.
The cfquery classes pulls out what classes are due for the employee
and the table displays them.
Now I have an onclick event set to open a CFWindow when a person clicks on the row that works fine, however I get an error because the URL.ID that is sent is all of the class IDs and not just the ID of the class in that specific row.
Part of the error message that is relevant.
URL - struct
ID 2,19,18,9,10,12,6,8,11,17,7,13,14
_cf_clientid FB7DCF3EB9543C2D3191FFD823A49DA8
_cf_containerId cw2_body
_cf_nocache true
_cf_nodebug true
_cf_rc 4
Page code.
<link rel="stylesheet" type="text/css" href="../../css/main.css">
<script type="text/javascript" src="../../includes/sortable.js"> </script>
<cfinclude template="config.cfm">
<!--- query to get the training ID of the person logged in. --->
<cfquery name="getMyClasses" datasource="#DSN#">
SELECT *
FROM dbo.Contacts
WHERE EDIPI = '123456789'<!---'#SESSION.UserID#'--->
</cfquery>
<!--- query to get uncompleted classes for the person logged in. --->
<CFQUERY NAME="classes" DATASOURCE="#DSN#" >
select sub1.personnel, sub1.conid, sub2.reqdate, sub1.course_name, sub1.id, sub1.description, sub1.date, sub1.attendees
from (SELECT c.personnel, c.id as conID,tc.*
FROM dbo.training_courses tc, dbo.contacts c
WHERE tc.date > <!---getdate()---> '6/1/2009' ) as sub1
left outer join (select * from usertraining where completed = 'yes') as sub2
on sub1.conid = sub2.contactid and sub1.id = sub2.courseid
where (sub2.id is null or sub2.completed != 'yes') and sub1.conid in (202,18,155,1,253,254) and sub1.conid = '#trim(getMyClasses.ID)#'
ORDER BY sub1.date
</CFQUERY>
<fieldset style="margin-left:10px; border:#cccccc 1px solid; width:720px; vertical-align:top;">
<table width="700" border="0" cellspacing="2" cellpadding="2" style="margin-top:10px; margin-left:10px; margin-right:10px; margin-bottom:10px;" class="sortable" id="allclasses">
<tr style="background-color:#F5F5F5; border:1px solid #666666;" height="20px">
<td><span style="font-weight:bold; color:#000000;">Course Name</span></td>
<td><span style="font-weight:bold; color:#000000;">Description</span></td>
<td><span style="font-weight:bold; color:#000000;">Due Date</span></td>
<td><span style="font-weight:bold; color:#000000;">Course Type</span></td>
</tr>
<cfoutput query="classes">
<tr onmouseover="style.backgroundColor='##D5EDB3';" onmouseout="style.backgroundColor='##FFF'" onclick="javascript:ColdFusion.Window.show('cw2')" >
<td style="display:none;"><input type="hidden" name="classID" value="#classes.ID#" /></td>
<td height="18px"> #classes.course_name#</td>
<td height="18px">#classes.description#</td>
<td height="18px">#DateFormat(classes.date, "mm/dd/yyyy")#</td>
<td height="18px"><cfif isDefined('classes.attendees') AND classes.attendees EQ '1'>All<cfelseif classes.attendees EQ '2'>All<cfelseif classes.attendees EQ '3'>Non-Supervisor<cfelseif classes.attendees EQ '4'>this<cfelseif classes.attendees EQ '5'>1102<cfelseif classes.attendees EQ '6'>110x</cfif></td>
</tr>
</cfoutput>
<tr>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
</table>
</fieldset>
<cfwindow center="true" width="650" height="450" name="cw2" minHeight="450" minWidth="650" title="Class Details" initshow="false" bodyStyle="background-color: white;" source="training/classes/classInfo.cfm?ID={classID@onclick}" />
Tracing back the #classes.ID# value that you're talking about, it seems to come from the CONTACTS table. It is the ID field of the CONTACTS table; which I am guessing is actually the course ID because of the join.
BIG TIP: You really need to name your IDs after the name of the table. For example, the contact's table primary key would be CONTACT_ID, and for the table COURSES it would be COURSE_ID. Then in EVERY TABLE they appear, keep the name the SAME. So that you don't have...
on sub1.conid = sub2.contactid and sub1.id = sub2.courseid
But instead you have...
on Sub1.ContactID = sub2.ContactID
and sub1.CourseID = Sub2.CourseID
I think they query needs to be re-written, will try to give you an example..
BIG TIP: You really need to name your IDs after the name of the table. For example, the contact's table primary key would be CONTACT_ID, and for the table COURSES it would be COURSE_ID. Then in EVERY TABLE they appear, keep the name the SAME. So that you don't have...
on sub1.conid = sub2.contactid and sub1.id = sub2.courseid
But instead you have...
on Sub1.ContactID = sub2.ContactID
and sub1.CourseID = Sub2.CourseID
I think they query needs to be re-written, will try to give you an example..
ok, here's a try at the query... It's hard to follow
But my guess is that you don't need the "getMyClasses" query at all because you can just use that criteria in the main query (I added WHERE EDIPI = '123456789' into this query)
If it's not too late, I would pause and give us the table structure. I think you should rework it a bit. Is a Contact the same as a User ?
But my guess is that you don't need the "getMyClasses" query at all because you can just use that criteria in the main query (I added WHERE EDIPI = '123456789' into this query)
If it's not too late, I would pause and give us the table structure. I think you should rework it a bit. Is a Contact the same as a User ?
select c.personnel
, c.ID as contactID
, tc.reqdate
, tc.course_name
, tc.description
, tc.attendees
, tc.date
From dbo.contacts c
left join usertraining ut on ut.contactid = c.ID
and completed = 'yes'
left join dbo.training_courses tc on ut.courseID = tc.ID
where tc.date > '6/1/2009'
and (ut.id is null or ut.completed != 'yes')
and c.ID in (202,18,155,1,253,254)
and c.EDIPI = '123456789'
ASKER
The contact table was created a while ago and is the core of the intranet website (for login, user info, etc..)
This piece I am working on with training courses was a recent request.
We are dealing with 3 tables in this query.
This piece I am working on with training courses was a recent request.
We are dealing with 3 tables in this query.
contacts (primary source for user details)
training_courses (source of course info)
usertraining (source of user training details, as in what classes they have taken and when)
Ok, so I guess the structure has to stay as it is..
So did the query work or do you have to tweak it?
So did the query work or do you have to tweak it?
did that query work?
ASKER
After I changed the query, I get the error:
Bind Failed, Element not found: classID
Bind Failed, Element not found: classID
This it the tag with the bind...
<cfwindow center="true" width="650" height="450" name="cw2" minHeight="450" minWidth="650" title="Class Details" initshow="false" bodyStyle="background-colo
Toggle
The classID is looking for a field named classID. It has nothing to do with the query.
Do you have a field (cfinput tag, cfselect tag) named classID?
ASKER
Yeah, I have a cfinput tag named classID..
Here is my updated code.
Here is my updated code.
<cfquery name="classes" datasource="#DSN#">
select c.personnel
, c.ID as contactID
, tc.course_name
, tc.description
, tc.attendees
, tc.date
From dbo.contacts c
left join usertraining ut on ut.contactid = c.ID
and completed = 'yes'
left join dbo.training_courses tc on ut.courseID = tc.ID
where tc.date > '6/1/2009'
and (ut.id is null or ut.completed != 'yes')
and c.ID in (202,18,155,1,253,254)
and c.EDIPI = '123456789'
</cfquery>
<cfajaximport tags="CFWINDOW, CFGRID, CFFORM">
</head>
<body>
<fieldset style="margin-left:10px; border:#cccccc 1px solid; width:720px; vertical-align:top;">
<table width="700" border="0" cellspacing="2" cellpadding="2" style="margin-top:10px; margin-left:10px; margin-right:10px; margin-bottom:10px;" class="sortable" id="allclasses" background="##FFFFFF">
<tr style="background-color:#F5F5F5; border:1px solid #666666;" height="20px">
<td><span style="font-weight:bold; color:#000000;">Course Name</span></td>
<td><span style="font-weight:bold; color:#000000;">Description</span></td>
<td><span style="font-weight:bold; color:#000000;">Due Date</span></td>
<td><span style="font-weight:bold; color:#000000;">Course Type</span></td>
</tr>
<cfoutput query="classes">
<tr onmouseover="style.backgroundColor='##D5EDB3';" onmouseout="style.backgroundColor='##FFF'" onclick="javascript:ColdFusion.Window.show('cw2')">
<td height="18px"><cfform>
<cfinput type="hidden" name="classID" value="#ID#">
</cfform>
#course_name#</td>
<td height="18px">#description#</td>
<td height="18px">#DateFormat(date, "mm/dd/yyyy")#</td>
<td height="18px"><cfif isDefined('attendees') AND attendees EQ '1'>
All
<cfelseif attendees EQ '2'>
All
<cfelseif attendees EQ '3'>
Non-Supervisor
<cfelseif attendees EQ '4'>
this
<cfelseif attendees EQ '5'>
1102
<cfelseif attendees EQ '6'>
110x
</cfif></td>
</tr>
</cfoutput>
</table>
</fieldset>
<cfwindow center="true" width="650" height="450" name="cw2" minHeight="450" minWidth="650" title="Class Details" initshow="false" bodyStyle="background-color: white;" source="training/classes/classInfo.cfm?ID={classID@onclick}" />
</body>
</html>
I'm not understanding how your form is working. You have a form inside the loop, so your web page will have X number of forms in it, all with a hidden field classID. Since the hidden field cannot change (because it's hidden), there is no sense in binding to it. The CFWINDOW cannot find the classID field, because it is not unique, there are many of them, all in different forms.
Are you trying to make it so that if a person clicks one of the courses the cfwindow will change to show the detail of that class?
ASKER
Yes that is what I am trying to do.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Now I get no errors and no table shows either. (when I put in a valid EDIPI #)
Then you need to play with your query. As I said, based on your original query, it was very hard to determine what it was supposed to be.. so mine is a total guess.
If you can state in words what it should do, that may help.
select c.personnel
, c.ID as contactID
, tc.course_name
, tc.description
, tc.attendees
, tc.date
From dbo.contacts c
left join usertraining ut on ut.contactid = c.ID
and completed = 'yes'
left join dbo.training_courses tc on ut.courseID = tc.ID
where tc.date > '6/1/2009'
and (ut.id is null or ut.completed != 'yes')
and c.ID in (202,18,155,1,253,254)
and c.EDIPI = '123456789'
This query will return all contacts where CONACT.ID is one of these 202,18,155,1,253,254
and the CONTACT.EDIPI number is 123456789
The user must have a completed USERTRAINING entry associated with a TRAINING_SOURCE whose date is after June 1, 2009
What you can do is put that query directly against the database and try modifying it until it returns records and see what was needed to change.
Perhaps there are no recrods after 6/1/09, for example
If you can state in words what it should do, that may help.
select c.personnel
, c.ID as contactID
, tc.course_name
, tc.description
, tc.attendees
, tc.date
From dbo.contacts c
left join usertraining ut on ut.contactid = c.ID
and completed = 'yes'
left join dbo.training_courses tc on ut.courseID = tc.ID
where tc.date > '6/1/2009'
and (ut.id is null or ut.completed != 'yes')
and c.ID in (202,18,155,1,253,254)
and c.EDIPI = '123456789'
This query will return all contacts where CONACT.ID is one of these 202,18,155,1,253,254
and the CONTACT.EDIPI number is 123456789
The user must have a completed USERTRAINING entry associated with a TRAINING_SOURCE whose date is after June 1, 2009
What you can do is put that query directly against the database and try modifying it until it returns records and see what was needed to change.
Perhaps there are no recrods after 6/1/09, for example
ASKER
I put the 'old' set of queries back in with the rest of the new code and it works now.
The query was supposed to get all classes that a user hasn't taken yet that are due after a certain date.
When it goes live the 6/1/09 will just be the current date, but I'm working with sample data at the moment.
The query was supposed to get all classes that a user hasn't taken yet that are due after a certain date.
When it goes live the 6/1/09 will just be the current date, but I'm working with sample data at the moment.
My first observation is on this inner query..
SELECT c.personnel, c.id as conID,tc.*
FROM dbo.training_courses tc, dbo.contacts c
WHERE tc.date > '6/1/2009'
There is nothing joining the training courses with the contacts so you are matching each training record with EVERY contact... you need a join