I'm working on a trading card site for an online card game. One of the key
functions of the site will be that a user can login and click on "match" and
the database will return a 'matching' trader - one that has all the cards the
user needs. I've setup a test version that counts on the user with userID=7
logging in. My page looks like this:
<!--- make sure user is logged in --->
<cfinclude template="../users/includes/loginCheck.cfm">
<cfquery name="qCheckUN" datasource="#dsn#" username="#un#" password="#pw#">
SELECT userID
FROM users
WHERE username = <cfqueryparam value="#getAuthUser()#"
cfsqltype="cf_sql_varchar">
</cfquery>
<!--- set userID --->
<cfset cUserID = #qCheckUN.userID#>
<!-- Get all the cards that the current user is looking for -->
<cfquery name="qGetWantedCards" datasource="#dsn#" username="#un#"
password="#pw#">
SELECT userID, cardID
FROM mycards
WHERE userID = #cUserID#
AND trade = 1
</cfquery>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd";>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Find Matching Traders</title>
</head>
<body>
<!--- add page header --->
<cfinclude template="../includes/header.cfm">
<table width="720" border="0" align="center" cellpadding="0" cellspacing="0"
class="MainOutline">
<td width="720" height="30" align="center" valign="middle">
<div align="left"><span class="main"><strong>Matching
Traders</strong></span></div></td>
</tr>
<cfoutput query="qGetWantedCards" group="cardID">
<cfquery name="qGetMatches" datasource="#dsn#" username="#un#"
password="#pw#">
SELECT userID, cardID
FROM mycards
WHERE userID <> "#cUserID#"
AND cardID = "#qGetWantedCards.cardID#"
AND own = 1
</cfquery>
<tr bgcolor="#IIF(CurrentRow MOD 2, DE('f5f5f5'), DE('ebebe2'))#">
<td width="720" height="30" align="left" valign="middle"><span
class="main"><strong>UserID</strong>:
#ValueList(qGetMatches.userID)#<br></span></td>
</tr>
<tr bgcolor="#IIF(CurrentRow MOD 2, DE('f5f5f5'), DE('ebebe2'))#">
<td width="720" height="30" align="left"
valign="middle"><cfoutput><span class="main">Owes Cards:
#qGetMatches.cardID#</span><br></cfoutput></td>
</tr>
</cfoutput>
</table>
<!--- add page footer --->
<cfinclude template="../includes/footer.cfm">
</body>
</html>
which outputs this for me:
Quote:
Matching Traders
UserID:
Owes Cards:
UserID:
Owes Cards:
UserID: 9,13,8
Owes Cards: 41001151
UserID: 3,13
Owes Cards: 41001152
UserID:
Owes Cards:
UserID:
Owes Cards:
...which is close, but no cigar. I need to get rid of those 'blank' entries,
plus I need to arrange it so what's left comes out like this:
Quote:
UserID: 3
Owns Cards: 41001152
UserID: 8
Owns Cards: 41001151
UserID: 9
Owns Cards: 41001151
UserID: 13
Owns Cards: 41001151, 41001152
The database is correct - I'm using mySQL v 4.0.18. You can verify this by
looking at the contents of the 'mycards' table:
UserID CardId Own Trade
3 41001152 1 0
7 41001151 0 1
7 41001152 0 1
7 41001102 0 1
7 41001101 0 1
7 41001154 0 1
7 41001153 0 1
8 41001151 1 0
9 41001151 1 0
13 41001151 1 0
13 41001152 1 0
So, what am I missing? Also, before someone mentions it, my original version of
this page contained a subquery, however the version of MySQL that we're running
right now doesn't support subqueries so I have to use something else.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187151
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
