controlling nested cfoutput

[namtax]

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

[Dan_Bracuk]

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

[namtax]

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

[Newsgroup_User]

> 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

[efecto747]

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?

[namtax]

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

[efecto747]

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.

[namtax]

cheers for response efecto...its not liking the SELECT TOP unfortunately, is throwing up an error message....

[cgsj_usa]

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

[efecto747]

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?

[namtax]

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

[cgsj_usa]

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

[namtax]

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

[namtax]

hi, chris.....ive never used view or stored procedures before...i take it thats the more advanced coldfusion stuff...??

cheers

[cgsj_usa]

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

[namtax]

yeah im using mysql...though im sure that stored procedures are mentioned in this ben forta book that ive got

[efecto747]

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:

[namtax]

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

[efecto747]

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.

[namtax]

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>