How do I get a button to show in a list only when a survey is filled?

[Guest]

I am using cf9 with mySQl 5+. I have two tables:

1. Signups -  where people signup to take a course.
2. Course_eval – once the course is completed, the student fills in the course evaluation

I use an inner join on userID in both tables.

I have a page, showsignups.cfm, that lists all of the students by course. Once a student finishes the survey, a button will show up next to the student in the showsignups.cfm page where it can be clicked on to show the survey by the individual student.

My problem is instead of showing all students with or without the survey buttons, it only shows the students who have finished the survey. It should show:

Student’s name | survey button

Student’s name |

Student’s name | survey button

Student's name |

I thought using a cfloop through the students would give me the effect I want, but alas, no. Here is the code I am using for the cfquery:

<cfquery name="getsignups" datasource="#application.dsn#">

select signups.courseTitle,signups.property,signups.calendardate,signups.company,signups.firstname,signups.lastname,signups.email,signups.phone,signups.userID,signups.signup_id,signups.rid,course_eval.userID,course_eval.id

from signups INNER JOIN course_eval ON signups.userID = course_eval.userID

where signups.rid = #rid#

</cfquery>

(Rid is the courseID)

Here is the output I am using:

<cfoutput>

<cfloop query="getsignups">

<tr>

<td>#rid# - #firstname# #lastname#  |   <a href="mailto:#email#">#email#</a></td><td>Ph: #phone#</td>

<td width="24"><a href="showsignups.cfm?signup_id=#signup_id#&go=go" class="button">Remove</a></td>

<cfif isdefined("id")>

<td width="24"><a href="../../forms/surveys.cfm?userID=#userID#" class="button">Survey</a></td>

</cfif>

</tr>

</cfloop>           

</cfoutput>

I know this is a long post, but I wanted to make sure any body who reads this understands what I am trying to accomplish. Any ideas on what I am doing wrong?

[EddieLotter · Accepted solution]

Don't use an inner join:

<cfquery name="getsignups" datasource="#application.dsn#">

select signups.courseTitle,signups.property,signups.calendardate,signups.com pany,signups.firstname,signups.lastname,signups.email,signups.phone,si gnups.userID,signups.signup_id,signups.rid,course_eval.userID,course_e val.id

from signups LEFT OUTER JOIN course_eval ON signups.userID = course_eval.userID

where signups.rid = #rid#

</cfquery>

<cfoutput> 
  <cfloop query="getsignups">
    <tr>
      <td>#rid# - #firstname# #lastname#  |   <a href="mailto:#email#">#email#</a></td><td>Ph: #phone#</td>
      <td width="24"><a href="showsignups.cfm?signup_id=#signup_id#&go=go" class="button">Remove</a></td>
      <cfif isdefined("id")>
        <td width="24"><a href="../../forms/surveys.cfm?userID=#userID#" class="button">Survey</a></td>
      </cfif>
    </tr>
  </cfloop>            
</cfoutput> 

Use:

<cfif id neq "">

Message was edited by: Eddie Lotter (Typo)

[Guest]

Thanks, worked like a charm.

[EddieLotter]

You're welcome.

[Guest]

I have a left outer join that is joining two tables. the mobile table is the default table that gets sent out daily. If the default table is edited, it is saved to the edited table where it will be sent out instead of the default table. How do I determine if the edited table has content for that day and how do I tell which table to pull the contant from??

<cfquery name="getDevotional" datasource="#application.dsn#">

SELECT mobile.mob_id, mobile.display_date, mobile.title, mobile.body, mobile.scripture, edited.mob_id

FROM mobile

left outer join edited ON mobile.calendar_date = edited.calendar_date

where mobile.display_date = <cfqueryparam value ="#dateformat(now(), "YYYY-MM-DD")#" cfsqltype="cf_sql_date">

</cfquery>

Both tables have the same columns