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.

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>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</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}" />

Open in new window

LVL 2
rrattieAsked:
Who is Participating?
 
gdemariaConnect With a Mentor Commented:
You should use ColdFusion.navigate for this, not bind.

See the link around the class name, it will send the URL to the CFWINDOW whenever you click a different class

<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>
</head>
<body>
<fieldset style="margin-left:10px; border:#cccccc 1px solid; width:720px; vertical-align:top;">
<cfform>
<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">
        <a href="##" onclick="ColdFusion.navigate('training/classes/classInfo.cfm?ID=#ID#','cw2');return false;">#course_name#</a>
      </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>
</cfform>
</fieldset>
<cfajaximport tags="CFWINDOW, CFGRID, CFFORM">
<cfwindow center="true" width="650" height="450" name="cw2" minHeight="450" minWidth="650" title="Class Details" initshow="true" bodyStyle="background-color: white;">
</body>
</html>

Open in new window

0
 
gdemariaCommented:
wow, that's a very interesting query using multiple subqueries instead of simple joins.

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

0
 
gdemariaCommented:
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..
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
gdemariaCommented:
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 ?

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'

Open in new window

0
 
rrattieAuthor Commented:
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.
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)
0
 
gdemariaCommented:
Ok, so I guess the structure has to stay as it is..

So did the query work or do you have to tweak it?
0
 
gdemariaCommented:
did that query work?
0
 
rrattieAuthor Commented:
After I changed the query, I get the error:

Bind Failed, Element not found: classID
0
 
gdemariaCommented:

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-color: white;" source="training/classes/classInfo.cfm?ID={classID@onclick}" />
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?
0
 
rrattieAuthor Commented:
Yeah, I have a cfinput tag named classID..

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>

Open in new window

0
 
gdemariaCommented:

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?
0
 
rrattieAuthor Commented:
Yes that is what I am trying to do.

0
 
rrattieAuthor Commented:
Now I get no errors and no table shows either. (when I put in a valid EDIPI #)
0
 
gdemariaCommented:
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
0
 
rrattieAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.