19 Replies Latest reply on Apr 1, 2007 4:51 PM by efecto747

    controlling nested cfoutput

    namtax Level 1
      i am using a nested cfoutput to group my database output......
      the query output is

      <cfoutput query="fans" group="name_usr" maxrows="40">
      #iname_usr#(name of fan)
      <cfoutput>
      #name_art#(name of artist)
      </cfoutput>
      </cfoutput>

      this display a list of an artists fans, then a list of the other artists that the paricular fan has favourited....
      you can see results here

      http://www.musicexplained.co.uk/a/fans.cfm?a=burial+

      the thing is, that i want the list of "other artists that the particular artist has favourited" to be maximum of 5 artists long, but am unsure of how to control the output of a nested cfoutput.....as when you add a maxrows function to the <cfoutput> an error message is displayed..

      the query behind this is pretty complex, but can post

      any help would be appreciated

      cheers ike
        • 1. Re: controlling nested cfoutput
          Dan Bracuk Level 5
          Instead of using a group attribute, try something like this.

          <cfoutput query="fan">
          #iname_usr#
          <cfquery name = "q2" dbtype="query" maxrows="5">
          select etc
          </cfquery>
          <cfloop query="q2">
          #name_art#
          etc
          • 2. Re: controlling nested cfoutput
            namtax Level 1
            hi cheers for this...have given this a go, but unfortunately the #name_art# output..which displays the users other fav artists, is now repeating the same artist names for every user in the table, instead of changing dynamically as it was before...

            cheers
            • 3. Re: controlling nested cfoutput
              Level 7
              > to be maximum of 5 artists long

              If there are ten matches, what is the criteria you have for deciding which
              five to display? Alphabetical? Some sort of ranking? Frequency?

              You should be able to adjust your query to only return the data you
              actually need, with a bit of tweaking.

              --
              Adam
              • 4. Re: controlling nested cfoutput
                efecto747 Level 1
                Hi, Adam is right in that you should be able to limit the selection at the query level.

                Are you able to post the query sql?
                • 5. Re: controlling nested cfoutput
                  namtax Level 1
                  ok the query is below..its a little complex....i have put table names etc in square brackets

                  if there are ten matchesm, i think the criteria for deciding which five to display will be alphabetical...

                  <cfquery name="fans" datasource="#application.datasource#">
                  SELECT name_art [band name], id_usr [user id ], name_usr [username], id_art [band id]
                  FROM (user_usr [user table]
                  LEFT OUTER JOIN artists_fav [table which contains artist ids users have favd]
                  ON user_usr.id_usr = artists_fav.id_usr_fav)
                  LEFT OUTER JOIN artist_art [artist table] ON
                  artists_fav.id_artkey_fav = artist_art.id_art
                  WHERE id_usr_fav [user id in table which contains favd artists info] IN
                  (SELECT id_usr_fav from artists_fav
                  WHERE id_artkey_fav [id of artist whose page you are on] = #id_art.id_art#
                  AND id_usr_fav <>#SESSION.auth.idnumber#)
                  AND id_artkey_fav <>#id_art.id_art#
                  GROUP BY name_art, id_usr, name_usr, id_art
                  </cfquery>

                  this query basically grabs a user who has favourited the artist whose page you are on, and then shows the other artists that user has also favourited in the artists_fav table....

                  thanks
                  • 6. controlling nested cfoutput
                    efecto747 Level 1
                    ok, wouldn't it just be a case of limiting the sub-query with a TOP statement?

                    <cfquery name="fans" datasource="#application.datasource#">
                    SELECT name_art [band name], id_usr [user id ], name_usr [username], id_art [band id]
                    FROM (user_usr [user table]
                    LEFT OUTER JOIN artists_fav [table which contains artist ids users have favd]
                    ON user_usr.id_usr = artists_fav.id_usr_fav)
                    LEFT OUTER JOIN artist_art [artist table] ON
                    artists_fav.id_artkey_fav = artist_art.id_art
                    WHERE id_usr_fav [user id in table which contains favd artists info] IN
                    (SELECT TOP 5 id_usr_fav from artists_fav
                    WHERE id_artkey_fav [id of artist whose page you are on] = #id_art.id_art#
                    AND id_usr_fav <>#SESSION.auth.idnumber#)
                    AND id_artkey_fav <>#id_art.id_art#
                    GROUP BY name_art, id_usr, name_usr, id_art
                    </cfquery>

                    To control which top 5 records were selected, add an ORDER BY statement to the sub-query.

                    cheers.
                    • 7. Re: controlling nested cfoutput
                      namtax Level 1
                      cheers for response efecto...its not liking the SELECT TOP unfortunately, is throwing up an error message....
                      • 8. Re: controlling nested cfoutput
                        cgsj_usa@yahoo.com Level 1
                        Be sure to put that t-sql logic into a view or stored procedure...such a big statement shouldn't be encapsulated in CF. Like someone else said on here, let the DB do some of the work. I hope that this helps. Thanks.

                        Chris
                        • 9. Re: controlling nested cfoutput
                          namtax Level 1
                          hiya chris, im not too sure of some of the terminology you are using..

                          t-sql logic?
                          view or stored procedure?
                          encapsulated in CF?

                          iv never come across these phrases before, can you expand

                          cheers
                          • 10. Re: controlling nested cfoutput
                            efecto747 Level 1
                            quote:

                            Originally posted by: namtax
                            cheers for response efecto...its not liking the SELECT TOP unfortunately, is throwing up an error message....

                            Any chance of seeing the error message?

                            What flavor database are you using? SELECT TOP works for MS SQL & MS Access - are you using one of these?
                            • 11. Re: controlling nested cfoutput
                              namtax Level 1
                              hi efeckto...the error message im getting is

                              Error Executing Database Query.
                              Syntax error or access violation: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5 id_usr_fav from artists_fav WHERE id_artkey_fav = 25 ) AND id_artkey_fav <>' at line 8

                              The error occurred in C:\Domains\musicexplained.co.uk\wwwroot\a\fans_2.cfm: line 101

                              99 : WHERE id_artkey_fav = #id_art.id_art#
                              100 : )
                              101 : AND id_artkey_fav <>#id_art.id_art#
                              102 : GROUP BY name_usr, name_art
                              103 :

                              im using php myadmin....

                              thanks
                              • 12. Re: controlling nested cfoutput
                                cgsj_usa@yahoo.com Level 1
                                All of this:

                                SELECT name_art [band name], id_usr [user id ], name_usr [username], id_art [band id]
                                FROM (user_usr [user table]
                                LEFT OUTER JOIN artists_fav [table which contains artist ids users have favd]
                                ON user_usr.id_usr = artists_fav.id_usr_fav)
                                LEFT OUTER JOIN artist_art [artist table] ON
                                artists_fav.id_artkey_fav = artist_art.id_art
                                WHERE id_usr_fav [user id in table which contains favd artists info] IN
                                (SELECT TOP 5 id_usr_fav from artists_fav
                                WHERE id_artkey_fav [id of artist whose page you are on] = #id_art.id_art#
                                AND id_usr_fav <>#SESSION.auth.idnumber#)
                                AND id_artkey_fav <>#id_art.id_art#
                                GROUP BY name_art, id_usr, name_usr, id_art

                                you should put in a view or stored procedure, so that the DB does some work too.

                                Also, you don't need the group by, since there's no aggregate function (sum, avg, etc.) in the select list.

                                Thanks.

                                Chris
                                • 13. Re: controlling nested cfoutput
                                  namtax Level 1
                                  hi, chris.....ive never used view or stored procedures before...i take it thats the more advanced coldfusion stuff...??

                                  cheers
                                  • 14. Re: controlling nested cfoutput
                                    cgsj_usa@yahoo.com Level 1
                                    No, actually, it's more on the DB side, but I just remembered that you're using "mySQL", right? You might not have those options available.

                                    Chris
                                    • 15. Re: controlling nested cfoutput
                                      namtax Level 1
                                      yeah im using mysql...though im sure that stored procedures are mentioned in this ben forta book that ive got
                                      • 16. Re: controlling nested cfoutput
                                        efecto747 Level 1
                                        If you're using MySQL then you need to use the LIMIT clause not TOP.

                                        Here's a good link for further reading: MySQL Ref

                                        Using the LIMIT clause, the query I posted earlier should look something like this:

                                        • 17. Re: controlling nested cfoutput
                                          namtax Level 1
                                          hi efecto...have implemented your code, but am getting the error

                                          General error: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

                                          is this something to do with the version of PHPMYadmin that im using maybe?
                                          cheers
                                          • 18. Re: controlling nested cfoutput
                                            namtax Level 1
                                            i discovered an answer incase anyone was interested, however it doesnt deal with the source query, only in the display code

                                            <CFSET iMaxFavorites = 5>

                                            <cfoutput query="fans" group="name_usr" maxrows="40">
                                            #name_usr#(name of fan)

                                            <CFSET iNumFavorites = 1>
                                            <cfoutput>
                                            <CFIF iNumFavorites LTE iMaxFavorites>
                                            #name_art#(name of artist)
                                            <CFELSEIF iNumFavorites EQ iMaxFavorites + 1>
                                            ... <!--- Put an ellipsis to show that entries were truncated. --->
                                            </CFIF>

                                            <CFSET iNumFavorites = iNumFavorites + 1>
                                            </cfoutput>
                                            </cfoutput>
                                            • 19. Re: controlling nested cfoutput
                                              efecto747 Level 1
                                              namtax, glad to see you came up with a solution that suits you. To limit the data being selected is preferable to limiting it at output but in your case this is probably fine.

                                              The error message you were getting suggests the version of MySQL you're running is older than the version that supports the LIMIT clause - this means my suggestion would be no good unless you were able to upgrade to the latest version of MySQL. This is something you might want to consider later down the line so you can take advantage of features such as the LIMIT clause.

                                              cheers.