Using START TRANSACTION

[Cetanu]

How exactly can I use Start Transaction? I've seen numerous examples on the Internet, but they confuse me. They have different syntaxes...

 

All I want to do is have an Infirmary for my RPG.

 

Member 1 starts with 40 health.

 

They take damage and have 25 health.

 

They go to the infirmary and get rejuvenated to 40.

 

Members will have different healths, so I can't just define a variable to restore them to their original health, so someone told me to use START TRANSACTION as an SQL statement..

 

Help pleeeeeease.

[ignace]

Wrong. You only need a simple update statement. If you would use transactions you would be able to rollback so if my character would take damage it would automatically heal if you would rollback.

 

START TRANSACTION;

UPDATE characters SET health = 25 WHERE id = $id;//25 HP

ROLLBACK;//40 HP

[Mark Baker]

Start Transaction is used when you are doing several updates to a database in one go (or transaction).

 

Consider character A selling a dagger to character B for 20 gp: there are several updates/inserts/deletes.

INSERT INTO inventory (character, item) VALUES ('B','dagger');
DELETE FROM inventory WHERE character = 'A' and item = 'dagger';
UPDATE characters SET money=money-20 WHERE character = 'B'
UPDATE characters SET money=money+20 WHERE character = 'A'

If, for some reason, the script fails after the INSERT but before the DELETE, then both character A and character B will have a dagger, and both will still have the same amount of money as when they started... not what you want to happen.

 

If you wrap this in a transaction, then:

START TRANSACTION
INSERT INTO inventory (character, item) VALUES ('B','dagger');
if (error) { ROLLBACK; exit; }
DELETE FROM inventory WHERE character = 'A' and item = 'dagger';
if (error) { ROLLBACK; exit; }
UPDATE characters SET money=money-20 WHERE character = 'B'
if (error) { ROLLBACK; exit; }
UPDATE characters SET money=money+20 WHERE character = 'A'
if (error) { ROLLBACK; exit; }
COMMIT

Now, if the script fails after the INSERT but before the DELETE, then the code will rollback, meaning that it will reverse the insert that succeeded before it exits, so the database will not contain a new dagger record in inventory for character B.

Only if all four statements work correctly will the database changes be "committed" (made permanent).

 

[Cetanu]

Oh, okay, well that makes a whole lot more sense. 

 

@ignace, if each character has different health, say...40, 55, 100, and 60, how would I determine their starting score to rejuvenate only that much? If I wanted to heal #1 with 40hp, and they had 60, I would add 20, but then that would add 20 to everyone that heals. I just want to heal them back to their starting score.

[Mark Baker]

@ignace, if each character has different health, say...40, 55, 100, and 60, how would I determine their starting score to rejuvenate only that much? If I wanted to heal #1 with 40hp, and they had 60, I would add 20, but then that would add 20 to everyone that heals. I just want to heal them back to their starting score.

So you must have a record of their starting score somewhere, right?

[ignace]

@ignace, if each character has different health, say...40, 55, 100, and 60, how would I determine their starting score to rejuvenate only that much? If I wanted to heal #1 with 40hp, and they had 60, I would add 20, but then that would add 20 to everyone that heals. I just want to heal them back to their starting score.

So you must have a record of their starting score somewhere, right?

 

You prolly associate (max) health with the characters level, right? Thus:

 

CREATE TABLE levels (
    levels_id SMALLINT NOT NULL AUTO_INCREMENT,
    levels_max_health SMALLINT,
    PRIMARY KEY (levels_id)
);

 

Use the value of levels_max_health to rejuvenate it.

[Cetanu]

Aw, that's what I figured, I thought there would be an easier way, though.

 

Anyway, I'll go do that. Thanks