CFIF and display message

[NickyJay]

Hi All,

My page connects to 3 tables on a database, a record set for each table (it seems easiest this way because of other things that go on). Two of the tables are connected by the third, basically a contact table, a publication table and an intersection to records who receiveds what (contact fk and publication fk).

The page before allows the user to select the publication to deal with - passed to this page via a publication id. This page the outputs a list of the contacts names, alongside each is a checkbox (again, other stuff on the page means that each checkbox is uniquly identified by the contact pk). What i wanted to do is display a message where a contact already exists on the intersection table.

currently i have the following:

<cfif isDefined('qryReceived.ContactIDfk') AND #qryReceived.ContactIDfk# NEQ #qryContacts.ContactID#>
<input <cfif IsDefined('checkall')>checked<cfelseif #FORM.recPub# EQ "#qryContacts.ContactID#")>checked</cfif> type="checkbox" name="recPub" value="#qryContacts.ContactID#">
<cfelse>
Contact already receives publication.
</cfif>

This works, however only for one contact out of a list of 600 - approx 30 or so DO have a fk present on the intersection table so these should all have the error message, not just the one!!

Please can someone tell me where im going wrong?

Thanks

Nicola

 

[cfStarlight]

If you're not looping through the query values, #queryName.columnName# will only return the value in the first row (if it exists).

Why do you need to display an error message? I'm thinking there is an easier way to do this than using 2 queries.

 

[NickyJay]

Hiya,

basically my message, not exactly an error, just indicates the contact in question (any one from the list) receives the publication - i can only get it to display the first one

Nicola

 

[cfStarlight]

That is because you're not looping through the query values. #queryName.ColumnName# only returns the value in the first record unless you loop through each record using cfoutput or cfloop.

Can I ask why you need 2 queries? There may be a simpler way to do this.

 

[NickyJay]

yep, course, any help is apprieciated as im still learning

The first query retrieves the publication and anything related from the intersection table - again the publication is selected on teh page before.

<cfquery name="qryPublication" datasource="stakeholdersql">
	SELECT *
	FROM tblPublication, tblContactPubs
	WHERE PubCode = '#url.pubcode#'
	AND tblContactPubs.pubcodefk = tblPublication.pubcode
</cfquery>

The second query gets just the contact details (including pulling the company they work at in too)

<cfquery name="qryContacts" datasource="stakeholdersql">
	SELECT *
	FROM tblContacts, tblOrg
	WHERE tblOrg.OrgID = tblContacts.OrgIDfk
	ORDER BY OrgName ASC
</cfquery>

Would i be better combining the two queries to get the message looping correctly? I figure the 2nd query is looping ok as i can see all the contacts....

Nicola

 

[cfStarlight]

Would i be better combining the two queries to get the message looping correctly?

Yes, I think so. Can you tell me which columns you're actually using in your page (ie TableName.ColumnName). Its hard to tell because both queries are using SELECT *. Generally its best to select only the columns you need and avoid using SELECT *. Yeah, I know its easier, but its bad for performance reasons ;-)

 

[NickyJay]

Hiya,

finally i get a chance to reply!

the tables im using are: tblDelegates, tblOrganisation and tblBooking. I have altered my select as follows:

<cfquery name="qryOnEvent" datasource="EasyKit">
	SELECT DelegateID, Name, CustomerRef, tblDelegates.Deleted, OrgID, OrgName
	FROM tblDelegates 
	INNER JOIN tblOrganisation ON tblDelegates.EmployingOrg = tblOrganisation.OrgID 
	LEFT OUTER JOIN tblBooking ON tblDelegates.DelegateID = tblBooking.DelegateIDfk
	WHERE EventIDfk = '#url.Eventid#'
	AND tblDelegates.Deleted = 'N'
	ORDER BY tblDelegates.Name
</cfquery>

<cfquery name="qryClients" datasource="EasyKit">
	SELECT *
	FROM tblDelegates, tblOrganisation
	WHERE tblDelegates.EmployingOrg = tblOrganisation.OrgID 
	ORDER BY Name ASC
</cfquery>

and attempted to change my <cfif> to be as follows:

<cfloop query="qryClients">
		<cfoutput>
<cfif isDefined('qryOnEvent.DelegateIDFk') AND #qryOnEvent.DelegateIDfk# EQ #qryClients.DelegateID#>
			Delegate already on Event.
		<cfelse>
			<input <cfif IsDefined('checkall')>checked<cfelseif (#FORM.bookevent# EQ "#DelegateID#")>checked</cfif> type="checkbox" name="bookevent" value="#DelegateID#">
		</cfif>
		</cfoutput>
		</cfloop>

Now i dont get any instances of the message to say that a delegate is booked on a course!

Help!

Nicola