Link to home
Start Free TrialLog in
Avatar of rrattie
rrattieFlag for United States of America

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.

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

Avatar of gdemaria
gdemaria
Flag of United States of America image

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

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..
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

Avatar of rrattie

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.
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?
did that query work?
Avatar of rrattie

ASKER

After I changed the query, I get the error:

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-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?
Avatar of rrattie

ASKER

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


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?
Avatar of rrattie

ASKER

Yes that is what I am trying to do.

ASKER CERTIFIED SOLUTION
Avatar of gdemaria
gdemaria
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rrattie

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
Avatar of rrattie

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.