Match() function for MySQL database

Posts: 770 · Posted: Sun Nov 07, 2004 9:35 pm

The trading card site I'm building is coming along nicely. One of the next features I need implemented before I go live is a Match function that users can make use of in order to find other users with cards they want. The database table looks like this:

UserID - tinyint(4)
CardID - bigint (20)
own - tinyint (3)
want - tinyint(3)

When a user (say userID = jamie) inputs a card he wants, say card number 123, then the site checks to see if that entry exists and if not adds it like so:

userID **** CardID **** own **** want
-------------------------------------------------
jamie **** 123 **** 0 **** 1
dave **** 123 **** 1 **** 0
bob **** 123 **** 2 ****0

I would like it so when 'jamie' logs in and clicks on my 'Match' link it returns something like this:

Matches for user: Jamie

1. Bob has 2 cards that you need!
cardid: 123 trading: 2

2. Dave has 1 card that you need!
cardid: 123 trading: 1

Does anyone know how I could go about this? Any comments/suggestions are welcome!

Posts: 135 · Posted: Sun Nov 07, 2004 10:24 pm

The SQL syntax may be a little off, but this should give you a starting point. (I'm assuming the table is called "ToTrade").

Code:

SELECT
ownTT.UserID + " has " + ownTT.own + " card(s) that you need! CardId: " + ownTT.CardID + "; Trading: " + ownTT.own
FROM
ToTrade AS wantTT
INNER JOIN ToTrade AS ownTT ON
wantTT.CardID = ownTT.CardID
WHERE
wantTT.userID = "Jamie"
AND wantTT.want > 0
AND ownTT.userID <> "Jamie"
AND ownTT.own > 0

[/code]

Posts: 770 · Posted: Sun Nov 07, 2004 10:29 pm

Thanks, I'll take a look at that tonight. I was thinking it would be a lot more complex than that for some reason...