which I might add will change in a future official release...
----- Original Message ----- From: "Robertson-Ravo, Neil (RX)" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, January 16, 2003 1:06 PM Subject: RE: [ cf-dev ] Confusing SQL > I think you are doing the best you can with mySQL.... considering you have > no SP's, views etc... :-) > > -----Original Message----- > From: Dave Phipps [mailto:[EMAIL PROTECTED]] > Sent: 16 January 2003 13:09 > To: [EMAIL PROTECTED] > Subject: RE: [ cf-dev ] Confusing SQL > > > The database is actually MySQL and whilst I could and should cache the > circuits query this is only hit once as I call the other 2 queries from > within the output of the circuits query and hence cannot cache them as > their result is dependent on a var which changes each time the loop is > executed. > > Here's a code snip: > > <cfoutput query="listCircuits" group="circuitid"> > > <cfinclude template="qry_list_menus.cfm"> > > <tr><td class="header" colspan="2"><img > src="images/arrow_right.gif"><b>#displayname#</b></td><td width="75" > class="list_italic_g">Created</td><td width="75" > class="list_italic_g">Modified</td></tr> > > <cfoutput group="contentid"> > <cfparam name="submenuid" default="0"> > > <cfloop query="list_menus"> > > <cfinclude template="qry_list_content.cfm"> > > <tr <cfif list_menus.currentrow MOD 2>bgcolor="##D9D7E6"</cfif>> > <td width="162" class="list"><img src="images/1px_spacer.gif" > width="12" height="9"> > <a > href="#request.self#?fuseaction=#XFA.edit#&mode=edit&circuitid=#list_menus.c > ircuitid#&menuid=#list_menus.menuid#&contentid=#list_menus.contentid#&#reque > st.urltoken#">#list_menus.pagetitle#</a></td> > <td width="348" class="list_italic">'#contentdescription#...'</td> > <td width="75" class="list">#LSDateFormat(list_menus.contentdate, > "dd/mm/yy")#</td> > <td width="75" class="list">#LSDateFormat(list_menus.lastupdate, > "dd/mm/yy")#</td> > </tr> > <cfif list_content.recordcount> > <tr> > <td colspan="4" class="header"><img > src="images/1px_spacer.gif" width="12" height="9"><img > src="images/arrow_right.gif">#list_menus.menuname#</td> > </tr> > </cfif> > > <cfloop query="list_content"> > > <tr <cfif list_content.currentrow MOD 2>bgcolor="##D9D7E6"</cfif>> > <td width="162" class="list"><img src="images/1px_spacer.gif" > width="24" height="9"> > <a > href="#request.self#?fuseaction=#XFA.edit#&mode=edit&circuitid=#list_menus.c > ircuitid#&menuid=#list_content.menuid#&submenuid=#list_content.submenuid#&co > ntentid=#list_content.contentid#&#request.urltoken#">#list_content.pagetitle > #</a></td> > <td width="348" class="list_italic">'#contentdescription#...'</td> > <td width="75" > class="list">#LSDateFormat(list_content.contentdate, "dd/mm/yy")#</td> > <td width="75" class="list">#LSDateFormat(list_content.lastupdate, > "dd/mm/yy")#</td> > </tr> > > </cfloop> > > </cfloop> > </cfoutput> > <tr><td class="header" colspan="4"><br> <br></td></tr> > </cfoutput> > > The result of the above code looks something like this: > > <tr><td class="header" colspan="2"><img > src="images/arrow_right.gif"><b>Academic</b></td><td width="75" > class="list_italic_g">Created</td><td width="75" > class="list_italic_g">Modified</td></tr> > > <tr bgcolor="#D9D7E6"><td width="162" class="list"><img > src="images/1px_spacer.gif" width="12" height="9"><a > href="index.cfm?fuseaction=cms.wz_content_addcontent&mode=edit&circuitid=4&m > enuid=14&contentid=22&CFID=129319&CFTOKEN=71536666">Introduction</a></td><td > > width="348" class="list_italic">'Introduction...'</td><td width="75" > class="list">01/12/02</td><td width="75" class="list">08/01/03</td></tr> > > <tr ><td width="162" class="list"><img src="images/1px_spacer.gif" > width="12" height="9"><a > href="index.cfm?fuseaction=cms.wz_content_addcontent&mode=edit&circuitid=4&m > enuid=15&contentid=23&CFID=129319&CFTOKEN=71536666">Curriculum</a></td><td > width="348" class="list_italic">'The School Organisation...'</td><td > width="75" class="list">01/12/02</td><td width="75" > class="list">14/01/03</td></tr> > <tr><td colspan="4" class="header"><img src="images/1px_spacer.gif" > width="12" height="9"><img src="images/arrow_right.gif">Curriculum</td></tr> > <tr bgcolor="#D9D7E6"><td width="162" class="list"><img > src="images/1px_spacer.gif" width="24" height="9"><a > href="index.cfm?fuseaction=cms.wz_content_addcontent&mode=edit&circuitid=4&m > enuid=15&submenuid=5&contentid=25&CFID=129319&CFTOKEN=71536666">Pre-Prep</a> > </td><td > width="348" class="list_italic">'Pre-Prep Curriculum...'</td><td width="75" > class="list">01/12/02</td><td width="75" class="list">08/01/03</td></tr> > > <tr ><td width="162" class="list"><img src="images/1px_spacer.gif" > width="24" height="9"><a > href="index.cfm?fuseaction=cms.wz_content_addcontent&mode=edit&circuitid=4&m > enuid=15&submenuid=6&contentid=26&CFID=129319&CFTOKEN=71536666">Prep</a></td > ><td > width="348" class="list_italic">'Prep School Curriculum...'</td><td > width="75" class="list">01/12/02</td><td width="75" > class="list">08/01/03</td></tr> > > <tr bgcolor="#D9D7E6"><td width="162" class="list"><img > src="images/1px_spacer.gif" width="24" height="9"><a > href="index.cfm?fuseaction=cms.wz_content_addcontent&mode=edit&circuitid=4&m > enuid=15&submenuid=7&contentid=27&CFID=129319&CFTOKEN=71536666">Pastoral > Care</a></td><td width="348" class="list_italic">'Pastoral Care...'</td><td > width="75" class="list">01/12/02</td><td width="75" > class="list">04/12/02</td></tr> > > <tr ><td width="162" class="list"><img src="images/1px_spacer.gif" > width="24" height="9"><a > href="index.cfm?fuseaction=cms.wz_content_addcontent&mode=edit&circuitid=4&m > enuid=15&submenuid=8&contentid=28&CFID=129319&CFTOKEN=71536666">Trips</a></t > d><td > width="348" class="list_italic">'Trips...'</td><td width="75" > class="list">01/12/02</td><td width="75" class="list">08/01/03</td></tr> > > <tr bgcolor="#D9D7E6"><td width="162" class="list"><img > src="images/1px_spacer.gif" width="24" height="9"><a > href="index.cfm?fuseaction=cms.wz_content_addcontent&mode=edit&circuitid=4&m > enuid=15&submenuid=10&contentid=47&CFID=129319&CFTOKEN=71536666">Music</a></ > td><td > width="348" class="list_italic">'Music...'</td><td width="75" > class="list">10/12/02</td><td width="75" class="list">08/01/03</td></tr> > > It all works but is a little heavy handed. Is there a better way. Could I > build an array or structure to hold the info I need? > > Cheers > > Dave > > At 12:48 1/16/2003 +0000, you wrote: > >Oh and the suggestion would be (if the circuits/menus don't change too > >much): > > > >Cache the queries > > > >This way the database doesn't get hit so much. The content may change too, > >but it might be useful to have, for example, a 10 or 30 minute cache on > >those too. Just to take the load off. > > > >Paul > > > > > Hi, > > > > > > I have three queries that build a navigation system which is > > > running really > > > slowly. This is because I haven't been able to combine the > > > three queries > > > into one and therefore have to do nested loops which results > > > in about 50 > > > queries running for some sections of the site. > > > > > > The three queries are below: > > > > > > ListCircuits: > > > > > > SELECT circuits.circuitid, circuits.circuitname, circuits.contentid, > > > circuits.displayname, content.contentid, content.pagetitle, > > > content.contentdate, content.contentdescription, content.lastupdate, > > > content.app_status, content.lastupdate, groups.groupid, > > > groups.groupname FROM groups, content_group, circuits LEFT > > > JOIN content ON > > > circuits.contentid = content.contentid > > > WHERE content.contentid = content_group.contentid > > > AND groups.groupid = content_group.groupid > > > ORDER BY circuits.circuitid > > > > > > ListMenus > > > > > > SELECT circuits.circuitid, circuits.circuitname, > > > circuits.displayname, > > > menus.menuid, menus.menuname, menus.contentid, submenus.submenuid, > > > content.pagetitle, content.contentdate, content.contentdescription, > > > content.lastupdate, content.app_status, groups.groupid, > > > groups.groupname FROM groups, circuits LEFT JOIN menus ON > > > circuits.circuitid = > > > menus.circuitid LEFT JOIN submenus ON menus.menuid = > > > submenus.menuid LEFT > > > JOIN content ON menus.contentid = content.contentid LEFT JOIN > > > content_group ON content.contentid = content_group.contentid > > > WHERE content_group.groupid = groups.groupid AND > > > circuits.circuitid = #listCircuits.circuitid# AND > > > content.pagetitle != 'The Week Ahead' GROUP BY > > > content.contentid ORDER BY menus.menuid > > > > > > ListContent > > > > > > SELECT submenus.submenuid, submenus.menuid, submenus.submenuname, > > > content.contentid, content.pagetitle, content.contentdate, > > > content.contentdescription, content.lastupdate, content.app_status, > > > content.isDeleted, groups.groupname, groups.groupid > > > FROM submenus LEFT JOIN content ON submenus.submenuid = > > > content.submenuid > > > LEFT JOIN content_group ON content.contentid = > > > content_group.contentid LEFT > > > JOIN groups ON content_group.groupid = groups.groupid > > > WHERE (submenus.menuid = #list_menus.menuid#) > > > ORDER BY submenus.submenuid, content.contentid > > > > > > Can anyone see a way to pool these three queries into one query? The > > > problem I have found is that each table (circuits, menus, > > > submenus) has a > > > contentid (which is the default page for that section). > > > > > > Any help on this? > > > > > > Cheers > > > > > > Dave > > > > > > > > > > > > -- > > > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > > > > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > > For additional commands, e-mail: > > > [EMAIL PROTECTED] For human help, e-mail: > > > [EMAIL PROTECTED] > > > > > > > > > > > > > > > > > > >-- > >** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > > >To unsubscribe, e-mail: [EMAIL PROTECTED] > >For additional commands, e-mail: [EMAIL PROTECTED] > >For human help, e-mail: [EMAIL PROTECTED] > > > ============================================ > Phipps CF Development > Oxford, Oxfordshire. > Telephone: +44(0)7718 896696 > http://www.phipps-cf.co.uk > ============================================ > > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > For human help, e-mail: [EMAIL PROTECTED] > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > For human help, e-mail: [EMAIL PROTECTED] __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
