Les,
This is completely untested, but give it a try. If it works it will shorten
your code and your execution time considerably. If there is any problems,
just scream real loud!!
<CFQUERY NAME="themes" DATASOURCE="#DB_redwood#">
SELECT *
FROM themes
ORDER BY theme
</CFQUERY>
<CFQUERY NAME="names" DATASOURCE="#DB_redwood#">
SELECT distinct lname, fname, artist_id
FROM photos, photographers
WHERE (photos.theme_id = '#themes.id#'
OR photos.theme_id LIKE '%,#themes.id#,%'
OR photos.theme_id LIKE '#themes.id#,%'
OR photos.theme_id LIKE '%,#themes.id#')
and photographers.id = photos.artist_id
order by lname
</CFQUERY>
<CFQUERY NAME="m" DATASOURCE="#DB_redwood#">
SELECT COUNT (*) as gg
FROM photos
where (theme_id = '#themes.id#'
OR theme_id LIKE '%,#themes.id#,%'
OR theme_id LIKE '#themes.id#,%'
OR theme_id LIKE '%,#themes.id#')
and artist_id = #names.artist_id#
GROUP BY artist_id
</CFQUERY>
<!--- you can also change the max_cols to other values... --->
<CFSET max_cols=4>
<TABLE style="border: 1px solid ##ccccee;" align="center" width="100%">
<CFSET count=0>
<CFOUTPUT query="themes" group="categoryID">
<CFIF count mod max_cols eq 0>
<TR valign="top">
</CFIF>
<TD width="33%">
<TABLE border="0" cellpadding="0" CELLPSACING="0" width="100%">
<TR>
<TD>#themes.theme#</TD>
</TR>
<TR>
<TD><CFOUTPUT><A href="">#names.fname# #names.lname#
(#m.gg#)</A></CFOUTPUT></TD>
</TR>
</tr>
</TABLE>
</center></TD>
</td>
<CFSET count = count + 1>
<CFIF (count mod max_cols eq 0)>
</tr>
</CFIF>
</CFOUTPUT>
<CFIF count mod max_cols neq 0>
<TD> </TD>
</TR>
</CFIF>
</TABLE>
Doug B.
----- Original Message -----
From: "Les Irvin" <[EMAIL PROTECTED]>
To: "CF-Talk" <[email protected]>
Sent: Saturday, January 27, 2007 1:21 PM
Subject: Re: Querying a delimited text field
> OK, have at it! I'll be confirming my "novice" status by revealing this
> code, but at least you won't be able to see me blush.
>
> I am first listing info on a database of photos by theme, then allowing
the
> user to pick the specific photographer he/she wants to see. This is the
> tough page for me:
>
> http://redwood.jmdl.com/themes.cfm
> http://redwood.jmdl.com/themes_code.cfm (code)
>
> The combination of a loop over the initial query and the delimited field
> (example, Henry Gilpin's same photo appears under "Iconic" and
"Landscape")
> are what is kicking my butt.
>
> It works the way I have it now, but it's ugly, ugly, ugly and certainly
> wastes unnecessary resources.
>
> Thanks to anyone who can school me.
> Les
>
>
>
>
>
>
>
> On 1/27/07, Dave Phillips <[EMAIL PROTECTED]> wrote:
> >
> > Les, if that variable is the result of another query, then you can
> > accomplish what you want MUCH MUCH faster using an in clause. Can you
> > post
> > this whole section of code and we can optimize it for you. We need to
see
> > the query that is getting you the values for theme_id variable, plus any
> > other code where you are manipulating info from that query.
> >
> > When your tables get larger, the LIKE and OR solution we had is going to
> > get
> > very slow for you. The IN clause would be a bit faster for sure.
> >
> > Dave
> >
> > -----Original Message-----
> > From: Les Irvin [mailto:[EMAIL PROTECTED]
> > Sent: Saturday, January 27, 2007 1:17 PM
> > To: CF-Talk
> > Subject: Re: Querying a delimited text field
> >
> >
> > Yes, Themes.id is actually a variable - a result of an earlier query.
> >
> > On 1/27/07, Dave Phillips <[EMAIL PROTECTED]> wrote:
> > >
> > > I think that his 'themes_id' is a coldfusion variable. Les, can you
> > > please confirm this or not? It makes a big difference in how things
> > > might work.
> > >
> > > Dave
> > >
> > > -----Original Message-----
> > > From: Doug Brown [mailto:[EMAIL PROTECTED]
> > > Sent: Saturday, January 27, 2007 12:46 PM
> > > To: CF-Talk
> > > Subject: Re: Querying a delimited text field
> > >
> > >
> > > Ok, well maybe an IN statement. I may be way off base though.
> > >
> > > IE:
> > >
> > > <CFQUERY NAME="names" DATASOURCE="#DB_redwood#">
> > > SELECT distinct lname, fname, artist_id
> > > FROM photos, photographers
> > > WHERE photos.theme_id IN (SELECT themes.id FROM photos)
> > > and photographers.id = photos.artist_id
> > > order by lname
> > > </CFQUERY>
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
> >
> >
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267856
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
