Hi all,
One solution is to create a subquery and set cf grouping parameter to the
user_id field. I did need to define the 'root' charID (0 in this case) in
the database however.
I hope this helps.
Scott
<cf_endtransmission>
________________________________________
<cfquery datasource="mydsn" name="getMyChars">
SELECT c.charID, c.charName, c.charSurname, c.lvl, c.parentID,c.userid
FROM characters c
WHERE c.parentid IN (SELECT charid from characters)
ORDER BY c.userid,c.parentid,charid
</cfquery>
<cfoutput query="getMyChars" group="userid">
<h2>My Characters</h2>
<p><b>User:</b> #UserID#</p>
<cfoutput>
<table border="0" cellpadding="3" cellspacing="3" width="400" bgcolor=black>
<Cfif parentid is 0 >
<tr>
<td colspan="2" width="100%">
<table border="0" cellpadding="3" cellspacing="1" width="100%">
<tr bgcolor="##999999">
<td width="360">
<b>#charName# #charSurname#</b>
</td>
<td width="40">
[edit]
</td>
</tr>
<tr bgcolor="##CCCCCC">
<td colspan=2 width=400>
Level #lvl#
</td>
</tr>
</table>
</td>
</tr>
<cfelse>
<tr>
<td width="40">
<font color="White"><b>alt:</b></font>
</td>
<td>
<table border="0" cellpadding="3" cellspacing="1" width="100%">
<tr bgcolor="##999999">
<td width="360">
<b>#charName# #charSurname#</b>
</td>
<td width="40">
[edit]
</td>
</tr>
<tr bgcolor="##CCCCCC">
<td colspan=2 width=400>
Level #lvl#
</td>
</tr>
</table>
</td>
</tr>
</cfif>
</table>
</cfoutput>
</cfoutput>
_________________________________
----- Original Message -----
From: Dave Vause <[EMAIL PROTECTED]>
To: Fusebox <[EMAIL PROTECTED]>
Sent: Monday, November 20, 2000 4:53 PM
Subject: cfloopage?
> I've been banging my head against the wall over this one
> for two days...
>
> I have a table in my database called characters. All users
> have one or more characters and must have one and only one
> character with a parentID of "0". This is the "parent"
> character. The rest of the user's characters' parentID is
> set to the charID of the parent. So, each row in the DB
> has userID, charID, and parentID.
>
> I want to display a sortable list of all users' characters.
> Something like this for each user:
>
> user1:
> Parent character (parentID=0, charID=1, userID=1)
> - child character (parentID=1, charID=2, userID=1)
> - child character (parentID=1, charID=3, userID=1)
>
> user2:
> Parent character (parentID=0, charID=4, userID=2)
>
> user3:
> Parent character (parentID=0, charID=5, userID=3)
> - child character (parentID=5, charID=6, userID=3)
>
> and so on...
>
> I've tried various loops and grouped cfoutputs. Nothing seems
> to give me the result I'm looking for without nesting another
> query inside a loop, which to me seems a little extreme. I had
> no problem doing this when querying the character table with a
> specific userID. Its when I try to pull characters for all
> users I get into trouble. Attached below is my code to pull
> this same table for only ONE user.
>
> Thanks in advance!
> Dave Vause
>
> -----------------
> dsp_myChars.cfm
> -----------------
> <cfquery datasource="#request.maindsn#" name="getMyChars">
> select charID, charName, charSurname, lvl, parentID
> from characters
> where characters.userID = #val(client.userID)#
> </cfquery>
>
> <h2>My Characters</h2>
>
> <p><b>User:</b> <cfoutput
query="request.getuser">#username#</cfoutput></p>
>
> <cfif getMyChars.recordcount GT 0>
> <cfquery datasource="#request.maindsn#" name="getPriChar">
> select charID, charName, charSurname, lvl, parentID
> from characters
> where characters.userID = #val(client.userID)#
> and parentID = 0
> </cfquery>
>
> <table border="0" cellpadding="3" cellspacing="3" width="400"
> bgcolor=##000000
> background="<cfoutput>#request.imagesroot#</cfoutput>/img-clear.gif">
>
> <cfoutput query="getPriChar">
> <tr>
> <td colspan="2" width="100%">
> <table border="0" cellpadding="3" cellspacing="1" width="100%"
> background="#request.imagesroot#/img-clear.gif">
> <tr bgcolor="##999999">
> <td width="360">
> <b>#charName# #charSurname#</b>
> </td>
> <td width="40">
> <a
>
href="#request.cfroot#/characters/index.cfm?fuseaction=editchar&charID=#char
> ID#&#request.urltoken#">[edit]</a>
> </td>
> </tr>
> <tr bgcolor="##CCCCCC">
> <td colspan=2 width=400>
> Level #lvl#
> </td>
> </tr>
> </table>
> </td>
> </tr>
> </cfoutput>
> <cfif getMyChars.recordcount GT 1>
> <cfquery datasource="#request.maindsn#" name="getAltChars">
> select charID, charName, charSurname, lvl, parentID
> from characters
> where characters.userID = #val(client.userID)#
> and parentID > 0
> order by lvl DESC
> </cfquery>
>
> <cfoutput query="getAltChars" group="lvl">
> <tr>
> <td width="40">
> <font color="White"><b>alt:</b></font>
> </td>
> <td>
> <table border="0" cellpadding="3" cellspacing="1" width="100%"
> background="#request.imagesroot#/img-clear.gif">
> <tr bgcolor="##999999">
> <td width="360">
> <b>#charName# #charSurname#</b>
> </td>
> <td width="40">
> <a
>
href="#request.cfroot#/characters/index.cfm?fuseaction=editchar&charID=#char
> ID#&#request.urltoken#">[edit]</a>
> </td>
> </tr>
> <tr bgcolor="##CCCCCC">
> <td colspan=2 width=400>
> Level #lvl#
> </td>
> </tr>
> </table>
> </td>
> </tr>
> </cfoutput>
> </cfif>
> </table>
> <cfelse>
> <p>You have no active characters.</p>
> </cfif>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
>
> Archives: http://www.mail-archive.com/[email protected]/
> Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
