first, put all of your user defined (form) data into 'cfqueryparam' tags to
protect your database.
second, you are Ordering by publication date. If you want to group by 'netid'
then you will need to order by netid first, then by publication date. If this
is too much to put into your query right off, then you can always pull the
query results then do a query of a query to 're-sort' the results.
Hope this helps.
> Hello -
>
> I have a query that runs once a form element is defined. The element
> is the submit button. when the query runs, I want it to output the
> results by faculty member with all citations listed under the faculty
> member. what is occurring is the name will be displayed and then the
> citations as I want, however, the query will them proceed to assign
> all other people in the directory to the selected faculty member's
> citations. A faculty member's eight citations turn into over 50K
> records being displayed.
>
> This is the query that runs if the form element is defined:
> **************************************
> <cfif isDefined("form.findPublication")>
> <cfquery name="qGetPublications" datasource="myDSN">
> SELECT P.AUTHOR1, P.AUTHOR2, P.AUTHOR3, P.AUTHOR4, P.AUTHOR5, P.
> AUTHOR6, P.AUTHOR7, P.AUTHOR8, P.PUBLICATION_DATE, P.PUBLICATION_NAME,
> P.PUBLICATION, P.CITATION, P.DISCIPLINE, D.NETID, D.LASTNAME, D.
> FIRSTNAME, d. classification, z.id, z.discipline
>
> FROM dbo.KSBDirectory AS D,
> DBO.PUBLICATIONS AS P,
> DBO.DISCIPLINE AS Z
>
> WHERE (p.author1=d.netid)
> and (d.discipline=z.id)
> AND (P.DISPLAY='true')
> and (d.classification <> 'staff')
> And (0=0)
>
> <cfif (faculty is not "0")>
> and (d.netid = '#form.faculty#') OR (P.AUTHOR2 = '#form.faculty#') OR
> (P.AUTHOR3 = '#form.faculty#') OR (P.AUTHOR4 = '#form.faculty#')
> OR (P.AUTHOR5 = '#form.faculty#') OR (P.AUTHOR6 = '#form.faculty#') OR
> (P.AUTHOR7 = '#form.faculty#') OR (P.AUTHOR8 = '#form.faculty#')
> </cfif>
>
> <cfif (discipline is not "0")>
> AND p.discipline = '#form.discipline#'
> </cfif>
> <cfif (pubYear is not "0")>
> AND p.publication_date = '#form.pubYear#'
> </cfif>
>
>
> ORDER BY publication_date desc
> </cfquery>
>
> </cfif>
> ********************************
> This is the output to display the data:
> ********************************
>
> <cfelseif isDefined ("form.findPublication") and ("form.faculty") is
> not "0" and ("form.discipline") is not "0" and ("form.discipline") is
> not "0">
> <h4>Available Publications</h4>
> <cfif qGetPublications.recordcount gte
> 1>
> <ul>
> <p>
> <cfoutput query="qGetPublications" group="netid">
> <p><a href="facultyProfile.cfm?netid=#netid#">#lastname#, #firstname#
> </a><br />
> <cfoutput group="citation"><em>#citation#</em><br /><br
> /></cfoutput></p>
> </cfoutput>
> </p>
> </ul>
> <cfelse>
> <p>No publications meet your
> criteria.</p>
> </cfif>
> </cfif>
>
> I suspect the "group" attribute in cfoutput may be to blame?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:3244
Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
