|
|
Posted by NC on 10/26/05 22:11
smorrey@gmail.com wrote:
>
> I'm trying to create a simple transaction handling system where users
> pay eachother via points of a sort.
> I have a table where each transaction is stored, in a single row.
> It looks like this
>
> transid,from_user,from_amount,to_user,to_amount
Why do you have from_amount and to_amount? Aren't they supposed
to be equal? Or is there a "house take" of some kind?
> Given the above, I just want a simple single SQL statement that can
> return a full balance.
Bad idea. If you want a transaction system, create a transaction
system. You need to add a `balance` column to the table where
user data is stored. Both the `users` table and `transactions`
table must be InnoDB. Then you can actually do transaction
processing:
BEGIN;
INSERT INTO `transactions`
SET from_user = [from_user],
from_amount = [from_amount],
to_user = [to_user],
to_amount = [to_amount];
UPDATE `users`
SET `balance` = `balance` - [from_amount]
WHERE `id` = [from_user];
UPDATE `users`
SET `balance` = `balance` + [to_amount]
WHERE `id` = [to_user];
COMMIT;
This way, balance is readily available as a single number;
there's no need to query records from the beginning of the
universe to look up balances...
Cheers,
NC
Navigation:
[Reply to this message]
|