i still find it better than access. ----- Original Message ----- From: "Robertson-Ravo, Neil (RX)" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, January 16, 2003 1:35 PM Subject: RE: [ cf-dev ] Confusing SQL
> yep, but as in a post a while ago, they have a long way to go before they > are anywhere near Enterprise level, still good though! > > -----Original Message----- > From: Tom Smith [mailto:[EMAIL PROTECTED]] > Sent: 16 January 2003 13:15 > To: [EMAIL PROTECTED] > Subject: Re: [ cf-dev ] Confusing SQL > > > 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] > > -- > ** 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]
