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
